+ Reply to Thread
Results 1 to 4 of 4

#VALUE Error for same formula with different worksheet reference

  1. #1
    Registered User
    Join Date
    06-05-2019
    Location
    Gold Coast
    MS-Off Ver
    Excel 2016
    Posts
    2

    #VALUE Error for same formula with different worksheet reference

    I need some help with this one. I have the following formulas in cells AE30 and AE31 on a worksheet named Class Numbers; I am currently creating a template so all values should equal zero until student data is entered.

    =IF(IntermediateB34!$H$6:$H$30>0,COUNTIF(IntermediateB3!$H$6:$H$30,'New Students'!$R$2),0)

    ------> this should count all the cells that are greater than 0 and = the value in New Students R2 (which is also zero in the template)

    It returns the value 0 now, which is correct and when there is data it counts it correctly

    =IF(IntermediateB4!$H$6:$H$30>0,COUNTIF(IntermediateB4!$H$6:$H$30,'New Students'!$R$2),0)

    Returns a #VALUE! error and I can't figure out why.

    I have gone through and checked that the cell formatting is the same in both places, the worksheet references are correct, I have evaluated the formulas in the reference worksheet and they solve out to zero as they should at the moment. I have also used error checking on the sheets and been told that all formulas are correct.

    Strangely enough, if I enter the second formula as an array it calculates correctly. I will use this as a work around but would like to figure out why the error is occurring.

    Any help is greatly appreciated

  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: #VALUE Error for same formula with different worksheet reference

    The IF function would normally work only on a single cell, unless it is part of an array formula, so that is why you are receiving the #VALUE error, as you are trying to apply it to an array of values.

    Why don't you just include the condition within a COUNTIFS formula, where you can have one or more conditions?

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-05-2019
    Location
    Gold Coast
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: #VALUE Error for same formula with different worksheet reference

    Pete,

    Thanks for your reply. I will give that a go. Any idea why that formula works for some cells but not others?

    Ben

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: #VALUE Error for same formula with different worksheet reference

    It may be that the 1st cell in some of your ranges, matches the criteria
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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] Cell reference is formula, but getting #Value error?
    By fourmurphys in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-29-2019, 11:39 AM
  2. Replies: 8
    Last Post: 12-24-2018, 04:29 AM
  3. Reference worksheet in a formula
    By logisticsexcel in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-15-2016, 08:05 AM
  4. Worksheet Reference Returns Type Mismatch Error when Using Network Drive
    By ShitLeopard in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2015, 04:14 PM
  5. Replies: 5
    Last Post: 02-06-2012, 12:36 PM
  6. if..then formula..circular reference error
    By nih in forum Excel General
    Replies: 8
    Last Post: 01-26-2010, 05:20 PM
  7. IF formula reference error
    By Micayla Bergen in forum Excel General
    Replies: 1
    Last Post: 06-02-2005, 03:05 AM

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