INDIRECT or OFFSET - can't make them work

[mattdavid.hall]

Hello!

We currently have a spreadsheet that has a chart. The Data Series for the chart is this formula:

='MKT-12b Past Due + NSCD'!$A$1102:$A$1192

We would like to make this "dynamic" where A1102 and A1192 are automatically adjusted to the last populated row.

So A1192 would be the last populated row and A1102 would be A1192 minus 90 rows. From what I have been reading I can use INDIRECT to pull from a parameters field where I've already grabbed the last populated row. However, I can't seem to make this work correctly on the chart.

Any suggestions?

 

[RoryA]

I'd define a name using:

=OFFSET(INDEX('MKT-12b Past Due + NSCD'!$A:$A,parameter_field_here),0,0,-90,1)

adjusting the red part to whichever cell has the last row number in, and use that name as the chart data source.

 

[mattdavid.hall]

I'd define a name using:

=OFFSET(INDEX('MKT-12b Past Due + NSCD'!$A:$A,parameter_field_here),0,0,-90,1)

adjusting the red part to whichever cell has the last row number in, and use that name as the chart data source.

That's an interesting method - I hadn't thought of INDEX. So I've created the named range and added it to my chart sheet. It comes up with the error "Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct."

This happened when I adjusted my chart series values to

='MKT-12b Past Due + NSCD'!RangeName

Edit: The named range is defined as a workbook in scope.

Thanks for the suggestion!

 

[RoryA]

What is the value of the cell with the last row number in it?

 

[mattdavid.hall]

What is the value of the cell with the last row number in it?

Current value is '1117' and it increases by one each calendar day.

The Cell it is located in is C3 and has this formula:

=SUMPRODUCT(MAX((B:B<>"")*ROW(B:B)))

So I adjusted your code earlier to be

=OFFSET(INDEX('MKT-12b Past Due + NSCD'!$A:$A,C3),0,0,-90,1)

and then it automatically adjust to be this:

=OFFSET(INDEX('MKT-12b Past Due + NSCD'!$A:$A,'MKT-12b Past Due + NSCD'!C3),0,0,-90,1)

.

 

[mattdavid.hall]

Just found my issue. I was placing just C3 instead of $C$3. Once I did that it seems to work. Testing further.