[SOLVED] New Invoice Functions

**Codge** · 10-09-2009, 09:23 AM

Hi
I am currently trying to construct an Invoicing system for my sons small business.
I am having to use 3 Invoices for this because of the different formulas in each one.
The first Invoice uses the IF function with a code to find the product and its price.
The second Invoice uses the Index & Match function to find the price of a door given the height and the width.
The third Invoice uses another IF function to find the price and the area of a panel given the length x breadth.

If I print all 3 Invoice some could contain up to 40 rows and another could contain only 1 or 2 rows.

One way I have solved this is to use a 4th Invoice and copy and paste from each Invoice into Invoice 4 for the small ones and use a 5th Invoice (continuing over 2 pages) to paste the larger ones.

The problem is that the functions are different in each Invoice and therefore I cannot combine them into one.

Is there a better way to do this.

A Simple answer for a Simple Mind.

Thanks Codge.

**shg** · 10-09-2009, 10:07 AM

What a nice Dad!

Post an example that shows what you're doing and what the issues are. Someone will help sort it out.

**teylyn** · 10-09-2009, 10:01 PM

Hi Codge,

have a look at the attached.

I have four tabs: Invoice, Products, Doors and Panels.

On the Invoice tab, Column B asks for a product code with data validation based on the first column of the Products sheet.

Column D offers a drop down box with a list that will vary. If Doors are the product, then it will offer the height for doors from the Doors tab. If Panels are the chosen product code, then it will offer the length for panels from the Panels tab.

Column E does similar things for width/breadth.

This is done by using range names for the measurement columns and rows on the doors and panels tabs. Have a look at the defined range names. The data validation list in column D on the invoice tab is created thus:

=INDIRECT($B6&"_height")

which means: take the text in cell B6 and add "_height", which will result in either "door_height" or "panel_height", depending on what is selected in cell B6. INDIRECT makes a reference from that text, so now the data validation looks at the range called "door_height", which is defined on the Doors tab.

The same INDIRECT formula helps find the price of items in column G. Using INDEX/MATCH, I take the word from B6 into an INDIRECT formula and add the "_height" and "_width" part as needed to construct the ranges for INDEX and MATCH

If no product code has been entered, no price will be returned. If the product code is anything other than "door" or "panel" I perform a simple VLOOKUP for the product code on the Product tab.

From there on it's simple arithmetic.

hope that helps

cheers

**Codge** · 10-12-2009, 08:26 AM

Hi shg
Hi teylyn

Thank you both for your replies.

Thanks teylyn for the work you must have put into this reply which was excellent, but as you will see from the attachment there are still a few areas to address.

The Purchases were fine but the Doors & Panels can not be selected from a drop down list because the doors have to be infinitely variable in height & width.
Because we have upto 50 different door styles the system I have used is to use a Base Price List and by changing the Group Number (1,2,3etc.) it also changes the Percentage increase (or decrease) of the price.(see Door Invoice)
The Panels also cannot be selected from a list because they need to be calculated by area as the pricing is per square metre.

I hope I am not giving you too much of an headache with this but I would value your comments.

Thanks again

Codge

**teylyn** · 10-12-2009, 05:22 PM

Hi Codge,

your workbook has links to other data sources, so there's not much I can do with the whole setup.

The range names CODE, FACADES, Percentage and PRODUCT all point to an external workbook called DOORMAKER INVOICE DOORS 1.xlt. Without having this, I can't verify the formulas.

Overall, I find the layout very confusing and don't understand where things are.

Can you please come up with a version that has sample data for all information kinds and then specify where you want which output, based on what logic?

That would help immensely. I'm sure it will not be too hard to work out, but currently the setup is just too chaotic.

cheers

**Codge** · 10-14-2009, 05:23 AM

Hi Teylyn

I am sorry for the confusion as you can see I am not very good with Excel.

However I have sent you a new version which I hope will make things a little clearer.

The main object is to combine the Product, Doors and Decors on to one Invoice.

The only way I could do this useing my version is to copy each section onto one Invoice.

To cover the amount of different Door styles I have placed a group number on the sheet so that by changing this it will change the prices by a given percent.

Thanks Again

Codge