Apple Event: May 7th at 7 am PT
Hi. I have a master sheet of customer's orders. I want to build a separate 'invoice sheet' for each row in that master sheet. Is there anyway to do this (other than manually selecting the required master row as I build each sheet)?
Thanks
MacBook, G5, Mac OS X (10.5.1)
Without specific details (including screenshots) it will be hard to answer. Numbers has functions that can get data using a variety of methods.
Indirect(), offset(), Lookup(), vlookup() and hlookup() are all functions that can help you
Thanks Wayne.
I have just been told that I didn't explain myself properly 😟
I have a master sheet of customer's orders, each row details the customer's name address and order details.
I then have a separate sheet used as an invoice and cells in that sheet link to the first row in the master sheet.
I want to add x number of sheets (i.e. one sheet for each row of the master) so that each invoice has the correct information from each row of the master.
Hi Barcodelabel,
To use the functions that Wayne mentions you need to set up a relationship between the two tables so that the Invoice table can look up information in your Master table.
Here is a simple example where I use an InvoiceID on each row of the Master table and include that InvoiceID in cell A1 of the Invoice table.
This screenshot shows how you can use the INDEX MATCH combination to look up the Customer for InvoiceID 100:
=INDEX(Master::B,MATCH($A$1,Master::$A,0))
The CustDetails in the cell below it are looked up with this formula:
=INDEX(Master::C,MATCH($A$1,Master::$A,0))
It's the same formula except the lookup has been changed from column B to column C.
You can also get a little fancier:
The Description is looked up with this formula:
=INDEX(Master::$A:$F,MATCH($A$1,Master::$A,0),MATCH(B$4,Master::$1:$1,0))
It's a more complicated formula, but it has an advantage. You can just fill right to the Quantity and Price columns and it will adjust automatically to look up the relevant information from the Master table based on the label you put in the row above it. It's doesn't matter, for example, if you decide to switch the order of the Price and Quantity from what it is in the Master table.
When you change the InvoiceID in cell A1, the Invoice table will now pull the relevant information from the Master table.
SG
Thanks guys - all help appreciated!
Nothing to be sad about. Place yourself on the other side of the conversation where you cannot see your screen, you do not have the document in question and you don't really know what the original poster (OP) wants to do.
Once you have looked over SGs response, post back if you have questions or specific questions. post screenshot os what you have and use words if you must. 😉
Can cells in consecutive sheets relate to the next row in a master sheet
