Help with lookup function (or something along those lines)

[SHEETMAGIC]

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

 

[SHEETMAGIC]

MORE INFO??

Do let me know if more information is needed in order to answer this question (me is sitting here wondering why no response to something I am sure is real simple... getting worried... have I missed something ??)

Thanks...

Jonathan

 

[al_b_cnu]

hI,

hOW ABOUT SOMETHING LIKE:

Book1
	A	B	C	D	E
1	ORDERID	ADDRESS 1	ADDRESS 2	VALUE	SHIPPING
2	1	ADDRESS 1 id 1	ADDRESS 2 id 1	VALUE id 1	SHIPPING id 1
3	2	ADDRESS 1 id 2	ADDRESS 2 id 2	VALUE id 2	SHIPPING id 2
4	3	ADDRESS 1 id 3	ADDRESS 2 id 3	VALUE id 3	SHIPPING id 3
5	4	ADDRESS 1 id 4	ADDRESS 2 id 4	VALUE id 4	SHIPPING id 4
6	5	ADDRESS 1 id 5	ADDRESS 2 id 5	VALUE id 5	SHIPPING id 5
7	6	ADDRESS 1 id 6	ADDRESS 2 id 6	VALUE id 6	SHIPPING id 6
8	7	ADDRESS 1 id 7	ADDRESS 2 id 7	VALUE id 7	SHIPPING id 7
9	8	ADDRESS 1 id 8	ADDRESS 2 id 8	VALUE id 8	SHIPPING id 8
10	9	ADDRESS 1 id 9	ADDRESS 2 id 9	VALUE id 9	SHIPPING id 9
ORDERS

Book1
	A	B	C	D	E	F	G	H	I	J
1	ORDERID	ADDRESS 1	ADDRESS 2	VALUE	SHIPPING					8
2	8	ADDRESS 1 id 8	ADDRESS 2 id 8	VALUE id 8	SHIPPING id 8
Invoice

FORMULA IN b2 & COPIED ACROSS THE ROW IS =OFFSET(ORDERS!$A$1,MATCH($J$1,ORDERS!$A:$A,0)-1,MATCH(B$1,ORDERS!1:1,0)-1)

 

[SHEETMAGIC]

Should work

Hi Alan,

I extend my thanks to you for that.
I shall try it out shortly...
Looks like it will do the trick.

The only slight change I shall have to make is to do with the fact that my INVOICE doesn't actually have the field names anywhere in it, so I can't use them as part of the matching. But I will simply substitute the actual field name into your reference to it in your formula. I figure that'll do the trick.

With my regards,


Jonathan

PS. I can see that the result is not as simple has I had imagined. Not feeling so coy about it now... and glad to have learnt something new... in regards to this OFFSET business...

Cheers.

 

[Aladin Akyurek]

If the data on ORDERS is sorted on ORDERID, the retrieval can be done in a faster way.

ORDERS (from Alan's exhibit)

Book10
	A	B	C	D	E
1	ORDERID	ADDRESS 1	ADDRESS 2	VALUE	SHIPPING
2	1	ADDRESS 1 id 1	ADDRESS 2 id 1	VALUE id 1	SHIPPING id 1
3	2	ADDRESS 1 id 2	ADDRESS 2 id 2	VALUE id 2	SHIPPING id 2
4	3	ADDRESS 1 id 3	ADDRESS 2 id 3	VALUE id 3	SHIPPING id 3
5	4	ADDRESS 1 id 4	ADDRESS 2 id 4	VALUE id 4	SHIPPING id 4
6	5	ADDRESS 1 id 5	ADDRESS 2 id 5	VALUE id 5	SHIPPING id 5
7	6	ADDRESS 1 id 6	ADDRESS 2 id 6	VALUE id 6	SHIPPING id 6
8	7	ADDRESS 1 id 7	ADDRESS 2 id 7	VALUE id 7	SHIPPING id 7
9	8	ADDRESS 1 id 8	ADDRESS 2 id 8	VALUE id 8	SHIPPING id 8
10	9	ADDRESS 1 id 9	ADDRESS 2 id 9	VALUE id 9	SHIPPING id 9
ORDERS

Invoice

Book10
	A	B	C	D	E	F
1	Pos	ORDERID	ADDRESS 1	ADDRESS 2	VALUE	SHIPPING
2	8	8	ADDRESS 1 id 8	ADDRESS 1 id 8	ADDRESS 1 id 8	ADDRESS 1 id 8
3	6	6	ADDRESS 1 id 6	ADDRESS 1 id 6	ADDRESS 1 id 6	ADDRESS 1 id 6
4	0	99
5
6
Invoice

A2, copied down:

=IF(LOOKUP(B2,ORDERS!$A$2:$A$10)=B2,MATCH(B2,ORDERS!$A$2:$A$10,1),0)

C2, copied across then down:

=IF(N($A2),INDEX(ORDERS!$B$2:$B$10,$A2),"")

If you are on Excel 2003, convert the data area on ORDERS into a list by means of Data|List|Create List. This will automatically adjust range references in the formulas on Invoice whenever the source data expands or crimps (so there will be no need for a dynamic named range).

 

[SHEETMAGIC]

Using the list

Hi Aladin,
Thanks for helping as you have.

If you are on Excel 2003, convert the data area on ORDERS into a list by means of Data|List|Create List. This will automatically adjust range references in the formulas on Invoice whenever the source data expands or crimps (so there will be no need for a dynamic named range).

Tell me,,, once I have made a list in this way, how to I then reference it in the validation on the other sheet?

Cheers,

Jonathan

 

[Aladin Akyurek]

Re: Using the list

Hi Aladin,
Thanks for helping as you have.

If you are on Excel 2003, convert the data area on ORDERS into a list by means of Data|List|Create List. This will automatically adjust range references in the formulas on Invoice whenever the source data expands or crimps (so there will be no need for a dynamic named range).

Tell me,,, once I have made a list in this way, how to I then reference it in the validation on the other sheet?

Cheers,

Jonathan

Which range from the List do you want as Source in data validation? And does the range consist of distinct items?

 

[SHEETMAGIC]

Thanks Aladin. I have since sorted this last question out by doing it another way.

 

[Aladin Akyurek]

Thanks Aladin. I have since sorted this last question out by doing it another way.

Hope you did not introduce any dynamic named range.