Multiple calculations to populate multiple cells

JRoadkill

New Member
Joined
Apr 25, 2017
Messages
1
Hi!

I am relatively OK with the basic IF and VLOOKUP statements but anything that requires calculation from multiple cells that gets me a little bit lost.
I will try to explain exactly what I am trying to do, hopefully it makes sense.

I want cells D through H to populate from the information I enter into cells A through C.

IF A2 = "W" and B2 = "I" and C2 = "H", then look in other sheet for header "W/I/H"
Other sheet has several tables - each of which are three columns giving all possible values to "H", "A" and "D"
The table with header "W/I/H" contains all possible values of "H", "A" and "D" if all three "W/I/H" cells are met
If the table with header "W/I/H" only contains one possibility of "H" = 13 and "A" = 12 and "D" = 12, then populate cells D2 to F2 with the values of the one possibility
If more than one possibility exists, populate cells D2 to F2 with a drop-down selection from another sheet but display the words "Multiple Results" in the cell
In cell G2, add the three values from D2 to F2 together out of a total of 45 and provide a percentage.

Repeat for all tables depending on what is in cells A, B and C in each row.


Examples of results received:

IF A2 = "W" and B2 = "E" and C2 = "H+A+D"
Then D2 = 15, E2 = 15, F2 = 15 and G2 = 100% - Because 15 + 15 + 15 = 45 out of 45


A
B
C
D
E
F
G
H
1
Overall
Att
Best
H
A
D
%
Overall %
2
W
E
H+A+D
15
15
15
100%
100%

<tbody>
</tbody>






IF A2 = "A" and B2 = "I" and C2 = "H+A"
Then D2 = multiple options, E2 = multiple options, F2 = multiple options and G2 = multiple percentages - Because D + E + F = multiple out of 45
Cells D to G have a drop-down to select the 6 different options
Cell H displays the percentage range that is possible from the 6 options

A
B
C
D
E
F
G
H
1
Overall
Att
Best
H
A
D
%
Overall %
2
A
I
H+A
14
14
13
13
13
13
14
14
13
13
13
13
1
0
3
2
1
0
64.45%
62.23%
64.45%
62.23%
60%
57.78%
62.23% - 64.45%
3
A
I
H+D
14
14
13
13
13
13
1
0
3
2
1
0
14
14
13
13
13
13
64.45%
62.23%
64.45%
62.23%
60%
57.78%
62.23% - 64.45%

<tbody>
</tbody>

















P.S. I don't have to have tables, any suggestions on how best to store the data are welcome.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top