Multiple inputs for cell/formula selection

Question

I have 3 dropdown inputs (x, y, and z). "X" has 10 possible choices, "y" has 12, and "z" has 52 for a total of 6240 possible combinations.

I also have 5 numerical inputs. The numerical inputs are put into one of 6240 possible formulas to determine an output for the user.

What I can't figure out is how to tell Excel to select the correct formula cell based on the COMBINED value of x, y and z.

For example:

if "x"=potato, "y"=carrot and "z"=apple, add all five numerical inputs and multiply by B1.

but

if "x"=artichoke, "y"=carrot and "z"=celery, add all five numerical inputs and multiply by B2.

I have to do this without using VB or macros. Is this possible?

Thanks.

Answer 1

CZG1,

Suggested formula for cell G13:

=INDEX(A1:XW391,(MATCH(F3,sectors,0)*38)+11,(54*MONTH(F4))-52+MATCH(F8,states,0)-1)

Note: I used 2 named ranges: sectors, which is M1:M10 on Sheet1, and states, which is A1:A52 on Sheet4

The formula is dependent on the sheet layout not changing!

___________

Regards, Tom

Answer 2

Thanks to both of you.

Lars-Åke - I think I over-simplified the problem, and I'm not sure I can extrapolate your solution to do what I need.

Ashish - Not sure how to upload here, but here is a link to the file. This is an incomplete copy, but you should be able to see the pattern.

Green at the top will be user input. Blue will be output. The data contained in the current linked spreadsheet requires F2=National and F3=1-Jan for any F6 (state) selection. The rows (from 17-52) will be repeated for every possible scenario of F2 and F3 values. Values for rows 17-37 are updated monthly from another spreadsheet.

The challenge is to have it find the correct cell (from row 49 in the example) based on the values of F2, F3 and F6, and then to get the correct result back to D13 and D14.

I'm a novice at Excel, and this is well over my head, but my boss doesn't seem to understand that. I really appreciate your assistance.

In the linked file the data that you refer above to as being in cells F2, F3, and F6 seem to be in F3, F4, and F8 respectively so I assume there is just a typo here.

You write that "The rows (from 17-52) will be repeated for every possible scenario of F3 and F4 values" (I corrected the typos here). How may industries (sectors) do you have? How many fiscal year start dates do you have? From your original post I guess you have 10 industries and 12 start dates. That means that you will have 120 blocks of data each consisting of 36 rows and 53 columns, right?

To find the correct cell for your result you can do like this:

Column:

=======

The column is the easy one if you just have the same spelling in cell F8 as you have on row 18. That means that you should have either "Nationwide" or "Total" in both places. One way of securing this would be to have the Data Validation list that you refer to in the Data Validation of cell F8 to be taken from the range B18:BA18.

(In the file you linked you have the range set as _options6 which is not defined as far as I can see.)

This formula will get you the column:

=MATCH(F8, A18:BA18, 0)

Row:

====

The row is bit more tricky. I guess that the actual name of the sector will be put into column A on the row with the state names, e.g. in cell A18.  I also guess that the text 'Jan FY' will be 'Feb FY', 'Mar FY' etc for the different data blocks representing different fiscal years.

If these guesses are wrong, please provide more details on how the data will look like.

This formula will get you the row:

=SUMPRODUCT(--(A1:A5000=F3),--(B3:B5002=TEXT(F4,"MMM FY")), ROW(32:5031))

Note: Depending on regional settings, you may have to modify the MMM part to get the month name correct.

Finally:

Bringing it all together, this is a formula to put into cell D13 (Next Year % Change):

=INDEX(A1:BA5000,SUMPRODUCT(--(A1:A5000=F3),

--(B3:B5002=TEXT(F4,"MMM FY")),ROW(32:5031)),MATCH(F8,A18:BA18,0))

Note: For this formula to work it is important that the lines with 'Jan FY' etc are exactly two rows below the (blue colored) row with the state names.

It is also important that the rows with "FINAL change" are exactly 31 rows below the (blue colored) row with the state names.

Hope this helps / Lars-Åke

Answer 3

Thanks to both of you.

Lars-Åke - I think I over-simplified the problem, and I'm not sure I can extrapolate your solution to do what I need.

Ashish - Not sure how to upload here, but here is a link to the file. This is an incomplete copy, but you should be able to see the pattern.

Green at the top will be user input. Blue will be output. The data contained in the current linked spreadsheet requires F2=National and F3=1-Jan for any F6 (state) selection. The rows (from 17-52) will be repeated for every possible scenario of F2 and F3 values. Values for rows 17-37 are updated monthly from another spreadsheet.

The challenge is to have it find the correct cell (from row 49 in the example) based on the values of F2, F3 and F6, and then to get the correct result back to D13 and D14.

I'm a novice at Excel, and this is well over my head, but my boss doesn't seem to understand that. I really appreciate your assistance.

Answer 4

Hi,

Could you upload your workbook with those many combinations.  Also, in the file, show all the possible formulas for different combinations.

Perhaps if we can identify some pattern in the formulas, we may be able to save some effort.

Answer 5

I have 3 dropdown inputs (x, y, and z). "X" has 10 possible choices, "y" has 12, and "z" has 52 for a total of 6240 possible combinations.

I also have 5 numerical inputs. The numerical inputs are put into one of 6240 possible formulas to determine an output for the user.

What I can't figure out is how to tell Excel to select the correct formula cell based on the COMBINED value of x, y and z.

For example:

if "x"=potato, "y"=carrot and "z"=apple, add all five numerical inputs and multiply by B1.

but

if "x"=artichoke, "y"=carrot and "z"=celery, add all five numerical inputs and multiply by B2.

I have to do this without using VB or macros. Is this possible?

Thanks.

Let your

• X dropdown be in cell A1, with data validation based on a list of values in cells A2:A11,
• Y dropdown be in cell B1, with data validation based on a list of values in cells B2:B13,
• Z dropdown be in cell C1, with data validation based on a list of values in cells C2:C53

Now put your 6240 formulas in cells D1:D6240   (this will take some time I guess...)

Finally, try this formula to get the correct value from these 6240 values:

=OFFSET(D1,(MATCH(A1,A2:A11)-1)*624+(MATCH(B1,B2:B13)-1)*52+MATCH(C1,C2:C53)-1,)

Hope this helps / Lars-Åke