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
<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
<tbody>
</tbody>
P.S. I don't have to have tables, any suggestions on how best to store the data are welcome.
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.