How to create a dynamic INDIRECT formula when dragging down

[zazu8]

Hi, I have a very simple question about INDIRECT. I have a drop down list in column B and a drop down list in column C depending on the value given in column B. I can apply the formula =INDIRECT(B6) for value in C6, =INDIRECT(B7) for value in C7, etc. and it works when done manually. If I click all the column C and I insert the formula, all the values in column C will be stuck to the value in column B predetermined. I can do manually each row, but it is a database that others will need to use, so I want to fix the drop down options for whoever is filling. Any ideas? Thank you

 

[6StringJazzer]

I am not clear on what you are describing. Is your INDIRECT formula in the cell, or is it a custom formula for the data validation list? You say that columns B and C both have data validation lists, but then you say you click column C and "insert the formula" and refer to "values in column C". Setting up a data validation list formula does not predetermine any values, so I am just not getting what you are asking.

If you select the cells in column C starting with C6 where you want the dropdown, and set up data validation using =INDIRECT(B6) with no "$" characters, then Excel will make this a relative reference and the data validation formula in C10 will refer to B10. If that is not what is happening, please give more details.

 

[wag97]

Hi, I have a very simple question about INDIRECT. I have a drop down list in column B and a drop down list in column C depending on the value given in column B. I can apply the formula =INDIRECT(B6) for value in C6, =INDIRECT(B7) for value in C7, etc. and it works when done manually. If I click all the column C and I insert the formula, all the values in column C will be stuck to the value in column B predetermined. I can do manually each row, but it is a database that others will need to use, so I want to fix the drop down options for whoever is filling. Any ideas? Thank you

Hi Zazu,

I think I understand what you're describing... as far as I understand, when you drag down column C all of the values match cell B7 regardless of the row number in column C (e.g. C8,C9.C10=B7). If this is the issue you're having, try using an XLOOKUP either on its own or with the INDIRECT formula. Let me know if this helps. Sorry if I misunderstood the question.