+ Reply to Thread
Results 1 to 7 of 7

#VALUE! Error Help

  1. #1
    Registered User
    Join Date
    11-07-2018
    Location
    Indiana
    MS-Off Ver
    Office 365
    Posts
    22

    #VALUE! Error Help

    Hello,

    I am having an issue with a formula... I know #VALUE! errors are typically caused by extra spaces, formatting errors, etc. I am at a loss. I feel like there may be something simple that I am missing, but I am slowly losing it the longer I look at it. I am in need of an extra set of eyes, that's for sure. I've attached a copy of my workbook. Where the cursor currently is shows one of the errors. It partially works in the rows above that, but I can't find where it differs.
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,974

    Re: #VALUE! Error Help

    The formula is a bit complicated so I haven't done a deep dive but this problems sticks out. You have references in your formula to another table:

    COLUMN(tblPRODUCTION[@SUN2]
    COLUMN(tblPRODUCTION[@SAT2]

    but all of the SUN2 and SAT2 values in that table are 0, so COLUMN returns #VALUE! which propagates upwards. This is true for all SATx and SUNx references, which appear in various forms in many of your formulas.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,780

    Re: #VALUE! Error Help

    Why would the COLUMN function care what the contents of the cell are?
    Everyone who confuses correlation and causation ends up dead.

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,780

    Re: #VALUE! Error Help

    The error is because you used @ in the formula references, which means "this row". The tblProduction table only has 69 rows, so any references in the formula table from row 70 onward won't work. Removing the @ symbols should fix it.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,974

    Re: #VALUE! Error Help

    Quote Originally Posted by romperstomper View Post
    Why would the COLUMN function care what the contents of the cell are?
    Whoops. Somehow I was thinking it was going to use the contents of the cell to return a column reference for another cell. Clearer heads prevailed.

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    3,171

    Re: #VALUE! Error Help

    Hi,
    try this formula in D instead:
    =IF([@[Type Gal]]="NA", 0,SUM(FILTER(tblPRODUCTION[[SUN1]:[SAT1]],tblPRODUCTION[Type]=[@[Type Gal]])))*([@[Amount Per Gal]])
    Attached Files Attached Files

  7. #7
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,780

    Re: #VALUE! Error Help

    Quote Originally Posted by 6StringJazzer View Post
    Somehow I was thinking it was going to use the contents of the cell to return a column reference for another cell
    I must admit that when I saw your comment and then the formula with the ADDRESS function, I initially went, "yeah, that would do it" before my brain did a double take.

+ 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] Odd Error Handling Behaviour; goes to error handler even when no error?
    By kalikj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-04-2018, 10:04 AM
  2. [SOLVED] Run-tim error -2147467259(80004005): Automation error Unspecidied error
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2014, 12:12 PM
  3. Excel macro (compile error. syntax error.) error
    By salar_younis in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-06-2014, 06:11 AM
  4. Receiving following error “Complie error : syntax error” Help
    By masond3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2012, 10:19 AM
  5. Error "run-time Error '1004': General Odbc Error
    By D4WNO77 in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-16-2012, 09:55 AM
  6. Error 75 File/Path access error, sometimes Error 1004
    By smokebreak in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-16-2011, 02:35 PM
  7. Error Handling - On Error GoTo doesn't trap error successfully
    By David in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-16-2006, 02:10 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