true power of MS excel. update: PowerBI

[puuts]

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

sir, thank you so much for your reply. naka-attach na po yung mga files.

yung data source, yun po yung sample data na kinukunan ko everyday, then ang gagawin ko naman is i-vlookup ko yung data sa another sheet (yung result file naman) for each day. sobrang matrabaho po nito and need na gawin ko everyday. then may gagawin pa po akong mga analysis after ko kumuha ng data.

sana po you can help me figure out a way para di ko na kelangan i-vlook up yung mga data sa data source and automatic ma-update yung another file ko everyday.

thank you so much again.

Hello mistysparkles,

patry po neto. Pasensya po natagalan medyo busy po kasi sa work.

 

[carmeloco]

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

another update by our regular member from excel analytics.

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


101 Ready-to-Use Excel Formulas (Mr. Spreadsheet's Bookshelf)
Michael Alexander | Wiley | August, 2014 | 240 pages | English |

This is the sort of book that is well worth its space on the bookshelf. I recall using several of the formulas in this guide, but it has been long enough that most of them have slipped my mind. There are many interesting formulas that are new to me as well. This thin book is filled with practical excel formulas, presented in a concise manner. Introductory notes of sorts will precede a formula, with an explanation of how it works to follow. There are several alternatives offered, where applicable. Early chapters provide formula fundamentals, and progressively move into more intricate formulas further in. There are basic formulas for totals, complex formulas that refer to other worksheets, lookup formulas, string concatenation formulas, conditional formulas, and all sorts of fun things to have within reach. I love this handy little reference, and if the rest of the Mr. Spreadsheet's Bookshelf series of books is anything like this, it would make for quite the collection of just the facts guides that minimize the need to rummage through pages. With 101 Ready-to-Use Excel Formulas, when you need it, you quickly find it, and then you're done.

kesa gumawa pa ko ng thread dito ko nalang post if ok lang sayo TS? Malaking tulong to satin mga excel users

Or you can download here for more tips ~ http://extratorrent.cc/search/?search=101+excel&new=1&x=0&y=0

may new link pa po ba nito?

 

[mistysparkles]

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

Hi Sir, thank you so much for this. It's okay, i understand you still have work to attend to.

Can i use the codes with the actual files? Will they still work?

Thank you again!!

Hello mistysparkles,

patry po neto. Pasensya po natagalan medyo busy po kasi sa work.

 

[puuts]

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

Hi Sir, thank you so much for this. It's okay, i understand you still have work to attend to.

Can i use the codes with the actual files? Will they still work?

Thank you again!!

yes po magwwork po yan as long as yung actual files nyo is katulad nung binigay nyo saken. I mean yung mga columns nung data source at result.

 

[mistysparkles]

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

Sige, Sir. I will try po. Thank you so much, Sir. You're a lifesaver. Thanks for this thread. Really a big help.

yes po magwwork po yan as long as yung actual files nyo is katulad nung binigay nyo saken. I mean yung mga columns nung data source at result.

 

[dreadstar]

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

Need help po on an excel project. I have an excel file I use as a bookkeeping journal of my startup company's VAT reporting to BIR every month. The columns I have are date, TIN, company_name, address1, address2, OR_number, base_amount, VAT_amount and total amount. About half the entries are existing suppliers hence its quite tedious to input the same TIN, company_name, address1 and address2.

So I created another sheet and used it like a contact database with columns TIN, company_name, address1 and address2. If I find an existing TIN I would just copy / paste to the VAT report sheet. Malaking time saving but I want to improve some more.

Is there a way to make the VAT report sheet react so that when I input a TIN number that is already existing sa contact database, it would just automatically add the other fields company_name, address1 and address2? This would save a lot of time switching between sheets and copy / pasting manually.

Any help and suggestion appreciated. TIA.

 

[puuts]

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

Need help po on an excel project. I have an excel file I use as a bookkeeping journal of my startup company's VAT reporting to BIR every month. The columns I have are date, TIN, company_name, address1, address2, OR_number, base_amount, VAT_amount and total amount. About half the entries are existing suppliers hence its quite tedious to input the same TIN, company_name, address1 and address2.

So I created another sheet and used it like a contact database with columns TIN, company_name, address1 and address2. If I find an existing TIN I would just copy / paste to the VAT report sheet. Malaking time saving but I want to improve some more.

Is there a way to make the VAT report sheet react so that when I input a TIN number that is already existing sa contact database, it would just automatically add the other fields company_name, address1 and address2? This would save a lot of time switching between sheets and copy / pasting manually.

Any help and suggestion appreciated. TIA.

possible po yan sir. Pa-attached na lang po dito yung working file mo, Like po nung kay mistysparkles, ano ung datasource at ano ung result na gusto mo.

 

[dreadstar]

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

@puuts

Thank you for the quick reply and assistance. Attached is the requested sample file (simplified since I have over a hundred entries).

On the OCT sheet I would like to enter a new entry for the date Oct 12. Its TIN number 008-186-435-000. This is an existing contact hence is already in the CONTACTS sheet. When I input the TIN number I would like it to recognize it is already an existing contact and automatically populate the corresponding Payee, Address1 and Address2 data obtained from the CONTACT sheet. How can I do this?

Currently I manually check the CONTACTS sheet to see if TIN exists already then I copy / paste the corresponding data. Still tedious work hence I came up with the idea above. I hope it can be done easily

 

[kirby21]

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

Thanks to the contributors who never stop helping me on this thread. Two thumbs up to you guys.

 

[themonyo]

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

@puuts

Thank you for the quick reply and assistance. Attached is the requested sample file (simplified since I have over a hundred entries).

On the OCT sheet I would like to enter a new entry for the date Oct 12. Its TIN number 008-186-435-000. This is an existing contact hence is already in the CONTACTS sheet. When I input the TIN number I would like it to recognize it is already an existing contact and automatically populate the corresponding Payee, Address1 and Address2 data obtained from the CONTACT sheet. How can I do this?

Currently I manually check the CONTACTS sheet to see if TIN exists already then I copy / paste the corresponding data. Still tedious work hence I came up with the idea above. I hope it can be done easily

I think vlookup ang solution dyan sa table mo
Try ko bukas sa opis

 

[puuts]

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

@puuts

Thank you for the quick reply and assistance. Attached is the requested sample file (simplified since I have over a hundred entries).

On the OCT sheet I would like to enter a new entry for the date Oct 12. Its TIN number 008-186-435-000. This is an existing contact hence is already in the CONTACTS sheet. When I input the TIN number I would like it to recognize it is already an existing contact and automatically populate the corresponding Payee, Address1 and Address2 data obtained from the CONTACT sheet. How can I do this?

Currently I manually check the CONTACTS sheet to see if TIN exists already then I copy / paste the corresponding data. Still tedious work hence I came up with the idea above. I hope it can be done easily

Eto sir, patry po.

Comment:

Every input mo po ng value sa Column B sa Sheet OCT, magssearch po sya ng tin # dun sa Contacts na sheet. If may nakita sya, mag aauto populate ung Payee,Address 1 at Address 2, else may magpprompt po na No records found.

 

[dreadstar]

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

@themonyo and @puuts

Thank you so much! Both your suggestions worked great!

 

[dimerdime]

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

Hi Po mga boss, Puwede po patulong - Bale nakita ko po itong file na galing ka sir Kirby and gusto ko po sana siyang gawing volume calculator. nagtry po ako mag edit pero hindi talaga kaya. Na attach ko po ung file kasama po ung gusto ko sana na maging outcome nya. Maraming salamat po sa tutulong

 

[themonyo]

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

@puuts

Thank you for the quick reply and assistance. Attached is the requested sample file (simplified since I have over a hundred entries).

On the OCT sheet I would like to enter a new entry for the date Oct 12. Its TIN number 008-186-435-000. This is an existing contact hence is already in the CONTACTS sheet. When I input the TIN number I would like it to recognize it is already an existing contact and automatically populate the corresponding Payee, Address1 and Address2 data obtained from the CONTACT sheet. How can I do this?

Currently I manually check the CONTACTS sheet to see if TIN exists already then I copy / paste the corresponding data. Still tedious work hence I came up with the idea above. I hope it can be done easily

@themonyo and @puuts

Thank you so much! Both your suggestions worked great!

heto ginawa ko.
may drop-down list yung sa TIN, tapos auto-populate yung company at address fields. you can add more TIN source sa reference table mo ng clients/suppliers then automatic na mag-aadd na yun sa drop-down list ng TIN

 

[dimerdime]

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

I mean volume calculator po dun sa post ko, . maraming salamat po

 

[puuts]

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

I mean volume calculator po dun sa post ko, . maraming salamat po

Per item ba save neto or consolidated/total?

 

[dimerdime]

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

Per item ba save neto or consolidated/total?

Total volume po sana ng items ung mais'save sa database sir puuts - tas ung SKU's, if'fill nalang po un sa userform, same as the date box na nagawa ni sir kirby..

maraming salamat po uli sa pagtulong.

 

[dreadstar]

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

Need help again from the awesome guys here. Attached sample file below.

Just want to do some simple automation. In the "Clear Row?" field, if I enter "Y", I want it to clear the data of the next 4 cells in the same row. Can this be done without using macros? What formula command can do this? TIA.

 

[themonyo]

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

Need help again from the awesome guys here. Attached sample file below.

Just want to do some simple automation. In the "Clear Row?" field, if I enter "Y", I want it to clear the data of the next 4 cells in the same row. Can this be done without using macros? What formula command can do this? TIA.

macro nga ata kelangan mo
kasi kung may manual input ka ng data sa row/succeeding cells, hindi pwedeng maglagay ng IF argument/formula unless may reference table ka na pagkukuhanan ng data mapapatungan kasi ang formula pag may new data ka eh

ang other option na alam ko ay conditional formatting, pero it will not clear the cells... just change the color of the text to white para mukhang blank

 

[dreadstar]

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

@themonyo

Thanks! Hopefully hopefully some others may give another way / option.