+ Reply to Thread
Results 1 to 14 of 14

Use Combo Box selection in SUM formula

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Use Combo Box selection in SUM formula

    On the attachment (which i hope has worked), i have a sheet i have added a Combo Box too with a drop down menu in, with two options, Y and N

    Now in another cell, H24, i want to use the chosen option in a formula, to say that if Y is chosen then the value in H24 is to be 4.38, but if N is chosen i want the value in H24 to show as 0

    How can i do this plz? i have looked on google and tried but although the combo box is drawn over F6, the SUM formula didnt seem to pick up any values entered in F6, so i assume it has to somehow link to the combo box itself and not the cell it is over? I cant see anywhere either to give that combo box a name in order to link it?

    any help much appreciated, thanks!
    Attached Images Attached Images

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Use Combo Box selection in SUM formula

    Right-click the combo box - you should be able to associate it with a particular cell.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Use Combo Box selection in SUM formula

    Are you following this :

    Untitled.png
    Quang PT

  4. #4
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Use Combo Box selection in SUM formula

    i dont i'm afriad, this coding isnt an area i am very good at, thats totally bamboozling to me the image above about what is being done

  5. #5
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Use Combo Box selection in SUM formula

    hiya, just done that, i've Linked it to cell F13. On the image below, i have tried to enter a formula i found online that says if '1' is selected then "good" should show in cell I13, and if not it shows 'bad', but if i select 1 in the drop down menu is still shows 'bad'. You can see the formula at the top i have used which i found online...... any ideas plz?

    query2.jpg

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: Use Combo Box selection in SUM formula

    See attached:

    J2 is link cell

    Results in H24
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Use Combo Box selection in SUM formula

    thats what i need, thanks! but how did you do that? i havent heard of a Link Cell before, i've just googled it and couldnt see anything about how to create one ? sorry if these seem easy questions

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: Use Combo Box selection in SUM formula

    Right click on the Combo box and you will get the screen shown in post #3

    Enter J2 (or whatever) into Cell Link:

    Input Range: is H2:H3 (containing "Y","N")

  9. #9
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Use Combo Box selection in SUM formula

    thanks, i'll have a look agani
    Last edited by zhunter71; 04-20-2018 at 09:05 AM.

  10. #10
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Use Combo Box selection in SUM formula

    on the attached spreadsheet , TEST1, have you any idea why it doesnt work

    The drop down menu has values 1 to 25 in, its Linked to cell J1 so whatever value you choose appears there

    then in cell H2 i have used the MATCH option to choose the corresponding value to whichever value is chosen in the drop down, but it just says #N/A

    thanks
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: Use Combo Box selection in SUM formula

    I changed combo Box to "Form Control" with formula in H2. "Active X" controls treat input as TEXT so your entry was TEXT being compared to NUMBER)

    In H3, formula uses Data Validation in E1 (an alternative)
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Use Combo Box selection in SUM formula

    thats brilliant! perfect that! i will have a look now how to make the "form control" drop down menu as opposed to a Combo Box :D thanks again!

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: Use Combo Box selection in SUM formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  14. #14
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Use Combo Box selection in SUM formula

    done! thanks again, been a big help sorting that :D

+ 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. Poplulating a second combo box based on user selection in first combo box
    By lateniteNC1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2017, 12:40 AM
  2. Replies: 19
    Last Post: 09-06-2014, 06:30 PM
  3. Replies: 8
    Last Post: 12-26-2013, 02:23 PM
  4. [SOLVED] Combo Box2 selections available based on Combo box 1 selection
    By wishn4fishn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-28-2013, 08:13 PM
  5. [SOLVED] How do I use Combo box selection to determine formula used?
    By 7Black in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-30-2013, 01:20 AM
  6. combo box input range based on the other combo box selection
    By hedayet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-08-2013, 02:27 AM
  7. Looking to code combo/drop down list user selection and display formula results
    By toddbailey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-07-2012, 06:17 PM

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