+ Reply to Thread
Results 1 to 7 of 7

Yes No Option to Effect Sum

  1. #1
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Yes No Option to Effect Sum

    Hi Excel Experts,

    I have simple for some calculation, I'm trying to fix an option Yes or No which decide to add this in Total or no, like when I choose Yes it's mean to Add in Sum When I choose No It's mean to not add in some, please check the screenshots and Excel File.

    Thank in Advance.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,897

    Re: Yes No Option to Effect Sum

    I did not try to analysis your sheet, but maybe a helper column would work, then add the cells in the helper column, for example:
    In C9 I entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Yes No Option to Effect Sum

    Hi Davesexcel,
    This is not which I'm looking, can you please check the screenshots or Excel sheet?

  4. #4
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Yes No Option to Effect Sum

    Hi, any expert here?

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,462

    Re: Yes No Option to Effect Sum

    My first thought was to expand your data validation, but I do not use data validation at all, so I am not very good at it. Your current data validation is "allow values from the 'choose' list". If you can expand the data validation (probably through the custom option using a likely complex formula) so that "If $B$10="yes" don't allow anything to be entered, otherwise allow a value from 'choose' to be entered". I am not very good at it, but, with that custom formula option, you can build a pretty complex formula to check combinations of values and restrict entry based on some pretty complex criteria.

    My first thought might be something like =IF($B$10="yes",B11="",COUNTIF(choose,B11)>=1)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Yes No Option to Effect Sum

    @MrShorty - that is an excellent suggestion - avoids VBA which is the way my mind was heading
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Yes No Option to Effect Sum

    and perhaps for your next worksheet, something like this:

    - place a button over cell C7 and ensure it obscures the whole cell
    (C7 will carry a value of "yes" or "no")
    - add Data Validation to cell B7
    - Data Validation formula could be:
    =C7="yes"
    (which only allows input if value "yes" is in cell C7)

    - assign code below to the button
    Please Login or Register  to view this content.
    Button caption is the opposite of the value sitting in cell C7
    Suggest that C7 should default to "no" when form is blank
    Last edited by kev_; 08-10-2017 at 05:28 PM.

+ 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. Rollover Effect
    By manue2elle in forum Excel General
    Replies: 0
    Last Post: 04-15-2015, 12:53 PM
  2. Help…! How do I look for cause and effect
    By CDC13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-30-2013, 04:01 AM
  3. effect:IF statement
    By adhamija in forum Excel General
    Replies: 12
    Last Post: 05-01-2011, 11:57 AM
  4. PageSetup has no effect
    By Jeroen1000 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-02-2009, 04:38 AM
  5. Typewriter Effect
    By Skydiver in forum Excel General
    Replies: 5
    Last Post: 06-05-2006, 05:20 PM
  6. What is the reverse effect?
    By Rebecca in forum Excel General
    Replies: 1
    Last Post: 03-14-2006, 11:25 AM
  7. [SOLVED] Mouse over effect, anyone know how to do it?
    By Mark Stephens in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-04-2005, 09:06 AM

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