true power of MS excel. update: PowerBI

[realnapster]

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

Okay gets. Per region ang lipat ng data or Bulk (Kelangan muna fill-apan lahat ng regions)?

Ganto po yung process. Yung per gion may isang Analyst naka assigned diyan kaya need ko real time makita yung updates kasi daily ko tinitignan yan to follow up yung Analyst regarding sa Issue. Tapos meron akong per week and per month na reports for open and close issues. Pede try ko parehas Sir Puuts?

 

[puuts]

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

Ganto po yung process. Yung per gion may isang Analyst naka assigned diyan kaya need ko real time makita yung updates kasi daily ko tinitignan yan to follow up yung Analyst regarding sa Issue. Tapos meron akong per week and per month na reports for open and close issues. Pede try ko parehas Sir Puuts?

Dagdag lang ba ng dagdag sila ng data? Or pwede din nila i-edit yung mga nagawa na nila?

 

[merspakdat]

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

Salamat kaayo Boss

 

[realnapster]

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

Dagdag lang ba ng dagdag sila ng data? Or pwede din nila i-edit yung mga nagawa na nila?

Status na lang iedit nila kung close na and magadd sila ng doc number.

 

[realnapster]

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

Sir Puuts any update po?

 

[rapz94]

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

Pa tulong mga sir, pwde ba to?

 

[themonyo]

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

Pa tulong mga sir, pwde ba to?

pwede
pinakamadali ay open mo mga workbook mo na gusto i-link
punta sa range ng data na gusto mo i-link and select them
press ctr+C (or right click > copy)
punta ka sa workbook na kung saan mo gusto naka-display ang lahat ng data
select paste special, paste as link, excel binary or something similar

may sinsubukan ako using indirect() function, kaso di ko mapagana

 

[romcel11]

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

may sinsubukan ako using indirect() function, kaso di ko mapagana

medyo tricky po gamitin ung idirect. lalo na kung gagamitin mo sya for referencing outside the workbook.
syntax sa pag refer sa workbook:
= "[" + workbookname + "]" + sheetname + "!" + cell address

sample if wlang spaces ung workbook at sheet name:
=[WBname.xlsx]SHTName!C9

sample kung merong spaces ung workbook at sheet name:
='[WB name.xlsx]SHT Name'!C9

notice ung single quote.


another cons ng pag gamit ng indirect function kpag mgrerefer sa ibang workbook,
ay kpag ngpalit ng file name or kahit sheet name nag error.

meron naman din work around to gamt ka lang ng cell na mg rereturn ng worksheet name(ung pinalitan ha or ung malimit palitan ng name).
tapos concatenate mo lang ung value. yun na!

 

[nightshift207]

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

salamat ts. patambay po

 

[rapz94]

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

medyo tricky po gamitin ung idirect. lalo na kung gagamitin mo sya for referencing outside the workbook.
syntax sa pag refer sa workbook:
= "[" + workbookname + "]" + sheetname + "!" + cell address

sample if wlang spaces ung workbook at sheet name:
=[WBname.xlsx]SHTName!C9

sample kung merong spaces ung workbook at sheet name:
='[WB name.xlsx]SHT Name'!C9

notice ung single quote.


another cons ng pag gamit ng indirect function kpag mgrerefer sa ibang workbook,
ay kpag ngpalit ng file name or kahit sheet name nag error.

meron naman din work around to gamt ka lang ng cell na mg rereturn ng worksheet name(ung pinalitan ha or ung malimit palitan ng name).
tapos concatenate mo lang ung value. yun na!

OK po sir try ko. tnx

 

[gamechanger7890]

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

pabm master. sigurado maraming matutulungan nito. ayus mga sample!

 

[gerardphil]

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

For those who need help, paki Viber ako anytime sa 09568074086. I'll be glad to help for free.

 

[albert3]

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

Hi Master papatulong po sana ako.

merong po kasi akon mga notepad na files na galing sa excel workbook. ngayon po, pwede po kayang malagyan ng "completed" na word sa dulo ng bawat files once na na import ko na sya sa aming website? gaya po ng halimbawa na nakaattached.

Maraming salamat po sa makakasagot!View attachment 337673View attachment 337674

 

[darryless]

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

Hi guys pa help po. Medyo bago palang po ako. I have roster file ng company namin with survey answers as raw data. Kelangan ko po gumawa ng macro na makakapagsulat sa rows ng mga questions na need ng clarification for each employee.

 

[jskhulitz]

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

Hi Master papatulong po sana ako.

merong po kasi akon mga notepad na files na galing sa excel workbook. ngayon po, pwede po kayang malagyan ng "completed" na word sa dulo ng bawat files once na na import ko na sya sa aming website? gaya po ng halimbawa na nakaattached.

Maraming salamat po sa makakasagot!View attachment 1246172View attachment 1246173

Mukhang hindi na sakop ng excel ito dahil sa ibang system na dadaan ang file, UNLESS, meron ka scheduled task na always magche-check ng list of text files as compared dun sa mga file names na uploaded na sa database.

 

[romcel11]

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

Hi guys pa help po. Medyo bago palang po ako. I have roster file ng company namin with survey answers as raw data. Kelangan ko po gumawa ng macro na makakapagsulat sa rows ng mga questions na need ng clarification for each employee.

medyo mahirap to sir. patingin muna sana ng workbook kung merong available. para matignan kung built-in Excel functions ang gagamtin natin or VBA na.

Hi Master papatulong po sana ako.

merong po kasi akon mga notepad na files na galing sa excel workbook. ngayon po, pwede po kayang malagyan ng "completed" na word sa dulo ng bawat files once na na import ko na sya sa aming website? gaya po ng halimbawa na nakaattached.

Maraming salamat po sa makakasagot!View attachment 1246172View attachment 1246173

Questions:

merong po kasi akon mga notepad na files na galing sa excel workbook.

1) pano po ito na export/extract from excel workbook? through vba po ba? if yes, bka pedeng i-import mo nadin to sa website nyo through vba.

ngayon po, pwede po kayang malagyan ng "completed" na word sa dulo ng bawat files once na na import ko na sya sa aming website?

2) pano mo ini-import ung notepad files mo sa website nyo? mas mgnda kung gngwa mo to programatically para pati pag rename ng notepad files idamay mo na.

 

[azter]

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

Master

Formula only. Pass muna sa VBA.

Need ko advance filtering technique.

A B
1 W 1
2 W 2
3 W 3
4 C 2
5 C 3
6 D 1
7 blank row
8 D 1
9 W 2
10 blank row
11 W 1
12 C 3
13 D 2

Problem:
Need ko ma-filter getting unique values from column A with corresponding values on column B while ignoring blank rows inside a table. Without using pivot table.

Hoping for your kind help.

Thank you in advance.

 

[themonyo]

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

Master

Formula only. Pass muna sa VBA.

Need ko advance filtering technique.

A B
1 W 1
2 W 2
3 W 3
4 C 2
5 C 3
6 D 1
7 blank row
8 D 1
9 W 2
10 blank row
11 W 1
12 C 3
13 D 2

Problem:
Need ko ma-filter getting unique values from column A with corresponding values on column B while ignoring blank rows inside a table. Without using pivot table.

Hoping for your kind help.

Thank you in advance.

Pa-post ng gusto mong outputs. If() formula lang siguro yan. Ano yung argument ng "unique vales" na yan?

 

[azter]

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

Pa-post ng gusto mong outputs. If() formula lang siguro yan. Ano yung argument ng "unique vales" na yan?

Ung unique values ay siya rin yang nakikita mo sir.

W1
W2
W3
C3
C3
etc....

for example sa W1, since 2 ang nakikita mo, isang W1 lang kukunin mo as unique and then sa Table mo it shows na 2 ang quantity niya, parang countif lang. possible ma?

 

[darryless]

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

Thank you sir! I managed to make it po. Now, I need to create a break schedule tracker and use pivot table to show per cluster per team ung mga over breaks and lunch for each agents