For the Excel Query Gurus

[Andrew]

Hi All,

I'm only new to Excel macro/query building (Microsoft Infrastructure man)
but i have an issue with a clients spreadsheet and was wondering if its
posible to solve.

Question.

If i'm using a validated list column preforming a look up of another
worksheet for its value, if i select on of the ilst options can i bring back
in another field related fields to this value?
In excel terms - Worksheet 1 Row 1 Column A has a validated list contain
1,2,3,4.....10. If i select 1 i want to bring back in Row 2 Column B related
fields to this value which are true, false, end and start. These fields are
specified in worksheet 2.
I have been informed by othre chat groups that this is possible but no one
knows how?
Is this possible?? If so what is the macro/VB scripting needed to provide
this solution??
Is there a better approach??

Andrew

 

[Arvi Laanemets]

Hi

Wgen on sheet Worksheet 2 you have in range A2:A11 values 1,2,3,...,10, and
in range B2:B11 according values to return, then on Worksheet 1 into cell B1
enter the formula:
=VLOOKUP(A1,'Worksheet 2'!A2:B11,2,0)

 

[Jason Morin]

Check out VLOOKUP. There is a tutorial here:

http://www.contextures.com/xlFunctions02.html

HTH
Jason
Atlanta, GA

 

[Andrew]

Ok,

Thanks for your answers guys its answer half my problem.
Now if i use a query like
=IF(ISNA(VLOOKUP(D3,OptionsA186:B364,1,FALSE)),"",VLOOKUP(D3,Options!A186:B364,2,FALSE))
is there any way when return the second column answer in a validated list
that can be group by the selection made in vlookup field.

So to explain my self in a bit move details my table ray is from a10:b20.
With in this aray for rows 1 -10 in column a there is only 3 options that
each have 6-7 answer each. If i was to use a similar formula to what i showed
you above is there a way so when you select one of the 3 options in column a
it only returns the 6-7 answer for this option?

Andrew

 

[Arvi Laanemets]

Hi

You want choices for second data validation list (in column B) to depend on
selected value in first data validation list (in column A)? There are
several ways for it. Some examples:

1. Define different named ranges on separate sheet (a range for every choice
in column A, p.e. List1, List2, List3, ...). Define validation list source
for column B as
=CHOOSE(MATCH(SelectionInA,{selectionInA1;selectionInA2;selectionInA3,...},0
),List1, List2, List3, ...)

2. On separate sheet, create a table like:
SelectionInA, SelectionInB
selectionInA1 selectionInB11
selectionInA1 selectionInB21
....
selectionInA2 selectionInB12
selectionInA2 selectionInB22
....

The table must be ordered.
Now define a dynamic named range, which depends on selected value in same
row of column A, i.e. you select some cell in column B, and using INDEX or
OFFSET, and MATCH functions, you define the named range in a way, that
values in SelectionInB column, for which SelectionInA values match with
value in column A, are included. This named range will be the source for
validation list in column B.

3. On separate sheet, create a table like:
SelectionInA1, SelectionInA2, SelectionInA3, ...
SelectionInB11 SelectionInB12 SelectionInB13 ...
SelectionInB21 SelectionInB22 SelectionInB23 ...

Define (dynamic) named range p.e. List1, as first row of this table - you
can use i´t as source for data validation list in column A
Like as in p.2, define dynamic named range, p.e. List2, but now the column,
the range is defined in, varies depending on value in column A. This named
range will be the source for validation list in column B.