Formula Error when creating Dynamic graph.....

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.