+ Reply to Thread
Results 1 to 9 of 9

Formula works in one cell, and then identical formula does not work in next cell! What?!

  1. 11-29-2017, 02:09 AM #1
    unclejemima
    unclejemima is offline
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    209

    Formula works in one cell, and then identical formula does not work in next cell! What?!

    I've got an attached spreadsheet for a floral design price builder that also makes a quote.

    I'm working on the last part of this project and in the 'Quote' Tab, in C10 is a formula that is just supposed to display the value in sheet 'Arrangement Builder'B1:C1).

    Thing is, if I put that formula in 'Quote' B10, it work PERFECT and returns the value in 'Arrangement Builder'B1:c1.

    If I put that formula in 'Quote' C10, where its supposed to go...it returns a 0!!!!?!?!?

    What the heck? How can the same formula work in one cell, and then not in the one beside it?!?!?

    I'm totally stumped. If someone can have a look I'd really appreciate it!
    Reply With QuoteRegister To Reply
  2. 11-29-2017, 02:13 AM #2
    kersplash
    kersplash is offline
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Formula works in one cell, and then identical formula does not work in next cell! Wha

    Might have something to do with the merged cells?
    Reply With QuoteRegister To Reply
  3. 11-29-2017, 02:14 AM #3
    AliGW
    AliGW is offline
    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
    81,162

    Re: Formula works in one cell, and then identical formula does not work in next cell! Wha

    You don't need to specify a range for a merged cell. Just use this instead:

    =IF('Arrangement Builder'!B1>"",'Arrangement Builder'!B1,"")

    Reference the top leftmost cell in the merge, as that is the only one containing data.
    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.
    Reply With QuoteRegister To Reply
  4. 11-29-2017, 02:20 AM #4
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Formula works in one cell, and then identical formula does not work in next cell! Wha

    Your Actual Formula Should be like this...

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  5. 11-29-2017, 02:22 AM #5
    AliGW
    AliGW is offline
    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
    81,162

    Re: Formula works in one cell, and then identical formula does not work in next cell! Wha

    Both solutions work in this case, actually.
    Reply With QuoteRegister To Reply
  6. 11-29-2017, 02:39 AM #6
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Formula works in one cell, and then identical formula does not work in next cell! Wha

    Quote Originally Posted by AliGW View Post
    Both solutions work in this case, actually.
    Yes, I seen your message after posting my reply. Otherwise, I would have not posted my suggestion
    Reply With QuoteRegister To Reply
  7. 11-29-2017, 02:50 AM #7
    AliGW
    AliGW is offline
    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
    81,162

    Re: Formula works in one cell, and then identical formula does not work in next cell! Wha

    Well, the OP has gone without letting us know if it was any good.
    Reply With QuoteRegister To Reply
  8. 11-29-2017, 03:48 AM #8
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,471

    Re: Formula works in one cell, and then identical formula does not work in next cell! Wha

    Or just:

    ='Arrangement Builder'!B1

    then custome format/Type:blank space (hit Space bar one)
    Quang PT
    Reply With QuoteRegister To Reply
  9. 11-29-2017, 12:36 PM #9
    unclejemima
    unclejemima is offline
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    209

    Re: Formula works in one cell, and then identical formula does not work in next cell! Wha

    Hey, you guys/gals are all awesome! thank you ;-)

    I'm still not sure as to exactly 'why' it was doing so...but best answer is probably the merged cell thing. Just funny thing is I tried putting my old formula that points to b1:c1 into a non-merged cell, just like the one in B10, and still wont work.

    Ah well, all good. The formula's suggested above all do work. Thank you! Marking as solved!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA- Works when cell value changes (manually) but not when cell formula value changes
    By glenwalcuch in forum Excel General
    Replies: 4
    Last Post: 08-28-2015, 06:46 AM
  2. [SOLVED] long IF formula that works on first cell but don't work on other cells
    By Linsie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-29-2015, 03:32 PM
  3. Counta(Offset...)) works in cell but not named formula; cell references are not relative.
    By January1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2014, 02:42 PM
  4. [SOLVED] weekday() result used in an array formula doesn't work. Formula works if i type in date
    By aarco50 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-25-2014, 03:25 PM
  5. [SOLVED] formula works in cell but doesn't work in vba
    By maleeny31 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2013, 12:36 AM
  6. Formula to color cell if value in two cells are not identical
    By DKAbi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-27-2012, 11:40 AM
  7. VB works only when value is typed into cell, not when formula updates cell value
    By dwiseman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-26-2010, 10:56 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1