Hi, I'm struggling with this and hope somebody might be able to help.
I have two adjacent columns in a data table that both need to use data validation, from 2 lookup tables. The data validation in both cases provides a drop down list for the user to choose their selection from. The available choices in the second column need to be filtered according to the selection in the first column.
I've read some old posts describing how to use a combination of OFFSET and MATCH functions within the source field of the data validation popup window to achieve this.
I've done that and it works fine for an individual cell. However, I've not found a way to have the cell references in the OFFSET and MATCH functions automatically increment as you move down the second column, so the only way I can implement it at the moment is to manually edit the data validation source string for every cell in the second column. This would be both very tedious and time-consuming, as I'm talking about several thousand rows in year one, with around the same number added each year.
Is there any way to get the data validation source string to increment automatically?
Example:
Data Table
spreadsheet
Column I............Column J
A
B
C
A
C
D
D
A
Lookup Table 1
spreadsheet
Column L
A
B
C
D
Lookup Table 2 (sorted on Column N)
spreadsheet
Column N..........Column O
A......................text1
A......................text2
A......................text3
B......................text1
C......................text1
C......................text2
D......................text1
D......................text2
Column N (lookup table 2) is itself a drop down list using data validation with Column L (lookup table 1) as its source.
So, in data table column I the user chooses from a drop down list generated by Column L. I want a drop down list in Column J that takes the value entered in Column I and uses it to add into the drop down list only those entries in Column O where the entry in Column N of the same row matches.
Row 2 is the first row of data, as row 1 is header information.
I put this string in the source field of the data validation popup with cell J2 selected...
=OFFSET(O1,MATCH($I$2,$N$2:$N$9,0),0,COUNTIF($N$2:$N$9,$I$2),1)
This correctly gives me the three possible valid choices for Column J based on A having been chosen in Column I (text1, text2 and text3).
I need one piece of data in the string ($I$2 - used in two places within the string), the string being in the source field of the data validation popup, to change as I move from row to row.
e.g.
If I am entering data in J3, then $I$2 should be changed to $I$3.
If I am entering data in J50, then $I$2 should be changed to $I$50.
etc.
If I select the entire column J, or even just select some cells in column J, then then the above string in the data validation popup source field, all column J cells acquire the same validation string, so every cell of Column J would provide a drop down list based on Column I, Row 2, not based on column I in the same row as the selected column J cell.
Am I approaching this functionality incorrectly? Is this how I should be doing it?
Any ideas, help, thoughts etc would be gratefully received, as it is currently a bit of a showstopper for me.
TIA,
Nigel
I have two adjacent columns in a data table that both need to use data validation, from 2 lookup tables. The data validation in both cases provides a drop down list for the user to choose their selection from. The available choices in the second column need to be filtered according to the selection in the first column.
I've read some old posts describing how to use a combination of OFFSET and MATCH functions within the source field of the data validation popup window to achieve this.
I've done that and it works fine for an individual cell. However, I've not found a way to have the cell references in the OFFSET and MATCH functions automatically increment as you move down the second column, so the only way I can implement it at the moment is to manually edit the data validation source string for every cell in the second column. This would be both very tedious and time-consuming, as I'm talking about several thousand rows in year one, with around the same number added each year.
Is there any way to get the data validation source string to increment automatically?
Example:
Data Table
spreadsheet
Column I............Column J
A
B
C
A
C
D
D
A
Lookup Table 1
spreadsheet
Column L
A
B
C
D
Lookup Table 2 (sorted on Column N)
spreadsheet
Column N..........Column O
A......................text1
A......................text2
A......................text3
B......................text1
C......................text1
C......................text2
D......................text1
D......................text2
Column N (lookup table 2) is itself a drop down list using data validation with Column L (lookup table 1) as its source.
So, in data table column I the user chooses from a drop down list generated by Column L. I want a drop down list in Column J that takes the value entered in Column I and uses it to add into the drop down list only those entries in Column O where the entry in Column N of the same row matches.
Row 2 is the first row of data, as row 1 is header information.
I put this string in the source field of the data validation popup with cell J2 selected...
=OFFSET(O1,MATCH($I$2,$N$2:$N$9,0),0,COUNTIF($N$2:$N$9,$I$2),1)
This correctly gives me the three possible valid choices for Column J based on A having been chosen in Column I (text1, text2 and text3).
I need one piece of data in the string ($I$2 - used in two places within the string), the string being in the source field of the data validation popup, to change as I move from row to row.
e.g.
If I am entering data in J3, then $I$2 should be changed to $I$3.
If I am entering data in J50, then $I$2 should be changed to $I$50.
etc.
If I select the entire column J, or even just select some cells in column J, then then the above string in the data validation popup source field, all column J cells acquire the same validation string, so every cell of Column J would provide a drop down list based on Column I, Row 2, not based on column I in the same row as the selected column J cell.
Am I approaching this functionality incorrectly? Is this how I should be doing it?
Any ideas, help, thoughts etc would be gratefully received, as it is currently a bit of a showstopper for me.
TIA,
Nigel
