+ Reply to Thread
Results 1 to 3 of 3

Cell reference is formula, but getting #Value error?

  1. #1
    Forum Contributor
    Join Date
    05-23-2007
    Posts
    126

    Cell reference is formula, but getting #Value error?

    I have two worksheets: "POINTS" and "Billing Summary"
    On the POINTS sheet, I'm trying to add together different cells from Billing Summary.
    I'm attaching the file, but basically there's something wrong with the formulas in Billing Summary columns E, H, K, and N.
    The formula works, but the POINTS sheet isn't seeing it as valid...
    Thoughts? Help?
    Thanks!
    Attached Files Attached Files

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

    Re: Cell reference is formula, but getting #Value error?

    The values in each of the Points columns are all empty string "". Excel does not know how to add strings together (using the + operator). When you try to add text strings together like this, Excel throws an error.

    Solutions depend on exactly what you want to do.
    You could use SUM() instead of +. =SUM('Billing Summary'!E2,'Billing Summary'!E15). The SUM() function knows how to ignore text and would return 0 instead of an error.
    You could have the Points formula on Billing Summary return 0 instead of empty string "".
    or something else that fits into your project better.

    Help us understand how you want this to work when the points formula is returning empty string and we'll see what we can suggest.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    05-23-2007
    Posts
    126

    Re: Cell reference is formula, but getting #Value error?

    Thanks MrShorty! The one (simple) thing I didn't try... SUM worked... which I virtually always use....
    Many thanks again!!

+ 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] Formula error when a reference cell is blank
    By Rookie2016 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2017, 12:50 AM
  2. Replies: 1
    Last Post: 02-11-2015, 01:56 PM
  3. [SOLVED] formula returns a #VALUE! error if no value in reference cell???
    By ciapul12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2014, 05:39 PM
  4. [SOLVED] Replace absolute cell reference with Indirect cell reference in formula
    By Roothy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-11-2013, 04:46 AM
  5. Dragging formula, change sheet reference, but not cell reference
    By Kalilaya0419 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:50 PM
  6. Replies: 1
    Last Post: 04-30-2012, 12:07 PM
  7. Invalid or unqualified reference error when inserting a formula into a cell via VBA
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-11-2010, 10:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1