Prev: Password to open excel file
Next: Source and Destination worksheet difference
From: Dallasm on 30 May 2010 09:44
I have a spreadsheet with $costs arranged according to values in both rows
and columns.
How do I create drop down lists for both the rows and columns and then
return the cell value?
eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each
cell within this group how do I create drop downs for each selection, 2-6 and
B-G, and return the value to a selected cell.

From: T. Valko on 30 May 2010 12:40
In a typical table of this nature there would be descriptive row and column
headers. To do a lookup on this table you would then look for the
intersection of a specific row header and a specific column header.

Here's a simplified example...

...........A..........B..........C
1.................gizmoA...gizmoB
2.....blue........12.........10
3.....black......15.........21

You want to know how many black gizmoA's are in stock.

Lookup values:

E1: black
F1: gizmoA

Formula entered in G1:

=VLOOKUP(E1,A1:C3,MATCH(F1,A1:C1,0),0)

The lookup values in E1 and F1 can be selected using a data validation drop
down list. Watch this short video.

How to setup a data validation drop down list:

http://youtube.com/watch?v=t2OsWJijrOM


--
Biff
Microsoft Excel MVP


"Dallasm" <Dallasm(a)discussions.microsoft.com> wrote in message
news:ABBE9EE8-375D-4B38-9BFA-0D60E6334B81(a)microsoft.com...
>I have a spreadsheet with $costs arranged according to values in both rows
> and columns.
> How do I create drop down lists for both the rows and columns and then
> return the cell value?
> eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each
> cell within this group how do I create drop downs for each selection, 2-6
> and
> B-G, and return the value to a selected cell.
>


 | 
Pages: 1
Prev: Password to open excel file
Next: Source and Destination worksheet difference