Need to Lookup a List for Data Validation

[tampaC66]

Hello,

I am trying to do something, which I think should be pretty simple, but I can't get it to work. I am hoping someone here can help me out.

I have 2 tables which on are two separate worksheets....

Table 1
CostCode ----Item
A -------------Dropdown List<dropdown>
B <dropdown>-------------Dropdown List
C <dropdown>-------------Dropdown List
D <dropdown>-------------Dropdown List


Table 2
Cost Code----Item 1-----Item 2------Item 3-----Item 4.... and so on
A-------------Thing 1-----Thing 2------Thing 3-----Thing 4
B-------------Thing 5-----Thing 6------Thing 7-----Thing 8
C-------------Thing 9-----Thing 10-----Thing 11----Thing 12
D-------------Thing 13----Thing 14-----Thing 15----Thing 16

In the "Item" column of table 1, I want to generate a data validation list by searching for the same "cost code" in Table 2, and then returning a list that includes every item column.

So, the drop down in Table 1 should consist of "Thing 1, Thing 2, Thing 3, and Thing 4"

I have hundreds of different cost codes, so I want the data validation list to look to the cost code column and "lookup" the list on another table. I've tried index/match and vlookup but I cant seem to get it to work.


Any help would be greatly Appreciated!
Thanks!</dropdown></dropdown></dropdown></dropdown>

 

[cdchapman]

Hi tampaC66,

I don't know if this will help, but try the following formula:

=OFFSET(INDIRECT(ADDRESS(MATCH($A2,Sheet2!$A:$A,0),2,3,,"Sheet2")),,,,COUNTA(INDIRECT(ADDRESS(MATCH($A2,Sheet2!$A:$A,0),2,3,,"Sheet2")&":$Z"&MATCH($A2,Sheet2!$A:$A,0))))

This assumes your Table 1 and dropdown validation lists are in Sheet1 (starting in col A) and your Table 2 is in Sheet2 (again starting in col A). The formula should account for any extra Cost Codes and/or Items added to Table 2, as long as there are no blank cells between the data.

Might not be suitable to what you want, but may give you a starting point anyway.


Chris

 

[tampaC66]

Hi tampaC66,

I don't know if this will help, but try the following formula:

=OFFSET(INDIRECT(ADDRESS(MATCH($A2,Sheet2!$A:$A,0),2,3,,"Sheet2")),,,,COUNTA(INDIRECT(ADDRESS(MATCH($A2,Sheet2!$A:$A,0),2,3,,"Sheet2")&":$Z"&MATCH($A2,Sheet2!$A:$A,0))))

This assumes your Table 1 and dropdown validation lists are in Sheet1 (starting in col A) and your Table 2 is in Sheet2 (again starting in col A). The formula should account for any extra Cost Codes and/or Items added to Table 2, as long as there are no blank cells between the data.

Might not be suitable to what you want, but may give you a starting point anyway.


Chris

Thanks Chris!

This is exactly what I needed. I created a test spreadsheet with both tables starting in the A Column and it worked perfectly. I need to now adapt this to my much more complicated spreadsheet.

Is there any way you could explain what each of the major chunks of that formula is doing so I can understand it better for future use?

 

[cdchapman]

Hi tampaC66,

It's getting a bit late here in the UK, but I'll try to explain the formula elements the best I can. Actually, thinking about it, I think we can do away with the ADDRESS formulas to make the whole formula a bit shorter. The revised version is:

=OFFSET(INDIRECT("Sheet2!B"&MATCH($A2,Sheet2!$A:$A,0)),,,,COUNTA(INDIRECT("Sheet2!B"&MATCH($A2,Sheet2!$A:$A,0)&":$Z"&MATCH($A2,Sheet2!$A:$A,0))))

(if this doesn't work, let me know and I'll look into it when I'm a bit fresher, and have more time).

Based on the revised formula above, the breakdown is:

The OFFSET formula returns a reference to a range of cells that is a given number of rows and/or columns from a starting cell reference. The arguments are

Reference - The cell reference from which you want to base the offset. For your data, we know that the column of the cell reference is always going to be col B (ie the first item for each cost code) in Table 2. We need to find the row number to go with this, so we use the MATCH function to find the row number in Sheet2 that matches the cost code we're looking for. We can then add "Sheet2!B" to this to tell Excel to look at Sheet2 column B. So the whole string becomes "Sheet2!B2", for example if we're looking at cost code A. Finally as the cell reference is still a text string at this stage, we need to use the INDIRECT function to convert this to an actual cell reference for the Reference argument.

Rows - the number of rows up or down that we want the Reference argument to refer to. As we don't want to move the starting cell reference, we can leave this argument blank.

Columns - the number of columns left or right that we want the Reference argument to refer to. Again as we don't want to change the starting cell reference, we can also leave this argument blank.

Height - the number of rows we want to go down from the starting cell reference. As your data in Table 2 is arranged horizontally rather than vertically, we can leave this argument blank.

Width - the number of columns we want to go across from the starting cell reference. To cater for any additional Item columns that may be added in the future, we can use the COUNTA formula to count the number of cells that are not blank on the identified row in Table 2 to find how many items exist for the selected Cost Code. Again, a similar technique is used to that in the Reference argument to find the correct row, but as the COUNTA formula requires a range of cells we have to add the last cell reference (in my example I have gone up to column Z, but this can easily be extended to any other column by replacing the Z with the column letter). As previously we need to wrap this in an INDIRECT formula to convert it to an actual cell reference that the COUNTA formula can use.

As you might have noticed the MATCH formulas are looking at the whole of column A to find a match. This is to cater for any additional Cost Codes that may be added to your Table 2 data in the future.

I hope this explains how the various parts of the formula work.

Chris.

 

[tampaC66]

Thanks Chris! Very helpful explanation. I really appreciate you taking the time to write that.

When I try to use the shorter version of your formula it seems to work fine when in a cell, but as soon as I try and put it in as a data validation formula it gives me an error (the formula you typed contains an error). I'm not sure why.


Also, when I adapt this formula (the first one) to my actual spread sheet i'd like to reference table columns (ie. Table2[Item 1]) rather than a specific column ($A:$A) but it doesn't seem to like that. Any idea why?

 

[tampaC66]

="$S"&MATCH([@[Sub Div]],DoNotEdit!$R:$R,0)&":"&(ADDRESS(MATCH([@[Sub Div]],DoNotEdit!$R:$R,0),19+2,3))

This function returns "$S3:$U3" which is the correct range, but when I put this formula into the validation field it gives me the same error message.

I was hoping that if this formula worked, I could just replace the "19+2" with a "19+Countif" function... but no such luck.

I also tried wrapping the whole formula in "Indirect()" but that didn't work either.

 

[cdchapman]

tampaC66,

I'm afraid I don't really know much about table syntax in Excel. I've tried a couple of things using table syntax in the formula, but have only managed to get Excel to display the first item in the dropdown for each Cost Code. Seems like Excel doesn't like table syntax in data validation either, so you have to assign a name to the formula with table syntax (via Name Manager), and then use that name in the data validation. Weird!

Sorry, I can't really help you further with this part, but there may be some other person on this forum who can.

As for the shorter version of the original formula giving an error as a data validation formula, I have just got that too, but it seems that if you press OK and then OK on the data validation box, Excel does actually accept the formula (well it did for me anyway). Perhaps it's because the lookup value at the time the data validation formula is entered is blank, but I don't know.

Regarding your second post - try assigning a name to your formula (via Name Manager), and then using the name as the data validation, as again, I suspect it is due to the table syntax being present in the formula.

If you're happy and able to post a link to a sample spreadsheet with your data, I'm quite happy to see what I can do, although it may be a while before I can look at it, as I'm searching for a new job at the moment.

Hope this is okay,

Chris

 

[tampaC66]

tampaC66,

I'm afraid I don't really know much about table syntax in Excel. I've tried a couple of things using table syntax in the formula, but have only managed to get Excel to display the first item in the dropdown for each Cost Code. Seems like Excel doesn't like table syntax in data validation either, so you have to assign a name to the formula with table syntax (via Name Manager), and then use that name in the data validation. Weird!

Sorry, I can't really help you further with this part, but there may be some other person on this forum who can.

As for the shorter version of the original formula giving an error as a data validation formula, I have just got that too, but it seems that if you press OK and then OK on the data validation box, Excel does actually accept the formula (well it did for me anyway). Perhaps it's because the lookup value at the time the data validation formula is entered is blank, but I don't know.

Regarding your second post - try assigning a name to your formula (via Name Manager), and then using the name as the data validation, as again, I suspect it is due to the table syntax being present in the formula.

If you're happy and able to post a link to a sample spreadsheet with your data, I'm quite happy to see what I can do, although it may be a while before I can look at it, as I'm searching for a new job at the moment.

Hope this is okay,

Chris

Hi Chris.

I finally got it to work using the code below. It's pretty convoluted but it works. I couldn't get the Offset function to work properly so I just scrapped it and tried another method. I appreciate all of your help, I would never have figured it out without otherwise.

=INDIRECT(ADDRESS(MATCH(INDIRECT("RC[-1]",0),DoNotEdit!$T:$T,0),COLUMN(ItemValidation[1]),3,,"DoNotEdit")&":"&ADDRESS(MATCH(INDIRECT("RC[-1]",0),DoNotEdit!$T:$T,0),COLUMN(ItemValidation[1])+COUNTA(INDIRECT(ADDRESS(MATCH(INDIRECT("RC[-1]",0),DoNotEdit!$T:$T,0),COLUMN(ItemValidation[1]),3,,"DoNotEdit")&":"&ADDRESS(MATCH(INDIRECT("RC[-1]",0),DoNotEdit!$T:$T,0),COLUMN(ItemValidation[20]),3,,)))-1))