Prev: If argument
Next: Protecting Sheets with drop down boxes
From: Duke Carey on 15 Apr 2010 07:00
OK, now I see what you want to do. You want to DYNAMICALLY filter var2 based
on var1's value.

There's no easy way to do this in Excel, and there may be no way without
resorting to non-trivial VBA code.

This might be something that you could adapt. It talks about using
parameterized queries in Excel to pull data back from Access. You can also
use Excel as the 'database.' I'd see it as you using the distinct var1
values in the first list. The selected value becomes the parameter to the
query that finds the distinct var2 values associated with the selected var1,
and finally, use selected var1 and var2 values to find the distinct var3
values. Finally, all three become paramters in the final query that returns
data

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm


"Mikej" wrote:

> I actually managed to find a function to pull the data depending on the
> variables selected:
>
> DGET(master!$A$1:$F$8,B12,$A$5:$C$6))
>
> I'm really stuck with the selection drop down menus. I've managed to kind of
> get something to work with OFFSET for var2 (except that the options displayed
> in the drop down menu may not be unique)
>
> =OFFSET(data_start,MATCH(A6,var1_column,0)-1,1,COUNTIF(var1_column,A6),1)
>
> I have no idea on how to apply this to var3 to make it dependent on
> selections from var1 and var2. I'm not sure whether this is the right
> direction to go into.
From: Mikej on 15 Apr 2010 09:11
Thanks for all the suggestions. I'll look at that link and see how I can
integrate VBA. I'll try to adapt this within the next few days and keep you
posted.
First  |  Prev  | 
Pages: 1 2
Prev: If argument
Next: Protecting Sheets with drop down boxes