Dynamic chart from a range containing formulas

Hi guys. I have a range of cells (A1:E) with data referenced from another worksheet (sheet1). With if statement, this range remains empty until sheet1 is updated.

I want to insert a dynamic chart which updates when this range becomes non-empty. I have tried the offset function but it seems not to recognise cells containing formulas as empty even when they are virtually empty.

Formula blanks not being blank are a common problem, I would guess that you're using COUNTA to determine the number of rows / columns in offset.

Try finding the last row of numeric data by using something like MATCH(1e+100,A:A) instead.

To provide a more accurate solution, we will need more information in order to reduce guesswork. Your OFFSET formula to provide the ranges would help, as would knowing a column that holds numeric data to use for the row count.

Originally Posted by jason.b75

Formula blanks not being blank are a common problem, I would guess that you're using COUNTA to determine the number of rows / columns in offset.

Try finding the last row of numeric data by using something like MATCH(1e+100,A:A) instead.

To provide a more accurate solution, we will need more information in order to reduce guesswork. Your OFFSET formula to provide the ranges would help, as would knowing a column that holds numeric data to use for the row count.

Here it is sir.
=OFFSET(DynamicChart1!$A$2,0,0,COUNTA(DynamicChart1!$A:$A))

That's to make column A dynamic which I applied to the rest of the columns

I use =IF(TODAY()>L$1,'July 17'!$F$8,NA()) to update a chart from a worksheet. So can you use

=IF('Sheet 1'!A1>0,A1,NA())

Inserting NA prevents the chart from displaying

That is all well and good if the result of that formula is only used for the chart(s). If you have other formulas relying on those results as well then a deliberate NA() could be more of a problem than a solution.

See if this does what you need

=OFFSET(DynamicChart1!$A$2,0,0,MATCH(1e+100,DynamicChart1!$A:$A)-1)

Please not that the highlighted column should contain numeric data, if column A contains text references, you will need to use a different column for this part. Use the same column for everything to ensure consistency.

Originally Posted by jason.b75

See if this does what you need

=OFFSET(DynamicChart1!$A$2,0,0,MATCH(1e+100,DynamicChart1!$A:$A)-1)

Please not that the highlighted column should contain numeric data, if column A contains text references, you will need to use a different column for this part. Use the same column for everything to ensure consistency.

Awesome. It worked.
Thank you