Creating Invoices from Monthly Order Sheets

[bernardchan79]

Dear Experts,

Please kindly see attached excel file.

I have day 1,2,3 to 29,30,31 worksheets in this excel.

I need the daily details like, "Name, Location, Dinner, Lunch" to be sent to worksheet "Monthly Orders" for easy compilation.

After manually adding the prices of each orders in "Monthly Orders", I need to create invoices at end of the month for each orders.

So if I input a "P" at "Monthly Orders", It will pull the date 1 - 31 to Invoice so that we can print the invoice for the customers.

And I need to print label stickers from the "monthly orders" daily, eg date 1 = 8pax from Khairul, 38pax from Zhong Jian, 7pax from TKH.


Hope you guys understands what I'm talking about and hope that you can assist me in this arduous matter.

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect (Win 10 is NOT your Office version). If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[bernardchan79]

Thank you for amending the thread title!

Will follow accordingly in the future!

Thank you!

[AliGW]

But you haven't amended your profile - please read the bottom of my previous message and do as asked. Thanks.

[bernardchan79]

Ok, Thanks for the reminder, It is done now.

[JohnTopley]

Questions:

1. What is the purpose of testing the "daily" value e,g =IF(F$2='01'!$D$1,'01'!$F3,0)

2, Based on the above formula , "Monthly Orders" are for "Dinner" only [column F in daily records]: is this always the situation ?

3. You solution relies on ALL tabs and "Monthly Orders" having "Name" column in exactly the same order: while this not bad practice, it is probably better to match entries against the "Name" as this less error-prone. "Name" could be a drop-down list to avoid typing errors

4. Why entries for "Dinner"/"Lunch" which are identical in your sample?

[JohnTopley]

I have taken the liberty of changing formulae to make the more "generic" i,e easy to grag/copi

in "Monthly Orders"

in F3

=IFERROR(INDIRECT("'" &TEXT(F$2,"00") &"'!$f"&ROWS($1:3)),0)

copy across and down

NOTE:The INDIRECT function is a volatile function. This means that every time anything in your workbook changes or any time you press F9 to calculate, the INDIRECT function will be called. but with small volumes of data the impact is negigible.

in "Invoice" B15

=VLOOKUP("p",'Monthly Orders'!A:AJ,ROWS($1:6),FALSE)

Copy down

I haven't addressed the issue(or non-issue ?) of "Invoice" currently only referring to Dinner/Lunch.