Google Sheet Dynamic Sheet Range: Offset and Index and Indirect

1
votes

I have a chart that references a separate sheet. So the source for the chart is a range like this 'Daily'!A1:EL14

The source sheet grows daily by 1 column. I want the chart to update dynamically every day after the new column is added. I believe that requires the use of INDIRECT

I can get values from a column, but can't get the actual CELL REFERENCE or a string like "EL14".

Here's functions that return values:

NUM_COLUMNS

= countif('Daily'!A1:ZZ1, "<>")

VALUE OF LAST COLUMN IN ROW 1

= OFFSET( 'Daily LT 1sec'!A1, 0, (countif('Daily'!A1:ZZ1, "<>")-1) )

VALUE OF LASTROW : LASTCOLUMN

= OFFSET( 'Daily LT 1sec'!A1, (countif('Daily LT 1sec'!A1:A100, "<>")-1), (countif('Daily'!A1:ZZ1, "<>")-1) )

Can someone explain to me how I would use INDIRECT to actually get a create a valid cell range to use in the chart definition?

1
google-sheets

1 Answers

0
votes

You may use extra columns for source range. The empty cells won't affect the chart.

In my sample I used range A1:B508, but actual data is in range A1:AB8: enter image description here

I've tried to use formula as chart source, but that didn't work. I found some more info here:

https://productforums.google.com/forum/#!msg/docs/d08hJbjfaLI/HqACX_7zA3MJ