SHEETMAGIC
Board Regular
- Joined
- May 3, 2005
- Messages
- 209
Boy... I know this is real simple and I feel a bit stupid not being able to figure it out -- as I've managed to figure out much more complicated stuff. Perhaps it's just that I have limited time to suss it out.
Here's the scenario...
Sheets: ORDERS and INVOICE
On ORDERS I have order data laid out one order per row.
The first column (which is a range named ORDERID) has a unique order ID (1,2,3 etc). This range obviously grows as more orders are added [[if someone would re-explain to me how to define a dynamic range that woudl be great -- I've been told one before yet the impact of "if you don't use it you lose it" has since erased that from my memory]]
The subsequent columns have various data like ADDRESS1, ADDRESS2, VALUE, SHIPPING, etc
Now...
On INVOICE I have an invoice template laid out.
I have a cell (J1) where I have a validation list that gets its data from the range ORDERID. I then select the orderid in the resulting drop down.
What I want is for the rest of the invoice to then fill itself out from that data on ORDERS.
I know that in each relevent cell in INVOICE I need to have a formula that does some sort of lookup using the value in J1 (INVOICE sheet), and then takes the appropriate value from the appropriate cell in ORDERS.
Yet I can't figure out how to go about it. I know it's easy to do though... but the help info was not much good and doing a search for things like "MATCH", "LOOKUP" etc here in the forum gave me many hundreds of results.
Any help would be greatly appreciated.
With thanks...
Jonathan
Here's the scenario...
Sheets: ORDERS and INVOICE
On ORDERS I have order data laid out one order per row.
The first column (which is a range named ORDERID) has a unique order ID (1,2,3 etc). This range obviously grows as more orders are added [[if someone would re-explain to me how to define a dynamic range that woudl be great -- I've been told one before yet the impact of "if you don't use it you lose it" has since erased that from my memory]]
The subsequent columns have various data like ADDRESS1, ADDRESS2, VALUE, SHIPPING, etc
Now...
On INVOICE I have an invoice template laid out.
I have a cell (J1) where I have a validation list that gets its data from the range ORDERID. I then select the orderid in the resulting drop down.
What I want is for the rest of the invoice to then fill itself out from that data on ORDERS.
I know that in each relevent cell in INVOICE I need to have a formula that does some sort of lookup using the value in J1 (INVOICE sheet), and then takes the appropriate value from the appropriate cell in ORDERS.
Yet I can't figure out how to go about it. I know it's easy to do though... but the help info was not much good and doing a search for things like "MATCH", "LOOKUP" etc here in the forum gave me many hundreds of results.
Any help would be greatly appreciated.
With thanks...
Jonathan