Dynamic Array for multiple data validation - Variable Data Entry Cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1597096%22%20slang%3D%22en-US%22%3EDynamic%20Array%20for%20multiple%20data%20validation%20-%20Variable%20Data%20Entry%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1597096%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20that%20works%20great%20provided%20that%20the%20data%20triggering%20the%20array%20is%20always%20input%20from%20the%20same%20cell%20(G3%20in%20the%20formula%20below)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSORT(FILTER(%24D%242%3A%24E%2435487%2C%24D%242%3A%24D%2435487%3DG3)%2C2)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EG3%20has%20a%20drop%20down%20field%20from%20which%20a%20selection%20is%20made.%20The%20selection%20then%20triggers%20the%20dynamic%20array%20which%20allows%20for%20a%20conditional%20drop%20down%20to%20appear%20in%20another%20cell%20from%20which%20I%20can%20then%20select%20and%20populate.%20I%20am%20using%20%3DINDEX(%24J%243%23%2C%2C2)%20-%20to%20get%20the%20conditional%20dynamic%20dropdown.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20this%20same%20functionality%20to%20be%20available%20as%20an%20option%20in%20cell%20G4%2C%20G5%20etc%20(which%20has%20the%20same%20drop%20down)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20theory%20I%20can%20create%20multiple%20arrays%20and%20change%20the%20'G'%20value%20each%20time%20but%20i%20will%20have%20thousands%20to%20create%20and%20so%20was%20looking%20for%20a%20dynamic%20solution.%20How%20do%20I%20make%20the%20array%20variable%20point%20to%20whichever%20the%20active%20G%20cell%20is%20at%20the%20time%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%20I%20am%20not%20skilled%20enough%20to%20do%20this%20in%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPip%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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)

 

=SORT(FILTER($D$2:$E$35487,$D$2:$D$35487=G3),2) 

 

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.

 

Pip

 

 

4 Replies
Highlighted

@Piplinson 

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

image.png

 

Highlighted
Thanks. I got it to work. Phil
Highlighted
Highlighted

@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.