true power of MS excel. update: PowerBI

[ohohOrmhel]

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

Mga Master patulong naman po sa ginagawa kong Collection report. May naumpisahan na ko kaso hindi ko alam pano ko koConnect monthly Collection na nagbayad.

Ganito ba paps gusto mo? See attach.

- - - Updated - - -

mga master baka di na kayo busy patulong po ulit sa formula

http://i64.tinypic.com/2ijnbra.jpg

Tell us more pa paps. Hindi ko gets ung gusto mong result hehe

 

[wakokohekhek]

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

mga masters patulong po ulit sa bago kong project. malamang macro or vba na kelangan dito.
naka attach po yung working sheet ko and sa gusto ko pong maging output

View attachment 1184806

explain mo sakin to ng mas malinaw sir! tulungan kita.

 

[BurtKocaine]

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

explain mo sakin to ng mas malinaw sir! tulungan kita.

dun po sir sa attachment andun po yung sheet ng BOM ( quantity per process ) and Capacity ( available capacity ), bale dun sa result sheet ay sum of all products
(BOM sheet) * input qty ( Result sheet) / Capacity ( Process)

 

[learsi]

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

@BurtKocaine sa newproject , master na try mo na ang if-function?

 

[BurtKocaine]

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

@BurtKocaine sa newproject , master na try mo na ang if-function?

@learsi

yes sir kaso 3 po yung variable, product level at item. yung x ng item yung multiplier na level 2 ng product

 

[themonyo]

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

@learsi

yes sir kaso 3 po yung variable, product level at item. yung x ng item yung multiplier na level 2 ng product

Eh di nested if statements. Ang primary naman yung may "x" eh. Subukan ko pag makahawak ako ng pc. Cp mode

edit: paki-attach naman yung excel file nung may screenshot ng table sa taas para di ko na retype yung buong table mo.

 

[athenesia]

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

dun po sir sa attachment andun po yung sheet ng BOM ( quantity per process ) and Capacity ( available capacity ), bale dun sa result sheet ay sum of all products
(BOM sheet) * input qty ( Result sheet) / Capacity ( Process)

ganito ba yun dapat ang result?
mas madali yan pag sa Sheets lang din yung Input mu.

pero pag gusto mu talaga sa Form, pwede din natin gawan na paraan.

 

[BurtKocaine]

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

ganito ba yun dapat ang result?
mas madali yan pag sa Sheets lang din yung Input mu.

pero pag gusto mu talaga sa Form, pwede din natin gawan na paraan.

@ athenesia ganyan nga po ang gusto kong mangyari. pero mas gusto ko po sana yung nasa Form ^_^

 

[rodDfrog]

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​

Salamat dito TS...laking tulong to sa mga reports

 

[athenesia]

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

@ athenesia ganyan nga po ang gusto kong mangyari. pero mas gusto ko po sana yung nasa Form ^_^

Eto sample code lang to ha..
pwede mu to imodify base sa report mu.

click mu lang yung button para lumabas yung form.

 

[eizelz]

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

thanks dito.

 

[tetewc]

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

TS

 

[Jasffer192324]

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

thanks po d2 t.s.

 

[themonyo]

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

@BurtKocaine

heto nested IF statement ng table mo. pahaba lang ng pahaba. ginawa ko up to 6 levels. mahirap lang kung walang level 1.

=IF(AND(B2=1,D2="X")=TRUE,E2,IF(INDIRECT("D"&ROW()-1)="X",E2*INDIRECT("E"&ROW()-1),IF(INDIRECT("D"&ROW()-2)="X",E2*INDIRECT("E"&ROW()-2),IF(INDIRECT("D"&ROW()-3)="X",E2*INDIRECT("E"&ROW()-3),IF(INDIRECT("D"&ROW()-4)="X",E2*INDIRECT("E"&ROW()-4),IF(INDIRECT("D"&ROW()-5)="X",E2*INDIRECT("E"&ROW()-5),"array"))))))

yung sa dulo na array, you can just copy the portion ng last IF argument and replace the number ng 6 and higher depende sa level na gusto pahabain.

 

[BurtKocaine]

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

@BurtKocaine

heto nested IF statement ng table mo. pahaba lang ng pahaba. ginawa ko up to 6 levels. mahirap lang kung walang level 1.


yung sa dulo na array, you can just copy the portion ng last IF argument and replace the number ng 6 and higher depende sa level na gusto pahabain.

anlufet idol nadale mo batang bata!

yung buong file ko nasa around 30K rows

- - - Updated - - -

Eto sample code lang to ha..
pwede mu to imodify base sa report mu.

click mu lang yung button para lumabas yung form.

@athenesia working po yung form pano kung pati product ay variable? nasa around 1000 po kasi yung product gusto kong palabasin at least 20 products lang choice

 

[themonyo]

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

anlufet idol nadale mo batang bata!

yung buong file ko nasa around 30K rows

- - - Updated - - -



@athenesia working po yung form pano kung pati product ay variable? nasa around 1000 po kasi yung product gusto kong palabasin at least 20 products lang choice

i think with that kind of spreadsheet, mas madali at maganda kung sa database (Access) mo sya ipasok. mas madali iset yung variables with unlimited data entry and with only 1 reference or parent dependent. sa nakikita ko rin sa spreadsheet mo, mahirap mag-filter at mag-sort.

edit: may naalala ako... how about kung yung entire products mo nasa isang worksheet along with their part number and category, tapos kung gagawa ka na ng table (query) with the necessary numbers and their quantity sa separate sheet? gagawa ka ng vlookup table with drop-down selection of your products and you can still make each entry.

 

[BurtKocaine]

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

i think with that kind of spreadsheet, mas madali at maganda kung sa database (Access) mo sya ipasok. mas madali iset yung variables with unlimited data entry and with only 1 reference or parent dependent. sa nakikita ko rin sa spreadsheet mo, mahirap mag-filter at mag-sort.

edit: may naalala ako... how about kung yung entire products mo nasa isang worksheet along with their part number and category, tapos kung gagawa ka na ng table (query) with the necessary numbers and their quantity sa separate sheet? gagawa ka ng vlookup table with drop-down selection of your products and you can still make each entry.

actually extracted po eto galing SAP/Oracle. filtered and sorted na po nung na extract ko, pinagsama sama ko lang po yung mga product na active. anyway sir working naman po yung formula umabot ng 15 arrays pala ang kailangan ko.

 

[sekaraininku]

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

Mga master may tanong lang po ako. pwede po ba na ang certain result sa calculation is pwede xa mag appear in a certain date dun sa certain cell. example is ang lahat ng result ng calculation this week is bigla xang mag appear sa isang cell automatically on monday 6 am. or kahit date lang wala ng exact time. tanx!

 

[ohohOrmhel]

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

Mga master may tanong lang po ako. pwede po ba na ang certain result sa calculation is pwede xa mag appear in a certain date dun sa certain cell. example is ang lahat ng result ng calculation this week is bigla xang mag appear sa isang cell automatically on monday 6 am. or kahit date lang wala ng exact time. tanx!

Bigay ka sir ng sample file para mas madali

 

[sekaraininku]

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

eto po sir, bale yung result sa real time status pagka monday automatic na xa dun mapupunta sa weekly result. no need na to input manually. nag try na ako ng today function. pro ayaw nya eh. salamat po sa tutulong.

https://www.dropbox.com/s/voak5d3t5lpee2j/autofill%20by%20date.xlsx?dl=0