Vlookup within Vlookups

[StuartEade]

Good morning

I have attached my spreadsheet as it is so far.

I'm trying to have a dropdown list of consignee's that are associated to the shipper.

The shipper short code selects the shippers address.
Then you select the contact from selected shipper. (Thanks to Lee yesterday for that)

Now based on the shipper that is selected, each shipper may have upto 50 different consignee's that i want listed.

If you look at second sheet and scroll along the top until you reach customer A, customer B, this is where i'm going to list all the particulars that are relevant to each consignee (as per list 1 column left of customer a).

On some customers, every single row could be filled in and this information needs to be transferred into the first sheet.

All i need to know (phew) is how to link the customer's consignee to the customer.

Hope you follow my description of the challenge.

Thanks for your time
StuartEade

[rwgrietveld]

Make a name ´consignee´ which refrers to

Please Login or Register  to view this content.

Now with date validation in V14 =consignee

[rwgrietveld]

I do hope that your layout will dramatically change. Especially with Lookup functions MERGING can get you in to trouble. Therefore I would suggest a layout without the merged cells.

[StuartEade]

Thanks Ricardo,

Please explain what you mean by "Make a name ´consignee´ which refrers to".

Best Regards
StuartEade

[StuartEade]

Thanks for the tip about merging. I have changed it so it has no merged cells.
Learn something new everyday!!

Appreciate it
StuartEade

[rwgrietveld]

Is your thread solved?

I asumed you'd understand NAMES as our WB is full of them.

Excel 2003: Insert Name
Exce 2007: Name manager on the Formulas tab (ribbon)

[StuartEade]

No thread is not solved.

I followed your example but it didn't work.
It displays a dropdown box with blank entries.

[StuartEade]

Ricardo I see what you have done.

*correction*

What you have done DOES work.

I don't think I explained myself very well.

Going back to my example, on customer_addresses sheet, if you scroll across to columns AL and AM, these columns are consignee's for customer A (there could be upto 50 columns per customer, then column AO is for customer B etc etc


Thanks
StuartEade

[rwgrietveld]

StuartEade,

This can be done, but first do this for us.

Clean up your WB. It looks terrible and this makes it hard to give you a suitable solution.

[StuartEade]

Please find attached cleaned up worksheet to look at.

[rwgrietveld]

Solved.

I reworked your WB even more to make it less complicated.

Use two NAMED ranges
Contacts

Please Login or Register  to view this content.

and Consignees

Please Login or Register  to view this content.

[rwgrietveld]

Small error in Contacts

Please Login or Register  to view this content.

Please use new attachement

[StuartEade]

Thankyou thankyou thankyou Ricardo!

Works great.
One thing though.

On the consignee sheet you have done, client 1 may have 20 consignee's, client 2 may have 20, client 3 may have 50.
This causes a problem.
Any solution?


Thankyou
StuartEade