true power of MS excel. update: PowerBI

[kirby21]

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

pano po yung sa pag generate ng random numbers..elp po,,tnx

=randbetween(1,100) ' this will generate random numbers from 1 - 100

 

[justasking]

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

Help po..

may 200,000 cells ako sa column A.. Paano ko ma se-select ung cells from 1-50,000?

kailangan ko kasi i-copy lahat un sa ibang excel, ang masaklap ginawa ko ung sa 1,000,000 + cells dati tapos mano-mano pa

 

[kirby21]

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

Help po..

may 200,000 cells ako sa column A.. Paano ko ma se-select ung cells from 1-50,000?

kailangan ko kasi i-copy lahat un sa ibang excel, ang masaklap ginawa ko ung sa 1,000,000 + cells dati tapos mano-mano pa

from home tab punta ka sa "find and select" -> "go to" -> type mo "A50000" tapos press ctrl + shift + up arrow

 

[justasking]

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

from home tab punta ka sa "find and select" -> "go to" -> type mo "A50000" tapos press ctrl + shift + up arrow

Ok salamat. Nagets ko na ung concept

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

mga bossing pa help nmn po sa coding gusto ko po kase na blank ang ilabas kapag ung catch niyang column eh blank ko din

sample po
eto code ko
=IFERROR(INDEX('Applicants Tracking_2015'!BA:BA,MATCH("( Project Mgt ) Shift Charge Engineer",'Applicants Tracking_2015'!G:G,0)),"")

code
'Applicants Tracking_2015'!BA:BA = date po
e kpag blank po kase ang lumabas na data January 0, 1900 gusto ko po sana eh blank po ang result po sana po matulungan nyo po ako

thank you advance po

 

[TheRoister]

Re: MS excel we give solutions. formula, codes and tutorials.

kailangan ko po kung pano nila icompute yun. at yung basis. maybe we could. but i need your help.

Ganito po boss. Try natin to. 60% for beauty and 40% for brains. For now kasi bossing wala pang enough na basis talaga para sa final scoring. Ang gusto ko lang sana malaman bossing eh pano yung constantly pagcheck or update sa mga iniinput na judges sa excel documents nila. Nakita ko kasi to dati na ginagamit sa isang pageant. Salamat po boss.

 

[mAsterA]

Re: MS excel we give solutions. formula, codes and tutorials.

meron po bang shortcut key or hotkey para sa next sheet naalt tab ko kasi hehe d naman pwede pano kaya...

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

meron po bang shortcut key or hotkey para sa next sheet naalt tab ko kasi hehe d naman pwede pano kaya...

ctrl + page up or ctrl + page down

- - - Updated - - -

mga bossing pa help nmn po sa coding gusto ko po kase na blank ang ilabas kapag ung catch niyang column eh blank ko din

sample po
eto code ko
=IFERROR(INDEX('Applicants Tracking_2015'!BA:BA,MATCH("( Project Mgt ) Shift Charge Engineer",'Applicants Tracking_2015'!G:G,0)),"")

code
'Applicants Tracking_2015'!BA:BA = date po
e kpag blank po kase ang lumabas na data January 0, 1900 gusto ko po sana eh blank po ang result po sana po matulungan nyo po ako

thank you advance po

=if(INDEX('Applicants Tracking_2015'!BA:BA,MATCH("( Project Mgt ) Shift Charge Engineer",'Applicants Tracking_2015'!G:G,0))=0,"",INDEX('Applicants Tracking_2015'!BA:BA,MATCH("( Project Mgt ) Shift Charge Engineer",'Applicants Tracking_2015'!G:G,0)))

- - - Updated - - -

Ganito po boss. Try natin to. 60% for beauty and 40% for brains. For now kasi bossing wala pang enough na basis talaga para sa final scoring. Ang gusto ko lang sana malaman bossing eh pano yung constantly pagcheck or update sa mga iniinput na judges sa excel documents nila. Nakita ko kasi to dati na ginagamit sa isang pageant. Salamat po boss.

actually ratings lang ang nakikita kong solution.
each judge kailangan irate si contestant 1-60 for beauty and 1-40 for brain. after nila irate ang contestant may button sa excel which will transmit the score to another file. i mean yung database, then from there yun ang magtotal ng scores and sort it from largest to smallest points para makita agad kung sino ang grand winner 2nd and 3rd runner up.

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

Boss kirby21 thank you po.. Sir pwde nyo po ba explain pano po nangyari ehhehe thank you po advance

Big Help po ito
ctrl + page up or ctrl + page down

Sir Additional question po
Papano po mag copy paste ng code na d po sia nag #REF po may gnawa po kase akong code for sheet1 tpos paste ko po sa sheet2 pero nung paste ko na siya sa sheet2 nag #REF po sia pano po kaya magadang gawin po pag copy paste ng code po kase maraming column po eh pero same code pra sa lahat kase...

sample code:

=COUNTIFS('Applicants Tracking_2015'!I:I,'Recruitment Summary 2015'!$Z$8,'Applicants Tracking_2015'!I:I,'Recruitment Summary 2015'!$AA$8,'Applicants Tracking_2015'!G:G,"( Project Mgt ) Shift Charge Engineer")

ang nag babago lang kase = "( Project Mgt ) Shift Charge Engineer"

19column po kase sia eh mahrp po kase na mano mano sia edit kya need ko po tlga copy paste pra mas mabilis po ung work thank you po advance

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

Boss kirby21 thank you po.. Sir pwde nyo po ba explain pano po nangyari ehhehe thank you po advance

Big Help po ito
ctrl + page up or ctrl + page down

Sir Additional question po
Papano po mag copy paste ng code na d po sia nag #REF po may gnawa po kase akong code for sheet1 tpos paste ko po sa sheet2 pero nung paste ko na siya sa sheet2 nag #REF po sia pano po kaya magadang gawin po pag copy paste ng code po kase maraming column po eh pero same code pra sa lahat kase...

sample code:

=COUNTIFS('Applicants Tracking_2015'!I:I,'Recruitment Summary 2015'!$Z$8,'Applicants Tracking_2015'!I:I,'Recruitment Summary 2015'!$AA$8,'Applicants Tracking_2015'!G:G,"( Project Mgt ) Shift Charge Engineer")

ang nag babago lang kase = "( Project Mgt ) Shift Charge Engineer"

19column po kase sia eh mahrp po kase na mano mano sia edit kya need ko po tlga copy paste pra mas mabilis po ung work thank you po advance

=if(INDEX('Applicants Tracking_2015'!BA:BA,MATCH("( Project Mgt ) Shift Charge Engineer",'Applicants Tracking_2015'!G:G,0))=0,"",INDEX('Applicants Tracking_2015'!BA:BA,MATCH("( Project Mgt ) Shift Charge Engineer",'Applicants Tracking_2015'!G:G,0)))

himayin natin, hehehe.

if lang naman sya, kasi kung kasi pag ang result ng date is 1/1/1900 diba? to convert sya into value lang 0 lang yan. nakita ko index match lang ginamit mo para lumabas result diba?
so eto ang explanation.

if result of index match = 0 then blank
else
indexmatch ulit.
that's it.

ilock mo yung mga referrence. lagyan mo ng $ alam mo naman paggamit nyan diba?

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

ah bali ung dollar sign po eh pang lock ng code pra d gumalaw or mag increase tama po b?

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

ah bali ung dollar sign po eh pang lock ng code pra d gumalaw or mag increase tama po b?

yes for example sa c1 meron kang formula na =a1-b1
pagcopy paste mo sya sa c2 magiging =a2-b2 sya. but pag ginawa mo syang =$a$1-$b$1 at copy mo sa c2, =$a$1-$b$1 parin sya.
eto pa.

=$a$1

yung first $ is for column yung $ is lock naman sa row.

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

Thanks boss big help may natutunan ako ^^ nung nakita ko ung dollar sign sabi ko para saan un... un pla ang gamit nun ^^

gumagwa po b kayo ng payroll formula?

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

Thanks boss big help may natutunan ako ^^ nung nakita ko ung dollar sign sabi ko para saan un... un pla ang gamit nun ^^

gumagwa po b kayo ng payroll formula?

may mga ginawa na ko pero hindi sa regular work ko kasi yun eh.

 

[ronnie1271]

Re: MS excel we give solutions. formula, codes and tutorials.

sir , paturo po ng excel, kailanga ko po sa reporting

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

sir , paturo po ng excel, kailanga ko po sa reporting

ano ba kailangan mo sa report mo?

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

meron po ba kau mga sample computing for how many yrs of one employee sir kano makukuha nya ?

 

[mAsterA]

Re: MS excel we give solutions. formula, codes and tutorials.

kirby21

ctrl + page up or ctrl + page down....



boss salamat po sa pag sagot... malaking tulong po... pahabol lang po mron po ba tutorial for beginer ng excel mag uumpisa pa lang po ako from the very beginning salamat po...

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

meron po ba kau mga sample computing for how many yrs of one employee sir kano makukuha nya ?

years in service ba?

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

opo sir.. mern ka po b nun