+ Reply to Thread
Results 1 to 7 of 7

Formula Error when creating Dynamic graph.....

  1. #1
    Registered User
    Join Date
    10-01-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2010
    Posts
    4

    Formula Error when creating Dynamic graph.....

    To Whoever Can Help,

    I wrote some code in or to create a dynamic graph that uses the attached template sheet. However, i continually get an error message that pop ups whenever data is added or deleted. It is asking to verify the formulas are correct, which i know they are since everything still works. Is there anyway i can remove the warning or some way to bypass it? The code is in the name manager and the data selector. Thanks for any help in advance.

    Regards,
    Whylucky

    UPDATES:
    New Thread Title
    New Attachment (Unlocked and removed nonsense i tried to fix problem): Surge.xls
    Cross thread Post: http://www.mrexcel.com/forum/excel-q...re-errors.html
    Last edited by whylucky; 01-27-2016 at 09:41 PM. Reason: moderator

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,747

    Re: A formula contains one or more errors.....

    Quote Originally Posted by whylucky View Post
    i continually get an error message that pop ups whenever data is added or deleted.
    When I look at Name Manager, I see several entries with the same name. I assume they differ by scope. I am limited in what I can look at because the worksheet is protected by a non-null password. Please unprotect the worksheet.

    But the point is: some of the named entries contain #REF errors. I presume those are the ones that the chart Data Sources are referencing.

    So that is the source of "formula contains one or more invalid references" error when we press F9.
    Last edited by joeu2004; 01-27-2016 at 08:10 PM.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,934

    Re: A formula contains one or more errors.....

    These are the Named Ranges:

    Named Ranges.jpg
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    10-01-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: A formula contains one or more errors.....

    I removed the lock on the file, and i also removed some of the extra names i was using when attempting to fix the error myself

    If you would be kind enough to look it over again

  5. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,747

    Re: A formula contains one or more errors.....

    Quote Originally Posted by whylucky View Post
    I removed the lock on the file, and i also removed some of the extra names i was using when attempting to fix the error myself
    If you would be kind enough to look it over again
    For each name reference (exp1 and temp1), copy the referenced formula and paste it in some cell temporarily to allow you to debug it.

    They return a #REF error because the last parameter evaluates to zero. OFFSET(...,0,0) is not a valid reference.

    Aside.... Using OFFSET in a named reference is bad enough, performance-wise. All such references are evaluated every time Excel (re)calculates anything in the workbook.

    To make matters worse, you use COUNTA(B:B) and COUNTA(C:C). That causes Excel to examine all 65536 rows (xls file) of those columns every time Excel (re)calculates anything in the workbook. If you ever "upgrade" the file to xlsx, that will 1,048,576 rows. Ouch!

    You should consider using INDEX:INDEX instead of OFFSET. And if you must use COUNTA, use a limited range references, something that is more reasonable (but appropriate for your purposes, a judgment call). For example, COUNTA($C$1:$C$1000).
    Last edited by joeu2004; 01-27-2016 at 10:11 PM. Reason: INDEX:INDEX, etc

  6. #6
    Registered User
    Join Date
    10-01-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula Error when creating Dynamic graph.....

    I fixed the error i was having by always having the first cell containing some information (either a number or "-"). I also tried to use index per your suggestion but it doesnt seem to be producing any results.

  7. #7
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,747

    Re: Formula Error when creating Dynamic graph.....

    Quote Originally Posted by whylucky View Post
    I also tried to use index per your suggestion but it doesnt seem to be producing any results.
    Please Login or Register  to view this content.
    MATCH returns the actual row number of the last number in the column. This does permit empty cells between row 4 and the last number. But you probably would not like the graph that produces.

    1E+300 is intended to represent a "large number", larger than any number you would normally have. 1E+300 is not the largest value that Excel allows us to enter. But it is probably large enough, and it is easy to remember.

    INDEX('One Barrel'!$D:$D,...) is one of the few places where a full-column reference like D:D is okay. Excel indexes into the array; it does not load or process the entire column.

    ISERROR is needed because this is an xls file, which is XL2003 compatible. IFERROR does not exist in XL2003. If you ever upgrade to an xlsx file, the following paradigm would be more efficient:
    Please Login or Register  to view this content.
    In either case, if there are no values in row 4 or below, we return a reference to row 4. But again, you probably would not like the graph that produces.
    Last edited by joeu2004; 01-30-2016 at 03:49 AM. Reason: 1E300 explanation

+ 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. Replies: 2
    Last Post: 06-24-2015, 08:41 PM
  2. Formula Errors! Help please!
    By conwayroger25 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-19-2014, 11:55 AM
  3. If contains formula with index match formula produces errors
    By Nicole Matthews in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-31-2013, 07:39 PM
  4. Formula Errors
    By Oly Steel Man in forum Excel General
    Replies: 1
    Last Post: 08-25-2011, 10:35 AM
  5. Replies: 9
    Last Post: 03-16-2010, 03:34 PM
  6. [SOLVED] Formula errors
    By MeSteve in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-16-2006, 06:42 PM
  7. [SOLVED] Excel Throwing Circular Errors When No Errors Exist
    By MDW in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2006, 09:20 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