lookup with dropdown

[Ratedr]

Im trying to make a program for diamonds, where I can calulate the
cost of making a ring. I have an excel sheet like this:

ROUND
4 5 6 7 8
5pt 0.05 21 22 24 25 26
10pt 0.1 18 19 20 21 22
15pt 0.15 17 17 18 19 20
20pt 0.2 15 16 17 17 18
25pt 0.25 14 15 16 16 17
33pt 0.33 13 14 15 15 16
50pt 0.5 12 13 13 14 14


EMERALD
4 5 6 7 8
5pt NA NA NA NA NA NA
10pt NA NA NA NA NA NA
15pt 0.15 20 20 21 22 23
20pt 0.2 18 19 20 20 21
25pt 0.25 17 18 19 19 20
33pt 0.33 16 17 18 18 19
50pt 0.5 15 16 16 17 17


what I want to do, is have another sheet with a drop down chosing
round or emerald (there will be others but these for now). Then
another dropdown that will be 5pt, 10pt, 15pt, etc. Then the third
dropdown will be 4, 5, 6, 7, and 8. What I want the result to do, for
example if I selected emerald, 33pt, and 5..is to give me the result
of 17...if I selected round 5pt and 7..it would give me the result of
25. Thats it. Is this possible?

 

[Gord Dibben]

Looks like you need help on dependent lists.

See Debra Dalgeish's site.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

 

[Ratedr]

oh man..thats is really confusing to me (sorry I know I sound stupid).
I will try and figure it out..if anyone can look at the spreadsheet
that I have and help me along with it, I would really appreciate it.
If not, I will try and work it out.

 

[Ratedr]

ok..figured it out I think..but the way Im doing it from reading that
site, there are too many variables for that so Im going to have to
figure out a way, with an access database or something

 

[Pete_UK]

You have only quoted two example tables, which have identical layouts.
If all your tables will follow that same layout, then you can do it
quite simply in Excel. Set up another 2-column table, eg in columns M
and N, and list the headings for the other tables in column M and the
row number where the table starts in the other column, like this:

ROUND 1
EMERALD 11

In your other sheet you would have data validation picking up the list
of table types from column M - suppose this is in A1 of Sheet2. Your
second drop-down would be to select from 5pt, 10pt, 15pt etc, and this
could be in Sheet2!B1. The third drop-down would select from 4, 5, 6
etc, say in Sheet2!C1. You can define these lists of values in the
data validation dialogue, or you can give them named ranges on Sheet 1
and refer to the names in the DV boxes.

Define a named range All_tables as covering the cells from A1 to the
bottom of your data in column G on Sheet1.

Then in Sheet2, maybe cell D1, you could have this formula:

=INDEX(All_tables,MATCH(B1,Sheet1!$A$3:$A$9,0)+1+VLOOKUP(A1,Sheet1!M:N,
2,0),MATCH(C1,Sheet1!$C$2:$G$2,0)+1)

which should return the appropriate value.

Hope this helps.

Pete