Drop down list query

[SD125]

Hi all,

Using MS Excel 2003, I have a table which includes a list of names down in
column A, then across the top row is a group of 5 values (0-10,11-20,21-30
etc), the following row then has each of these lists split into 3 further
groups within each of the above groups (0-12,13-24,-25-36 - these are for
months).

The rest of the table is filed with numbers which would be read against
which name and group it fitted into.

My query is:

Is there a way that I can use drop down lists to select each of the outcomes
from the table (eg the name, between 11-20 and then 12-24) and then they tell
me the value of the cell which the would equal.

I would like to know if there is a specific formula which can be used for
this.

Many thanks in advance.

S

 

[Tim879]

I don't think you can do this with drop downs but if you are willing
to modify your data layout, you can mimic what you describe below by
using a pivot table.


Try this:
1) transpose the months (0-12, 13-24...) from a column to a row
2) Select all the data, go to Data - > Pivot Table and Pivot Chart
(follow the steps to create a pivot)
3) Finally, add the name field as a row and the 2 column fields as
columns and add the numbers you want to return to the data area.

you can then use the drop downs in the pivot to filter the data as you
describe below.

 

[SD125]

Thanks for the swift response Tim but I am unsure as to what you have
suggested, as I am fairly new to excel.

I'll try to expalin the table I have a little better so that you can
understand what it is I want:

A3:A20 are name fields

I have then merged then top cells B11, E1:G1 etc with the 0-10,11-20 etc
as mentioned below.

B2,C2 and D2 then have 0-12, 13-24,25-36 respectively, this is then repeated
in E2,F2,G2.

The rest of the table the contains values which are what correspond to the
name, value and month being picked.

When just looking at the table this all looks pretty but I'm unsure what I
would need to do next. What I want is somewhere else in the sheet to be able
to select the three outcomes and it to then tell me which value in the table
that corresponds to.

Thanks again