Question from an Excel newbie

[marcse86]

Hi guys,

I am new to Excel and would like to know what formula to use for the example below.

I have a data table which includes the type of fruit, the size and the result.

I know how to use the data validation function to create a selectable drop down list for both items, but would like to automatically calculate the result based on the options that I chose.

I would like this to be displayed in the result column.

​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Size[/TD]
[TD="align: center"]Apples[/TD]
[TD="align: center"]Oranges[/TD]
[TD="align: center"]Bananas[/TD]
[/TR]
[TR]
[TD="align: center"]Small[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]Medium[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]Large[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[/TR]
</tbody>[/TABLE]

​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Fruit[/TD]
[TD="align: center"]Apples[/TD]
[/TR]
[TR]
[TD="align: center"]Size[/TD]
[TD="align: center"]Med[/TD]
[/TR]
[TR]
[TD="align: center"]Result[/TD]
[TD="align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance

 

[FDibbins]

See if this will get you started?

[Table="width:, class:grid"][tr][td] [/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

3​

[/td][td]

Type​

[/td][td]

Category​

[/td][td]

Breed​

[/td][/tr]

[tr][td]

4​

[/td][td]Fish[/td][td]Marine[/td][td]Shark[/td][/tr]
[/table]
This would be where your drop-downs are
1. create a range name for the main category
2. create a range name for each sub-category, based on what it is

for mine, the main category is called Type (A2)
for the sub categories I give them the same name as in Type (B2:E2)
(if you have another level, you would repeat this for each subcategory, to get sub-sub-categries)

To get the DD's to work...

DD for B4 =TYPE
DD for C4 =INDIRECT(SUBSTITUTE(B4," ","_"))

THis would be your data table that th drop-downs reference...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

1​

[/td][td]Level 1[/td][td][/td][td]Level 2[/td][td][/td][td][/td][td][/td][td][/td][td]Level 3[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Type[/td][td][/td][td]Mammal[/td][td]Bird[/td][td]Fish[/td][td]Bug[/td][td][/td][td]Dog[/td][td]Cat[/td][td]Fresh Water[/td][td]Marine[/td][/tr]

[tr][td]

3​

[/td][td]Mammal[/td][td][/td][td]Dog[/td][td]Domestic[/td][td]Fresh Water[/td][td]Insect[/td][td][/td][td]Doberman[/td][td]Lion[/td][td]Trout[/td][td]Shark[/td][/tr]

[tr][td]

4​

[/td][td]Bird[/td][td][/td][td]Cat[/td][td]Wild[/td][td]Marine[/td][td]Arachnid[/td][td][/td][td]Poodle[/td][td]Tiger[/td][td]Minow[/td][td]Barracuda[/td][/tr]

[tr][td]

5​

[/td][td]Fish[/td][td][/td][td]Cow[/td][td][/td][td][/td][td][/td][td][/td][td]bulldog[/td][td]House[/td][td]Pike[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Bugs[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Hope this helps?

 

[Brian from Maui]

Hi guys,

I am new to Excel and would like to know what formula to use for the example below.

I have a data table which includes the type of fruit, the size and the result.

I know how to use the data validation function to create a selectable drop down list for both items, but would like to automatically calculate the result based on the options that I chose.

I would like this to be displayed in the result column.

​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Size
[/TD]
[TD="align: center"]Apples
[/TD]
[TD="align: center"]Oranges
[/TD]
[TD="align: center"]Bananas
[/TD]
[/TR]
[TR]
[TD="align: center"]Small
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[/TR]
[TR]
[TD="align: center"]Medium
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]6
[/TD]
[/TR]
[TR]
[TD="align: center"]Large
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]9
[/TD]
[/TR]
</tbody>[/TABLE]

​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Fruit
[/TD]
[TD="align: center"]Apples
[/TD]
[/TR]
[TR]
[TD="align: center"]Size
[/TD]
[TD="align: center"]Med
[/TD]
[/TR]
[TR]
[TD="align: center"]Result
[/TD]
[TD="align: center"]4
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance

Try,

=INDEX(B2:D4,MATCH(E1,A2:A4,0),MATCH(F1,B1:D1,0))

Where B2:D4 houses your values, A2:A4 houses your sizes and B1:D1 houses fruit names. Enter size in E1 and fruit in F1.

 

[marcse86]

Thank you!

 

[FDibbins]

oops I think I showed you how to creas DD's when you already had that part, and just wanted to pull data based on those DD selections - apologies