true power of MS excel. update: PowerBI

[pelera]

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

Hello guys ginawa ko lang tong thread para sa mga kasymb natin na need ng tulong about MS excel. mga reporting staff, encoders, data analyst, secrectary, or Ms excel systems. pasok po kayo dito. share tayo ng knowledge. i will provide tutorials too. believe me after learning this things after a month i got my promotion to go up another level from our company. so try it now. made your bosses like their jaws drop.​

on my first example. you will see chart with controls like the picture below.
View attachment 887637

here you will see conditional formatting with toggle.like the picture below.
View attachment 887650

now you will see a picture album using excel. but in this tutorial i'll give you an example of animal album.
View attachment 888228

in this picture you will see a chart that looks like a temperature. it would be helpful to a presentations with a sales and target sales.
View attachment 888874

now in this picture you will see a speedo meter chart with control. the tutorial was provided from the attachment.
View attachment 888895

at eto naman ang sample dashboards ko

sample 1
http://i.giphy.com/xTiTnzLUI4whphKpoc.gif

sample 2
http://i.giphy.com/3o85xkmN4eEdkNV6Zq.gif
​

if nagustohan nyo po. pwede nyo sya idownload sa baba.

digital clock by dhok si...

​

sana po magshare din ang iba,


grading system excel application upload by our contributors. http://www.symbianize.com/showthread.php?t=1132342&p=19858679&viewfull=1#post19858679

eto na po para yung iba di na masyado magtanong sa excel tricks. lalo na mga charts with controls. im giving you all this.

http://i.imgsafe.org/19867c1.jpg

Excel hacks.rar


eto po splitted into two parts. thanks to devildarkrs
part 1
http://www.symbianize.com/attachment.php?attachmentid=1043979&d=1436253924
part 2
http://www.symbianize.com/attachment.php?attachmentid=1043978&d=1436253924
credits to creator of the book.

HOW TO UNLOCK VBA PROJECT
credits goes to athenesia​

Maraming salamat dito master. Working po ito.

 

[rapz94]

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

sir bytesize, sir Kirby.

View attachment 292672

possible ba yan? or anyway para maging ganyan?

TIA mga Sir.

 

[fleshy]

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

salamat dito ts

 

[bytesize]

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

sir bytesize, sir Kirby.

View attachment 1164042

possible ba yan? or anyway para maging ganyan?

TIA mga Sir.

possible sir. gumawa lang ako ng isa pang column para itest yung values sa number column kung space sya. if true, kinuha ko yung max value nila, if false, space lang siya. tapos pinlot ko na. pinalitan ko lang ung BAR nung control column para maging picture na nakalagay "no data" hehe. siguro si sir kirby may iba pang solution or yung iba dyan na magaling sa charting, pa share naman ng solution nyo. ciao

View attachment 292710

 

[rapz94]

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

possible sir. gumawa lang ako ng isa pang column para itest yung values sa number column kung space sya. if true, kinuha ko yung max value nila, if false, space lang siya. tapos pinlot ko na. pinalitan ko lang ung BAR nung control column para maging picture na nakalagay "no data" hehe. siguro si sir kirby may iba pang solution or yung iba dyan na magaling sa charting, pa share naman ng solution nyo. ciao

View attachment 1164094

pwde nga sir bytesize, ayos. Salamat paturo din ako vba haha..

 

[xdocmer06x]

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

Sir, ito pagkakaintindi ko. I assume marunong ka po mag VBA kahit konti

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Randomize

If Selection.Count = 1 Then
If Target.Value < TimeValue("07:23") And Target.Value <> "" Then

Target.Value = DateAdd("n", Int((12 - 0 + 1) * Rnd + 0), "7:23")

End If

If Target.Value > TimeValue("04:37") And Target.Value <> "" And Selection.Count = 1 Then

Target.Value = DateAdd("n", Int((7 - 0 + 1) * Rnd + 0), "4:30")

End If
End If

End Sub

Sir Salamat ng marami dito. Tama ka, hindi ako gaano marunong sa VBA. Anyway, sir napansin ko lang sa script mo sa taas bakit wala yata ang 7:35 AM?

Susubukan ko siya sir, feedback ako afterwards.

For now naremedyuhan ko siya gamit ang simpleng if statements lang at randbetween sa excel.

Salamat uli sa pagpansin sa post ko.

Edit:

Sir heto yung ginawa ko na formula , baka may work up mo pa yung unang bigay mo sa akin. Bale per cell yan applicable.

=IF(J32<N32,RANDBETWEEN(TIMEVALUE("07:23 AM") * 10000, TIMEVALUE("07:35 AM") * 10000) / 10000,J32)
=IF(K32>O32,RANDBETWEEN(TIMEVALUE("4:30 PM") * 10000, TIMEVALUE("4:37 PM") * 10000) / 10000,K32)

 

[bytesize]

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

Sir Salamat ng marami dito. Tama ka, hindi ako gaano marunong sa VBA. Anyway, sir napansin ko lang sa script mo sa taas bakit wala yata ang 7:35 AM?

Susubukan ko siya sir, feedback ako afterwards.

For now naremedyuhan ko siya gamit ang simpleng if statements lang at randbetween sa excel.

Salamat uli sa pagpansin sa post ko.

nandito yung 7:35 sir. --> DateAdd("n", Int((12 - 0 + 1) * Rnd + 0), "7:23")

yung part na: Int((12 - 0 + 1) * Rnd + 0) ay magrarandomize ng value from 0-12. so gagawin ni DateAdd mag a-add siya ng randomized value from 0 to 12 sa "7:23". so kapag ang lumabas na randomized value ay 12, magiging 7:23 + 12 minutes = 7:35

- - - Updated - - -

Sir Salamat ng marami dito. Tama ka, hindi ako gaano marunong sa VBA. Anyway, sir napansin ko lang sa script mo sa taas bakit wala yata ang 7:35 AM?

Susubukan ko siya sir, feedback ako afterwards.

For now naremedyuhan ko siya gamit ang simpleng if statements lang at randbetween sa excel.

Salamat uli sa pagpansin sa post ko.

Edit:

Sir heto yung ginawa ko na formula , baka may work up mo pa yung unang bigay mo sa akin. Bale per cell yan applicable.

=IF(J32<N32,RANDBETWEEN(TIMEVALUE("07:23 AM") * 10000, TIMEVALUE("07:35 AM") * 10000) / 10000,J32)
=IF(K32>O32,RANDBETWEEN(TIMEVALUE("4:30 PM") * 10000, TIMEVALUE("4:37 PM") * 10000) / 10000,K32)

sir, sorry di ko alam na formula pala hanap mo. kase sabi mo "if selected cells" kaya inicip ko gusto mong gawin vba na pag ni-click mo yung isang cell, magbabago siya automatically pag nameet yung criteria. anyway. tama naman yang ginawa mo. eto medyo mas maiksi na formula:

=IF(J32<N32,(RANDBETWEEN(0,12)/1000)+N32,TEXT(J32,"hh:mm"))
=IF(K32>O32,(RANDBETWEEN(0,7)/1000)+O32,TEXT(K32,"hh:mm"))

 

[kirby21]

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

hello guys. someone just sent me a pm.
asking for HR system.

sino po ba meron dyan?
@jperson abang ka muna dito ah.

meron ka ba existing? para if ever may mag develop ng gamit mo?

 

[Joey Orale]

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

Sir... pano ba i unshared ang excel file? nakalimutan ko kc ang password... huhuhu

 

[joperd]

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

Mga sir Question naman po.
meron akong simpleng production log sheet.

tas may command button ako.
eto nakalagay na macro.

Private Sub CommandButton1_Click()
lngCol = IIf(CommandButton1.Caption = "End Activity", 3, 2)
lngRow = ActiveSheet.Cells(Rows.Count, lngCol).End(xlUp).Row + 1
Cells(lngRow, lngCol) = Now()
CommandButton1.Caption = IIf(CommandButton1.Caption = "End Activity", "Start Activity", "End Activity")

End Sub


gusto ko sana dagdagan ng function na.. yung end time automatic na start time na ng new task.


eto yung screenshots. 3 clicks yan ng button.
View attachment 293570View attachment 293571View attachment 293572View attachment 293572



Thanks

 

[bytesize]

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

Mga sir Question naman po.
meron akong simpleng production log sheet.

tas may command button ako.
eto nakalagay na macro.....

sorry nakauwi nako pwede ko na itest. eto nagana na tong code na to. ehhehehe.

Private Sub CommandButton1_Click()
lngcol = IIf(CommandButton1.Caption = "End Activity", 3, 2)
lngrow = ActiveSheet.Cells(Rows.Count, lngcol).End(xlUp).Row + 1

If (Cells(lngrow - 1, 3) = "End Time") And lngcol = 2 Then
Cells(lngrow, lngcol) = Now()
GoTo end1
End If

If (lngcol = 3) Then
Cells(lngrow, lngcol) = Now()
GoTo end1
End If

Cells(lngrow, lngcol) = Cells(lngrow - 1, 3)
end1:
CommandButton1.Caption = IIf(CommandButton1.Caption = "End Activity", "Start Activity", "End Activity")

End Sub

 

[kirby21]

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

sorry nakauwi nako pwede ko na itest. eto nagana na tong code na to. ehhehehe.

Private Sub CommandButton1_Click()
lngcol = IIf(CommandButton1.Caption = "End Activity", 3, 2)
lngrow = ActiveSheet.Cells(Rows.Count, lngcol).End(xlUp).Row + 1

If (Cells(lngrow - 1, 3) = "End Time") And lngcol = 2 Then
Cells(lngrow, lngcol) = Now()
GoTo end1
End If

If (lngcol = 3) Then
Cells(lngrow, lngcol) = Now()
GoTo end1
End If

Cells(lngrow, lngcol) = Cells(lngrow - 1, 3)
end1:
CommandButton1.Caption = IIf(CommandButton1.Caption = "End Activity", "Start Activity", "End Activity")

End Sub

i sent you pm. please reply asap.

 

[bytesize]

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

i sent you pm. please reply asap.

sir nagreply na ko. hindi ko alam kung nakukuha nyo kase di lumalabas sa sent items.

 

[kirby21]

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

sir nagreply na ko. hindi ko alam kung nakukuha nyo kase di lumalabas sa sent items.

ok na pre. kitakits sa monday afternoon.

 

[dannyps2]

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

Mga sir, Good day po!

may question po ako.

gusto ko sana gawan nang code paano e copy from encoding sheet to database sheet ang mga data sa encoding sheet ng sabay sabay, then e clear nya yong encoding para mag encode na naman ulit. parang encoding form kaya lang maramehan yong data kasi sheet yong gamit ng user.

attach po yong files. salamat po!

 

[bytesize]

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

Mga sir, Good day po!

may question po ako.

gusto ko sana gawan nang code paano e copy from encoding sheet to database sheet ang mga data sa encoding sheet ng sabay sabay, then e clear nya yong encoding para mag encode na naman ulit. parang encoding form kaya lang maramehan yong data kasi sheet yong gamit ng user.

attach po yong files. salamat po!

eto po yung code:

Private Sub CommandButton1_Click()

Dim t As Integer
Dim f As Integer

t = 1
f = 1

t = Application.WorksheetFunction.CountA(Worksheets("Encoding").Range("a:a")) + 1
f = Application.WorksheetFunction.CountA(Worksheets("Database").Range("a:a")) + 1

Worksheets("Encoding").Range("A2:J" & t).Copy _
Destination:=Worksheets("Database").Range("A" & f)

Worksheets("Encoding").Range("A2:J" & t) = ""

End Sub

 

[dannyps2]

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

salamat po! good day...

 

[mistah0209]

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

mga sers... patulong nmn po kung mrn kayo codes s excel for searching any file and then will goto/show its path/location to other cell.. salamat po

 

[bytesize]

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

mga sers... patulong nmn po kung mrn kayo codes s excel for searching any file and then will goto/show its path/location to other cell.. salamat po

Google is the bestfriend of lazy coders =)

i forked the sub routine from here: http://kbase.icbconsulting.com/vba/vba-to-find-where-a-file-is-stored-using-the-file-name

eto yun main sub routine

Private Sub CommandButton1_Click()
Dim fname As String
Dim kpath As String
Dim result As String


fname = Sheet1.Cells(2, 2)
kpath = Sheet1.Cells(3, 2)
result = ListFiles(kpath, fname, True) 'see link for the code, or double click module1 in vba editor.
Sheet1.Cells(4, 2) = result

If result = "" Then
Sheet1.Cells(4, 2) = "No results found =("
End If


End Sub

excel sheet formulas:
the formula below isolates the filename from the path ; if error, filename will be B1
B2: =IFERROR(MID(B1,FIND("*",SUBSTITUTE(B1,"\","*",LEN(B1)-LEN(SUBSTITUTE(B1,"\",""))))+1,LEN(B1)),B1)

the formula below isolates the path from the filename ; if path is space, path will be "c:\"
B3: =IF(IFERROR(LEFT(B1,SEARCH(B2,B1)-1),"")="","c:\",LEFT(B1,SEARCH(B2,B1)-1))

Note: hidden yung rows ng kpath at filename para malinis tignan. i unhide nyo nalng.
PS: Mabagal mag search kapag wala kang isu-supply na known path, kase by default ise-search nya yung buong c:\ drive para sa file.

BUG ALERT: di siya makakapag-search sa root drive kung saan naka install yung windows. kailangan magsupply ka ng kahit isang known folder or path para gumana eg: c:\testfolder\
di ko alam baket, i assume dahil sa security features ng windows 10. ewan. wala na ako time i trace. hehehe. this is the best i could do. sa ibang drives (partioned, or slave drives) gagana siya perfectly kahit maglagay ka pa ng wildcards.

 

[jkborbz]

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

More power sa thread na to!