Database similarity

  • Thread starter Thread starter dranon
  • Start date Start date
D

dranon

I need to lookup a value based on a looked up value. Can this be done
in Excel? Example cell contents (range P1:R5) [I hope this is
readable]-

=0 ="" =""
=IF(Q2=Q1,P1,P1+1)[result is 1] ABC Hello
=IF(Q3=Q2,P2,P2+1)[result is 1] ABC Goodbye
=IF(Q4=Q3,P3,P3+1)[result is 2] XYZ Jump
=IF(Q5=Q4,P4,P4+1)[result is 2] XYZ Sit

Second range (Y1:Y2) also defined name PickListOne

=VLOOKUP(ROW(),$Q$2:$R$5,2,0)[Result is ABC]
=VLOOKUP(ROW(),$Q$2:$R$5,2,0)[Result is XYZ]

Pick Lists:

a1: Validation Allow=List, =PickListOne [This lets me choose between
ABC and XYZ]
a2: ???????????????????????????????????????????????

What can I put in a2 so that I can pick from "Hello" or "Goodbye" if
a1 is "ABC" or pick from "Jump" or "Sit" if a1 is "XYZ"?

Yes, I know I should do this in Access. Alas, that is not an option.

Thanks
 
Hi,

First, it probably could be done in Access, but there is really no reason it
should be done there.

Second, I can't make out what you want.

What does this mean:

=0 ="" =""

What is going on here:

=IF(Q2=Q1,P1,P1+1)[result is 1] ABC Hello

What relevance does the IF function or its results play for your problem?
If the above is in the range P1:R5 is "ABC Hello" in one cell?

If you have
=VLOOKUP(ROW(),$Q$2:$R$5,2,0)[Result is ABC] in Y1:Y2 what is this about a
picklist in those cells? Alos why bother with a vlookup? Just enter ABC in
Y1 because you are using a lookup function on a single row, there is only one
possible result.
=============
Having said all that, I think what you need is shown in the following example.

Here are the steps for creating a dynamic data validation list:

1. Create three or more list in the spreadsheet. For example:
A B C
New York Quebec Canada
Seattle Toronto US
Miami Victoria

You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this
last is optional

2. Select the cell where you want the initial validation list, for this
example, D1
3. Choose Data, Validation
4. From the Allow drop-down and choose List
5. In the Source box enter the following formula
=Countries
6. Move to the location where you want the dynamic (changing) list to
appear, say for example E1
7. Choose Data, Validation, List and in the Source box enter the formula
=INDIRECT(D1)

You can also make a self-referential dynamic data validation list.
 
Hi,

First, it probably could be done in Access, but there is really no reason it
should be done there.

Second, I can't make out what you want.

What does this mean:

=0 ="" =""
Click to expand...

P1 =0
Q1 =""
R1 =""
What is going on here:

=IF(Q2=Q1,P1,P1+1)[result is 1] ABC Hello
Click to expand...

P2=IF(Q2=Q1,P1,P1+1)
Q2=ABC
R2=Hello

The result of the formula in P2 is 1
What relevance does the IF function or its results play for your problem?
Click to expand...

Column P is a numeric counter that shows "1" for all rows for the
first "name" in Column Q, then it goes to "2" as long as the name in
Column Q is the second "name", and so forth.
If the above is in the range P1:R5 is "ABC Hello" in one cell?
Click to expand...

No, P:R is three columns, so I gave you three values. ABC is intended
for column Q, Hello is intended for column R.
If you have
=VLOOKUP(ROW(),$Q$2:$R$5,2,0)[Result is ABC] in Y1:Y2 what is this about a
picklist in those cells? Alos why bother with a vlookup? Just enter ABC in
Y1 because you are using a lookup function on a single row, there is only one
possible result.
Click to expand...

If you extend the data to hundreds of names in columns Q and dozens of
values for each name (shown in Column R), then I think it may make
more sense.
=============
Having said all that, I think what you need is shown in the following example.

Here are the steps for creating a dynamic data validation list:

1. Create three or more list in the spreadsheet. For example:
A B C
New York Quebec Canada
Seattle Toronto US
Miami Victoria

You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this
last is optional

2. Select the cell where you want the initial validation list, for this
example, D1
3. Choose Data, Validation
4. From the Allow drop-down and choose List
5. In the Source box enter the following formula
=Countries
6. Move to the location where you want the dynamic (changing) list to
appear, say for example E1
7. Choose Data, Validation, List and in the Source box enter the formula
=INDIRECT(D1)

You can also make a self-referential dynamic data validation list.
Click to expand...

Thanks, but I don't have the ability to syphon off the lists in the
way you want me to. The data is organized as follows:

US New York
US Seattle
US Miami
Canada Quebec
Canada Toronto
Canada Victoria

I want to enter a value into cell D1 that is either US or Canada
(easy). I then want to have the pick list for cell D2 restricted to
the valid values, based on the selection in D1. If I selected US in
cell D1, then the pick list should show New York, Seattle and Miami.
If I selected Canada in cell D1, then the pick list should show
Quebec, Toronto and Victoria.

Thanks for replying. Any ideas on how to do what I need done?
 
I need to lookup a value based on a looked up value. Can this be done
in Excel? Example cell contents (range P1:R5) [I hope this is
readable]-

=0 ="" =""
=IF(Q2=Q1,P1,P1+1)[result is 1] ABC Hello
=IF(Q3=Q2,P2,P2+1)[result is 1] ABC Goodbye
=IF(Q4=Q3,P3,P3+1)[result is 2] XYZ Jump
=IF(Q5=Q4,P4,P4+1)[result is 2] XYZ Sit

Second range (Y1:Y2) also defined name PickListOne

=VLOOKUP(ROW(),$Q$2:$R$5,2,0)[Result is ABC]
=VLOOKUP(ROW(),$Q$2:$R$5,2,0)[Result is XYZ]

Pick Lists:

a1: Validation Allow=List, =PickListOne [This lets me choose between
ABC and XYZ]
a2: ???????????????????????????????????????????????

What can I put in a2 so that I can pick from "Hello" or "Goodbye" if
a1 is "ABC" or pick from "Jump" or "Sit" if a1 is "XYZ"?
Click to expand...

Well, I have done it, but I'm not proud of it. Maybe somebody can see
a way to improve on it.

First, I put a new range into the mix and created formulas so that the
data that is organized as above, which can't be changed, (Q1:R4):

ABC Hello
ABC Goodbye
XYZ Jump
XYZ Sit

is reorganized as (AA1:AD2):

ABC 1 Hello Goodbye
XYZ 2 Jump Sit

Then, in a2 I put the following into the Validation source (based on a
list):

=INDEX($AC$1:$AD$2,ROW(),0,1)

This will pick up all the values in this row from column AC to AD (I
never have a lot of detail lines, certainly no more than 52, so if I
have to go out to BZ it wouldn't bother me).

If anybody wants to see the (ugly) formulas used to reorganize the
data, let me know.

In the meantime, I have a pick list question that I'll post in another
thread.
 
Back
Top