Use of 'flexible' formula based on cell value?

Hi.

I'm working with a complex and therefore very long formula which (based on selected filtering) needs to use values in a certain column to calculate the results. I have created a combo box. Based on selected setting in this combo box, the formula needs to use the values in either column E, column F, column G etc. I would like to prevent a huge IF-based formula by combining this lengthy formula multiple times, therefore I have been looking into using a (text?) reference in the formula in stead.

For example: CEL A1 contains the column which is to be used in the formula (and is determined based on the selection in the combo box). I'm trying to come up with a formula which is based on the column which is mentioned in cell A1. The formula which is used to calculate the results would be something like =SUM('A1' + 1) in which the selection will determine if the formula will be =SUM(E1+1), =SUM(F1+1). SUM(G1+1) etc.

So far I have not had any luck in getting this to work. Any ideas on this (is this possible at all?).

Thanks in advance,

Eric

Based on what I read, I feel =Choose is what you need..

Can you attach a sample Workbook?

Deep

And to attach a sample workbook for Deep.

Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Have you tried INDIRECT?
=INDIRECT(A1)+1

Thanks for the replies so far. I've added an example sheet in which I have attempted to explain the formula I'm looking for.

Cel B6 contains a dropdown list which contains the values of 3 columns (D, E and F). Based on the selected value on the dropdown, I'm attempting to create a 'flexible' formula which would sum the values in either column D, E, or F.

The concatenated formula I'm looking for would be something like =sum( & B7 & "2:" & B7 & "4") in which B7 is a link to the selected column in the dropdown.

Expected result of formula when selecting D: 6 (1+2+3)
Expected result of formula when selecting E: 15 (4+5+6)
Expected result of formula when selecting F: 24 (7+8+9)

Hope this explains what I'm attempting to do.

flexible formula example.xlsx

Thanks in advance for any tips on this

try
=SUM(INDIRECT(B6&"2:"&B6&"4"))

One way...

=SUM(INDEX(D2:F4,0,MATCH(B6,{"D","E","F"},0)))

You're welcome. Thanks for the feedback!