Dynamic Array for multiple data validation - Variable Data Entry Cell

Copper Contributor

Hi all


I have a formula that works great provided that the data triggering the array is always input from the same cell (G3 in the formula below)




G3 has a drop down field from which a selection is made. The selection then triggers the dynamic array which allows for a conditional drop down to appear in another cell from which I can then select and populate. I am using =INDEX($J$3#,,2) - to get the conditional dynamic dropdown. 


I want this same functionality to be available as an option in cell G4, G5 etc (which has the same drop down) 


In theory I can create multiple arrays and change the 'G' value each time but i will have thousands to create and so was looking for a dynamic solution. How do I make the array variable point to whichever the active G cell is at the time?


Any ideas? I am not skilled enough to do this in VBA.





4 Replies


Not sure I understood correctly, perhaps you may using something like this for data validation list (=$K3#)



Thanks. I got it to work. Phil

@Piplinson I have a situation similar to the one that you describe and was wondering how you solved.  I struggle with getting the cell that provides the lookup value to be dynamic.  Your column G.  


thanks in advance for any help.