true power of MS excel. update: PowerBI

[bhoc84]

Re: MS excel formula, programming, analysis, presentation and tutorials.

patulong po ulit mga sir kelengan ko sc value yung nasa column "I" gusto ko sana mangyari is automatic nyang gagamitin na value "if available" yung nasa column E and F

View attachment 318277

 

[edzaneallen2016]

Re: MS excel formula, programming, analysis, presentation and tutorials.

maraming maraming salamat dito...

 

[themonyo]

Re: MS excel formula, programming, analysis, presentation and tutorials.

patulong po ulit mga sir kelengan ko sc value yung nasa column "I" gusto ko sana mangyari is automatic nyang gagamitin na value "if available" yung nasa column E and F

View attachment 1209401

nalilito ako brod sa instructions mo
anong default value?
paano kung walang laman yung Column F & E?
siguro gawa ka ng multiple samples ng mga formula mo depende sa mga values na given, tapos pwede kita gawan ng mas kumplikadong formula base sa arguments

edit: yung gagawin mong excel lagyan mo ng values atsaka yung mga formula na gusto mo mangyari. ako na bahala sa if argument pag maintidihan ko na gusto mo

 

[bhoc84]

Re: MS excel formula, programming, analysis, presentation and tutorials.

pa check na lang po ng nakaatach na file

 

[themonyo]

Re: MS excel formula, programming, analysis, presentation and tutorials.

pa check na lang po ng nakaatach na file

heto brod. palitan mo na lang yung 20 & 19 na value ng row values mo.
=IF(AND(E20>0;F20>0);F20/E20;I19)

inattach ko na rin yung edited version with that formula.

edit: paliwanag ko na rin na kelangan ng value yung E & F para gumana yung formula. kundi, default ka sa previous value.

 

[bhoc84]

Re: MS excel formula, programming, analysis, presentation and tutorials.

ok. maraming salamat. pag aralan ko rin muna.


500 pla dapat sagot dun sa halip na 5, from formula na =F21/E21*H21

 

[themonyo]

Re: MS excel formula, programming, analysis, presentation and tutorials.

ok. maraming salamat. pag aralan ko rin muna.


500 pla dapat sagot dun sa halip na 5, from formula na =F21/E21*H21

Ay, sorry
Di ko napansin na may silbi pala yung column H

=IF(AND(E20>0;F20>0);F20/E20*H19;I19)

 

[bhoc84]

Re: MS excel formula, programming, analysis, presentation and tutorials.

yes sir,bale given lagi yung nasa H

 

[kirby21]

Re: MS excel formula, programming, analysis, presentation and tutorials.

mga boss ano mga work nyo?

 

[chensen]

Re: MS excel reportings, presentations, and vba helping thre

Thanks for sharing po

 

[puuts]

Re: MS excel formula, programming, analysis, presentation and tutorials.

mga boss ano mga work nyo?

Automation / Developer boss.

 

[chot07]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Sub RoundedRectangle1_Click()
Dim i As Long, lastrow As Long, n As Long
Dim vResult()
Dim myWs As Worksheet

Set myWs = ThisWorkbook.Sheets(Range("B9").Text)

If ActiveSheet.Range("b4") = "" Or ActiveSheet.Range("b5") = "" Or ActiveSheet.Range("b6") = "" Or ActiveSheet.Range("b7") = "" Or ActiveSheet.Range("b8") = "" Or ActiveSheet.Range("b9") = "" Or ActiveSheet.Range("d4") = "" Or ActiveSheet.Range("d6") = "" Or ActiveSheet.Range("d7") = "" Or ActiveSheet.Range("d8") = "" Then
MsgBox "Please complete all fields!"
Exit Sub
End If

i = 10
Do While Cells(i, 3) <> "" And i < 30
n = n + 1
ReDim Preserve vResult(1 To 9, 1 To n)
vResult(1, n) = ActiveSheet.Range("E7")
vResult(2, n) = ActiveSheet.Range("B4")
vResult(3, n) = ActiveSheet.Range("E4")
vResult(4, n) = ActiveSheet.Range("B5")
vResult(5, n) = ActiveSheet.Range("B6")
vResult(6, n) = ActiveSheet.Range("E6")
vResult(7, n) = ActiveSheet.Range("B7")
vResult(8, n) = ActiveSheet.Range("B8")
vResult(9, n) = ActiveSheet.Range("E8")
i = i + 1

Loop

myWs.Range("a" & Rows.Count).End(xlUp)(2).Resize(n, 9) = WorksheetFunction.Transpose(vResult)

MsgBox "Saved Succesfully!"
ThisWorkbook.Save
End Sub

mga boss patulong, yan kasi yung vba code ko sa excel, ang gusto ko kasing mangyari is, magtransfer ng data si excel from sheet1 to another sheet na determined by cell B9, so kung anong sheet name ang nakalagay kay cell B9, dun mapupunta ang data, kaso may error sya "Invalid procedure call or argument" , yan ang lumalabas, I managed na mawala ang error, kaso ang problema naman is yung data hindi nattransfer oh bigla na lang nawawala ang data without transferring to its respective sheets

 

[puuts]

Re: MS excel formula, programming, analysis, presentation and tutorials.

mga boss patulong, yan kasi yung vba code ko sa excel, ang gusto ko kasing mangyari is, magtransfer ng data si excel from sheet1 to another sheet na determined by cell B9, so kung anong sheet name ang nakalagay kay cell B9, dun mapupunta ang data, kaso may error sya "Invalid procedure call or argument" , yan ang lumalabas, I managed na mawala ang error, kaso ang problema naman is yung data hindi nattransfer oh bigla na lang nawawala ang data without transferring to its respective sheets

Pa-attach boss ng sample file mo

 

[chot07]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Pa-attach boss ng sample file mo

ito boss

http://www.symbianize.com/attachment.php?attachmentid=1210123&stc=1&d=1500286960

 

[puuts]

Re: MS excel formula, programming, analysis, presentation and tutorials.

ito boss

http://www.symbianize.com/attachment.php?attachmentid=1210123&stc=1&d=1500286960

boss pwede po palagay ng sample output na gusto nyo makuha. And yung ENCODE na sheet, ganyan talaga format nyan? hindi na yan pwede mabago? or pwede yan baguhin?

 

[athenesia]

Re: MS excel formula, programming, analysis, presentation and tutorials.

mga boss patulong, yan kasi yung vba code ko sa excel, ang gusto ko kasing mangyari is, magtransfer ng data si excel from sheet1 to another sheet na determined by cell B9, so kung anong sheet name ang nakalagay kay cell B9, dun mapupunta ang data, kaso may error sya "Invalid procedure call or argument" , yan ang lumalabas, I managed na mawala ang error, kaso ang problema naman is yung data hindi nattransfer oh bigla na lang nawawala ang data without transferring to its respective sheets

hindi talaga magtatransfer yan kasi yung condition mu sa loop false na agad.

Do While Cells(i, 3) <> "" And i < 30

yung Cells(i,3) mu walang laman so hindi talaga nya gagawin yung code mu sa loob ng loop.

 

[chot07]

Re: MS excel formula, programming, analysis, presentation and tutorials.

boss pwede po palagay ng sample output na gusto nyo makuha. And yung ENCODE na sheet, ganyan talaga format nyan? hindi na yan pwede mabago? or pwede yan baguhin?

Di pa final boss ung format ng encode, pero halos same lang din, kulang langnng formatting.

 

[albert3]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Hi Master,

hihingi sana ako ng tulong kung paano ko automatic na masisave(desktop) sa notepad yung content nung excel sheet and save using din yung sheet name ng worksheet.

 

[athenesia]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Hi Master,

hihingi sana ako ng tulong kung paano ko automatic na masisave(desktop) sa notepad yung content nung excel sheet and save using din yung sheet name ng worksheet.

try mu to.
Ctrl+Shift+S to run macro

 

[albert3]

Re: MS excel formula, programming, analysis, presentation and tutorials.

try mu to.
Ctrl+Shift+S to run macro

Thank you Master.

Paano po kung gusto ko sya i-autosave sa source directory nya?