INDIRECT or OFFSET - can't make them work

mattdavid.hall

New Member
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?

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RoryA

MrExcel MVP, Moderator
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

New Member
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
Excel Formula:
``='MKT-12b Past Due + NSCD'!RangeName``

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

Thanks for the suggestion!

RoryA

MrExcel MVP, Moderator
What is the value of the cell with the last row number in it?

mattdavid.hall

New Member
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:
Excel Formula:
``=SUMPRODUCT(MAX((B:B<>"")*ROW(B:B)))``

Excel Formula:
``=OFFSET(INDEX('MKT-12b Past Due + NSCD'!\$A:\$A,C3),0,0,-90,1)``
and then it automatically adjust to be this:
Excel Formula:
``=OFFSET(INDEX('MKT-12b Past Due + NSCD'!\$A:\$A,'MKT-12b Past Due + NSCD'!C3),0,0,-90,1)``
.

mattdavid.hall

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

Replies
2
Views
185
Replies
7
Views
496
Replies
10
Views
582
Replies
1
Views
125
Replies
0
Views
292

1,127,528
Messages
5,625,334
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back