Forum Discussion
How to return several values in an specific column?
I am trying to find a formula that allows me to retrieve several specific cells located in a same column so those values can be part of a drop-down selection.
In the example below, G4 and H4 have "the formula" that looks at the value on the cell above (in case G4 is 1, in the case of H4 is 3) and then, if that value matches one of the headers of the table, will find the values below that matched number (values below the matched header) and uses those values in a data validation way to create a drop down (obviously, I manually placed the values below "3" on the table and created a drop-down with data validation function on cell H4)
3 Replies
The attached example uses the INDIRECT function in the data validation and named ranges for the 1, 2 and 3. As it isn't possible to name the ranges 1, 2 and 3 i have chosen the names column1, column2 and column3.
- Thank you OliverScheurich
However, I do not quite understand how the formula that you place on the data validation identifies only specific cells from the selected column. I can see how (let's say, on cell I4) the function INDIRECT locates the column3 but where in the formula says to only display the cells with names (E4 to E7) and not the cell with the number (E3)?
Thank you again.
PS: I understand if this takes too much of your time.You can open the Name Manager with ctrl+F3 or if you select Formulas -> Name Manager. In the Name Manager you can see the values and the ranges to which the name refers to.
