Help with lookup function (or something along those lines)

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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

SHEETMAGIC

Board Regular
Joined
May 3, 2005
Messages
209
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 :rolleyes:
 

SHEETMAGIC

Board Regular
Joined
May 3, 2005
Messages
209
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

ORDERS (from Alan's exhibit)
Book10
ABCDE
1ORDERIDADDRESS 1ADDRESS 2VALUESHIPPING
21ADDRESS 1 id 1ADDRESS 2 id 1VALUE id 1SHIPPING id 1
32ADDRESS 1 id 2ADDRESS 2 id 2VALUE id 2SHIPPING id 2
43ADDRESS 1 id 3ADDRESS 2 id 3VALUE id 3SHIPPING id 3
54ADDRESS 1 id 4ADDRESS 2 id 4VALUE id 4SHIPPING id 4
65ADDRESS 1 id 5ADDRESS 2 id 5VALUE id 5SHIPPING id 5
76ADDRESS 1 id 6ADDRESS 2 id 6VALUE id 6SHIPPING id 6
87ADDRESS 1 id 7ADDRESS 2 id 7VALUE id 7SHIPPING id 7
98ADDRESS 1 id 8ADDRESS 2 id 8VALUE id 8SHIPPING id 8
109ADDRESS 1 id 9ADDRESS 2 id 9VALUE id 9SHIPPING id 9
ORDERS


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

Board Regular
Joined
May 3, 2005
Messages
209
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Re: Using the list

SHEETMAGIC said:
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

Board Regular
Joined
May 3, 2005
Messages
209
Thanks Aladin. I have since sorted this last question out by doing it another way.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,731
Messages
5,573,889
Members
412,555
Latest member
mark84
Top