Results 1 to 9 of 9

Remove #value! error

This is a discussion on Remove #value! error within the Excel Questions forums, part of the Question Forums category; I have a workbook that will have total 13 sheets; 12 for each month Jan to Dec and 13th is ...

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Posts
    97

    Red face Remove #value! error

    I have a workbook that will have total 13 sheets; 12 for each month Jan to Dec and 13th is the summary. I will call the data from each of the 12 sheets into the 13th sheet. A new sheet for each month would be created at the beginning of each month. Meanwhile I set up formula in the the summary sheet to call data for all the months whenever the file is opened. Right now I have only January sheet created in the workbook. I dont have any other month sheet in the workbook. At this point for February to December when I call the data, it gives me #value! error and what i understand is that it is because the refered sheets donot exist. Can I get a blank cell instead of this error? I use the following formula in the summary sheet:

    =SUMIF(February!$L$3:$L$13,"=???5*",February!$M$3:$M$8)

    The above code works fine when used to get January data in summary sheet.


    any help appreciated.

  2. #2
    Board Regular
    Join Date
    Jun 2008
    Location
    Cleveland, OH
    Posts
    343

    Default Re: Remove #value! error

    =if(iserror(SUMIF(February!$L$3:$L$13,"=???5*",February!$M$3:$M$8)),"",SUMIF(February!$L$3:$L$13,"=???5*",February!$M$3:$M$8))


    I think that would give you a blank cell

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Posts
    97

    Default Re: Remove #value! error

    Thanks, it did work, but on pressing enter, it opens a dialog box "Update Values: November" and wants me to select a file and click ok. Also this dialog box is displayed only when i click enter when the cursor is in the formula bar displaying the formula entered. But if I am in a different cell and move on to that particular cell and press enter it doesnot open the dialog box.

    Did I make myself clear?

  4. #4
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,262

    Default Re: Remove #value! error

    Do you have a November sheet created yet?
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Posts
    97

    Default Re: Remove #value! error

    No..I have only one sheet created: January. but I am applying formulas for all the months. November is just an example.

  6. #6
    Board Regular Jonnyu182's Avatar
    Join Date
    May 2008
    Location
    Leeds
    Posts
    99

    Default Re: Remove #value! error

    In order for you to reference the November sheet you need to have a sheet called November. This is why you are getting the pop up dialog. It is trying to find the November Sheet.
    A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station...
    __________________

  7. #7
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,262

    Default Re: Remove #value! error

    If you're maintaining the workbook, maybe you can create all of the sheets and then hide the ones that are in the future. At the start of each month, it's simple enough to unhide the proper sheet (you could even have Excel do it for you with a little VBA)...just my $.02
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

  8. #8
    Board Regular
    Join Date
    Nov 2008
    Posts
    97

    Default Re: Remove #value! error

    Thanks everyone..

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    66,237

    Default Re: Remove #value! error

    If you are not unconfortable with expensive formulas regarding this particular wb...
    Code:
    =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
       SUMIF(INDIRECT("'"&A2&"'!$L$3:$L$13"),"=???5*",
            INDIRECT("'"&A2&"'!$M$3:$M$13"))))
    where A2 houses a sheet name like February.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com