Apr 19 2022 10:42 AM
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)
Apr 19 2022 10:58 AM
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.
Apr 20 2022 03:02 PM
Apr 20 2022 03:35 PM
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.