+ Reply to Thread
Results 1 to 9 of 9

Use of 'flexible' formula based on cell value?

  1. #1
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

    Question 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
    Last edited by Eric_25; 02-22-2014 at 09:02 AM.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Use of 'flexible' formula based on cell value?

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

    Can you attach a sample Workbook?

    Deep
    Cheers!
    Deep Dave

  3. #3
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Use of 'flexible' formula based on cell value?

    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
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,460

    Re: Use of 'flexible' formula based on cell value?

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

  5. #5
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Use of 'flexible' formula based on cell value?

    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
    Last edited by Eric_25; 02-20-2014 at 04:36 PM.

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Use of 'flexible' formula based on cell value?

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

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Use of 'flexible' formula based on cell value?

    One way...

    =SUM(INDEX(D2:F4,0,MATCH(B6,{"D","E","F"},0)))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Use of 'flexible' formula based on cell value?

    Thanks, that was exactly what I was looking for!

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Use of 'flexible' formula based on cell value?

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Flexible Counting Formula
    By SymphonyTomorrow in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2013, 02:36 PM
  2. Need formula help with automatic monthly column coloring based on flexible dates
    By excellearning in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2013, 04:27 AM
  3. Flexible Vlookup formula
    By garethjohn in forum Excel General
    Replies: 2
    Last Post: 04-28-2011, 12:38 PM
  4. Flexible Range Formula
    By sgrey24 in forum Excel General
    Replies: 5
    Last Post: 02-09-2010, 03:34 PM
  5. [SOLVED] I NEED A FLEXIBLE FORMULA
    By QC Coug in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2006, 07:36 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1