INDIRECT or OFFSET - can't make them work

mattdavid.hall

New Member
Joined
Oct 2, 2008
Messages
11
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?
 

Some videos you may like

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
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Solution

mattdavid.hall

New Member
Joined
Oct 2, 2008
Messages
11
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
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What is the value of the cell with the last row number in it?
 

mattdavid.hall

New Member
Joined
Oct 2, 2008
Messages
11
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)))

So I adjusted your code earlier to be
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
Joined
Oct 2, 2008
Messages
11
Just found my issue. I was placing just C3 instead of $C$3. Once I did that it seems to work. Testing further.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top