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

Originally Posted by whylucky

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.

Originally Posted by whylucky

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).

These are the Named Ranges:

Named Ranges.jpg

Originally Posted by whylucky

I also tried to use index per your suggestion but it doesnt seem to be producing any results.

exp1:
=IF(ISERROR(MATCH(1E+300,'One Barrel'!$B:$B,1)),'One Barrel'!$D$4,'One Barrel'!$D$4:INDEX('One Barrel'!$D:$D,MATCH(1E+300,'One Barrel'!$B:$B,1)))
number1:
=IF(ISERROR(MATCH(1E+300,'One Barrel'!$B:$B,1)),'One Barrel'!$A$4,'One Barrel'!$A$4:INDEX('One Barrel'!$A:$A,MATCH(1E+300,'One Barrel'!$B:$B,1)))
temp1:
=IF(ISERROR(MATCH(1E+300,'One Barrel'!$B:$B,1)),'One Barrel'!$C$4,'One Barrel'!$C$4:INDEX('One Barrel'!$C:$C,MATCH(1E+300,'One Barrel'!$B:$B,1)))

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:

=IFERROR('One Barrel'!$D$4:INDEX('One Barrel'!$D:$D,MATCH(1E+300,'One Barrel'!$B:$B,1)),'One Barrel'!$D$4)

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.