Community archive

Ask something
Question Locked

Excel: Linked Lists and Value Lookup

0 helpful
5 replies

by karim.jina

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello! This is my first post, but I have been actively searching this forums for sometime. Thank you everyone for such great help - now I ask for some. I am going to attempt to explain this as best I can.

Issue:
I have 4 lists of data and they are not all equal. Column A comtains all my juice flavours (blueberry, grape, apple). Column B contains a choice between values in Column A with sugar and without sugar. (So if from the drop down I select Apple and the next is populated with Sugar and Non-Sugar - but not all the flavours have this option, some are fixed with Sugar). Column C contains the names of the beverage in question which is dependant on the flavour and sugar option.

Problem:
I have a linked list between the flavour and sugar, such that when I choose the flavour it will populate the next drop down with the sugar/non-sugar option. The problem is that since there are many names of beverage the I run into issues. If I continue using the linked list it will populate 'blanks' in my sugar/non-sugar list and or the beverage list.

Question:
Is there another way (I am assume using VLOOKUP) to match flavour with sugar/non-sugar option and display those beverage names?? I can not use VBA (need to have the code in the cells for security - no macros).

Thank you very much! If you need the code thus far I can post, but just something generic which I can change will work. Thank you again.

5 replies

by thumbsup2

Yes, you could use vlookup or even hlookup to accomplish this.

The problem with using a linked list or even a lookup (and probably what is happening to you with getting blanks) is that the lookup list MUST be sorted alphabetically by the first lookup field. If it's not, the search or lookup will start at the top and quit looking when it encounters the first one in the list that would be beyond the value being searched for (out of range) and return a false for the search, thus putting NULL (blanks) in your field.

So, if your linked list is sorted like this:

Blueberry
Apple
Cherry
Grape

When you use the dropdown list to select Apple, the search will not find the value Apple in the linked list because it is sitting below Blueberry and won't be able to populate the sugar/non-sugar dropdown.

Likewise, the second dropdown list (sugar/non-sugar) lookup list must also be sorted alphabetically within each flavor so that when you select non-sugar, it will be able to find the associated flavor "name" ("apple with no sugar"). If the linked list shows non-sugar AFTER sugar, the search will quit on the first entry.

Gosh, I hope that makes sense. Post back if you don't understand it.

---

by karim.jina

Hello ThumbsUp2! Thank you for your reply.

Yes I realized afterward I neglected to mention the sorted lists. I'll give you a sample of the lists in sorted fashion.

Column A Column B Column C
========== ======== =========
Apple No Sugar AppleTastic
Apple Sugar AppleBerry
Blueberry No Sugar ApplePunch
Blueberry Sugar BlueberryBee
Grape No Sugar GrapeBlast
Grape Sugar GrapeApe
GreatGrape

**Note: Sorry for the silly names, but I had to ensure the flow for naming purposes.

So you see that Apple and Grape have 3 beverages whilst Blueberry has only one. Here is the issue, the Apple and Grape will show 1 extra blank in sugar/no sugar, and Blueberry will have an extra space in beverages list.

Thank you.

by karim.jina

Oopss...Formatting - forgot about that.

Column A
========
Apple
Apple
Blueberry
Blueberry
Grape
Grape

Column B
========
No Sugar
Sugar
No Sugar
Sugar
No Sugar
Sugar

Column C
========
AppleTastic
AppleBerry
ApplePunch
BlueberryBee
GrapeBlast
GrapeApe
GreatGrape

by thomascraig

Not to hi-jack this thread but I also have been looking about to find an easy tutorial to show me how to link lists together similar to Karim's post.

I think I am a little further behind then everyone else because I cannot begin to link a list from Column A to a list in Column B and so on although I can create them easily. I am basically trying to have functionality that dictates? is you choose 'option A' from the first list, only options D, f andf Z show up in the second list.

I have 31 Primary lists that require unique Secondary lists based on the first selection as well as a tertiary field to as a sort of resolution list. All said and done the user should be able to have three lists to choose from drilling down as they select a Primary, Secondary and then a Resolution.

Can anyone help me out in understanding how this can be done or the right search term to search Google with? So far I have been using the following search terms to no avail.

Linked Lists, Linking Lists, Ordered lists in Excel 2003, etc...

Many thanks in advance.

TC

This discussion is closed to new replies.