Hi,
I'm using numbers v3.6.1.
We have a database of all equipment we own with rows stating the make, model number, serial number, description price per day and so on.
What I want to be able to do is create an invoice on a separate sheet and type the model number into the corresponding column on the invoice then have numbers automatically fill the rest of the information in.
Is this possible?
hopefully this makes sense.
MacBook Pro, OS X Yosemite (10.10.5)
This may work for you:
The table on the left is your Database of equipment to rent AND is named "Equipment"
The table on the right is an invoice example that takes information from the database.
For the table on right...
A2=IF(COUNTA(B2:C2)>1, ROW()−1, "")
this is shorthand for... select cell A2, then type (or copy and paste from here) the formula:
=IF(COUNTA(B2:C2)>1, ROW()−1, "")
columns B and C are where you enter information (in this example the columns are shaded white where you enter information)
D2=IF(A2≠"",OFFSET(Equipment::A$1,MATCH(C2,Equipment::B,matching-method)−1, 0,rows,columns), "")
E2=IF(A2≠"",VLOOKUP($D2,Equipment::$A:$F,MATCH(E$1, Equipment::$1:$1,matching-method), 0) , "")
select cell E2, copy
select cells E2 thru H2, paste
I2=IF(A2≠"",H2×B2, "")
select cells A2 thru I2, copy
select cells A2 thru the end of column I, paste
You can add a footer row to sum the total for the invoice and compute tax and other stuff. post back if you need help with that.
Thanks for this Wayne.
I believe I understand the equations however if I get stuck ill be sure to return.
Thanks again.
numbers column recall
