+ Reply to Thread
Results 1 to 6 of 6

how to get rid of #Value for following equation

  1. #1
    Registered User
    Join Date
    12-02-2012
    Location
    Harrow
    MS-Off Ver
    Excel 2003
    Posts
    73

    how to get rid of #Value for following equation

    I have the following equation

    =IF(ASSUMPTIONS!H11="-",DATE(YEAR($Z31)+5,MONTH($Z31),DAY($Z31)),ASSUMPTIONS!H11)

    However, there are blank rows which creates the error #VALUE. What do I need to add into this formula to prevent the error message appearing?

    Thanks

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: how to get rid of #Value for following equation

    Try this:

    =IF(ASSUMPTIONS!H11="","",IF(ASSUMPTIONS!H11="-",DATE(YEAR($Z31)+5,MONTH($Z31),DAY($Z31)),ASSUMPTIONS!H11))

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-02-2012
    Location
    Harrow
    MS-Off Ver
    Excel 2003
    Posts
    73

    Re: how to get rid of #Value for following equation

    HI that got rid of the value but it also turned all the other cells to a -

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: how to get rid of #Value for following equation

    Not sure what you mean - please attach a sample workbook.

    Pete

  5. #5
    Registered User
    Join Date
    12-02-2012
    Location
    Harrow
    MS-Off Ver
    Excel 2003
    Posts
    73

    Re: how to get rid of #Value for following equation

    HI Pete

    The overall file size is 1.54Mb- is there a way of uploading this?

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: how to get rid of #Value for following equation

    I only need a SAMPLE of data, which shows how it is laid out and how it interacts with other sheets - no more than 100 records. Can't you cut it down, remove any sheets that are not relevant and then save the file with a new name and post that. If you keep the structure of the data the same (i.e. same columns and sheet names), then it will be easier for you to apply back to your real file when/if I can offer you a solution.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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