+ Reply to Thread
Results 1 to 9 of 9

IF statement across multiple sheets

  1. #1
    Registered User
    Join Date
    04-28-2015
    Location
    Minnesota
    MS-Off Ver
    Excel Mac 2011
    Posts
    4

    Unhappy IF statement across multiple sheets

    This is my formula. =IF(Start!I7:End!I7>0, 0, Start!I7:End!I7) I get the error #value!

    I have a worksheet called Start at the beginning and a worksheet called End at the end of the sheets.

    On my sheet in the middle, I want a cell to add up all of the I7 values from Start to End and if it is greater than 0 to display 0. If not, I want it to display the sum of all of the I7 values.

    This formula works if I do =IF(Start!I7-End!I7>0, 0, Start!I7-End!I7) with the subtraction.

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: IF statement across multiple sheets

    Please Login or Register  to view this content.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

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

    Re: IF statement across multiple sheets

    Maybe this...

    =MIN(0,SUM(Start:End!I7))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: IF statement across multiple sheets

    Quote Originally Posted by Tony Valko View Post
    Maybe this...

    =MIN(0,SUM(Start:End!I7))
    Oh, why didn't I think of that?

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

    Re: IF statement across multiple sheets

    Quote Originally Posted by mikeTRON View Post
    Oh, why didn't I think of that?
    You just did!

  6. #6
    Registered User
    Join Date
    04-28-2015
    Location
    Minnesota
    MS-Off Ver
    Excel Mac 2011
    Posts
    4

    Re: IF statement across multiple sheets

    This is what I am going to use:

    =IF(MIN(START:END!I7)>0, 0, MIN(START:END!I7))

    I want it to subtract all of the sheets. I didn't know =MIN existed! Thanks everyone!

  7. #7
    Registered User
    Join Date
    04-28-2015
    Location
    Minnesota
    MS-Off Ver
    Excel Mac 2011
    Posts
    4

    Re: IF statement across multiple sheets

    This is what I am going to use:

    =IF(MIN(START:END!I7)>0, 0, MIN(START:END!I7))

    I want it to subtract all of the sheets. I didn't know =MIN existed! Thanks everyone!

  8. #8
    Registered User
    Join Date
    04-28-2015
    Location
    Minnesota
    MS-Off Ver
    Excel Mac 2011
    Posts
    4

    Re: IF statement across multiple sheets

    =IF(MIN(START:END!I7)>0, 0, MIN(START:END!I7))

    Doesn't give me the right answer. Telling me 0.

    I have 4 sheets where I7 is: 0, -149, 55, 0. The answer should be -204 (-149-55). The cells are empty in START and END sheets.

    =MIN does not mean minus. I don't want the minimum. I want it to subtract all the I7 cells from each other.

    =IF(SUM(START:END!I7)>0, 0, SUM(START:END!I7))
    This works if I make my cells all negative numbers (i.e. -55).
    Last edited by marahchristine; 04-29-2015 at 02:25 PM.

  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: IF statement across multiple sheets

    Quote Originally Posted by marahchristine View Post

    I have 4 sheets where I7 is: 0, -149, 55, 0. The answer should be -204 (-149-55). The cells are empty in START and END sheets.

    =MIN does not mean minus. I don't want the minimum. I want it to subtract all the I7 cells from each other.
    Then you would have to do something like this...

    =Sheet1!I7-Sheet2!I7-Sheet3!I7-Sheet4!I7

+ 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. How to SumIf over multiple sheets across a range with an If statement
    By love0126 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2013, 12:02 PM
  2. Max If statement not working across multiple sheets
    By Robmeister89 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-26-2013, 08:49 AM
  3. If Statement with Multiple Sheets
    By etulipano in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-24-2012, 06:59 PM
  4. If statement help with multiple sheets?
    By kezzby in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-19-2010, 07:54 PM
  5. If statement - Multiple Sheets
    By STEVEB in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-01-2006, 12:25 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