Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: Millypede
Millypede Author
User level: Level 1
0 points

A little help with invoice calculator

Hi All,

I am in the middle of creating a job sheet for myself,


(Main Sheet)

QtyProduct CodeDescriptionPrice

Total

What I would like to do , and I think its either VLOOKUP or maybe a pivot table, is that I have another sheet with all the products/services on and the infomation is pulled across.


So I also have a list of all the products, and its costs,

(Product Sheet)

Product CodeDescriptionPrice
LabourLabour Costs for on site installation

60

BatteryBattery for alarm box30
Junction BoxJunction box to extend wiring54

What I hope would happen is that I choose the product code from a drop down box, this fill in the Description and the price for me.



Does that make sense? Thanks in advance 🙂

Posted on Oct 21, 2013 8:20 PM

Reply
Question marked as Best reply
User profile for user: Barry
Barry
User level: Level 9
59,861 points

Posted on Oct 21, 2013 11:30 PM

Hi Milly,


Yes it does.


Your product sheet is a lookup table, and VLOOKUP is the function you will need.

User uploaded file

Product List contans only entered values (no formulas).


Main contains entered values in columns A and B, the first formulas below, entered and filled as indicated:


C2, filled down and right to D9 (last white filled row):


=IFERROR(VLOOKUP($B,Product List :: $A:$C,COLUMN()-1,FALSE),"")


"FALSE" will appear as "Exact match" in th formula. This will cause an error in the rows of Main where column B is blank. The error is caught by IFERROR, which inserts a null string in the cell containing the formula, making the cell appear blank.


E2, filled down to E9:


=IF(LEN(D)>0,A*D,"")


LEN(D) tests for content in 'this row' of column D. If found, the formula multiplies the quantity in A by the price in D. if D is 'empty', the formula places a null string in its cell.


Rows 10 and 11 are footer rows.


E10 calculates a tax amount using a rate of 5%.


=SUM(E)*5%


E11 calculates the total, including tax:


=SUM(E)+E10


SUM(E) includes only the 'normal' (or Body) rows. As E10 is in a Footer row, the cell is not included in the SUM, and must be added separately.


This should give you enough to get started. Further questions are welcomed.


Regards,

Barry

View in context
2 replies
Question marked as Best reply
User profile for user: Barry
Barry
User level: Level 9
59,861 points

Oct 21, 2013 11:30 PM in response to Millypede

Hi Milly,


Yes it does.


Your product sheet is a lookup table, and VLOOKUP is the function you will need.

User uploaded file

Product List contans only entered values (no formulas).


Main contains entered values in columns A and B, the first formulas below, entered and filled as indicated:


C2, filled down and right to D9 (last white filled row):


=IFERROR(VLOOKUP($B,Product List :: $A:$C,COLUMN()-1,FALSE),"")


"FALSE" will appear as "Exact match" in th formula. This will cause an error in the rows of Main where column B is blank. The error is caught by IFERROR, which inserts a null string in the cell containing the formula, making the cell appear blank.


E2, filled down to E9:


=IF(LEN(D)>0,A*D,"")


LEN(D) tests for content in 'this row' of column D. If found, the formula multiplies the quantity in A by the price in D. if D is 'empty', the formula places a null string in its cell.


Rows 10 and 11 are footer rows.


E10 calculates a tax amount using a rate of 5%.


=SUM(E)*5%


E11 calculates the total, including tax:


=SUM(E)+E10


SUM(E) includes only the 'normal' (or Body) rows. As E10 is in a Footer row, the cell is not included in the SUM, and must be added separately.


This should give you enough to get started. Further questions are welcomed.


Regards,

Barry

Reply

A little help with invoice calculator

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.
Learn more Sign up