Offset Function use in Range

zalik22

Board Regular
Joined
Dec 14, 2010
Messages
88
Hi,

I have a cell reference that displays a data table
='Base Data'!U4:AJ5

The data table in U4 to AJ5 is dynamic (but always ends in row 5) so was wondering if I can somehow offset the "AJ5" part of the formula. I want the "AJ5" part to change based on the last nonzero value in the row.

Thanks in advance!
 
Last edited:

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,330
Office Version
  1. 365
Platform
  1. Windows
I don't know if this is exactly what you're looking for, but I will suggest it anyway.

The C3 array-formula reckons the text of the relevant range in my reference range (that being D5:Q5), which is D5 to M5 (M5 being the most-right column that has no zero). The C5 formula uses that text as the argument REF in function INDIRECT; note that this argument cannot have the C3 formula pasted into it because C3 contains an array, which INDIRECT chokes on - so C3 will just have to reside on the sheet somewhere as a helper-formula. The C6 formula is proof that the algorithm works, because it correctly reports the sum of the range C5:Q5 as being 213.

BCDEFGHIJKLMNOPQ
3relevant rangeD5:M5
4
5indirect#VALUE!664322520336990
6summation213

<tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
C5=INDIRECT(C3)
C6=SUM(INDIRECT(C3))

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
C3{=ADDRESS(ROW(D5),COLUMN(D5),4)&":"&SUBSTITUTE(ADDRESS(ROW(D5),MAX(COLUMN(D5:Q5)*(D5:Q5<>0)),4),"1","")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>
 
Last edited:

zalik22

Board Regular
Joined
Dec 14, 2010
Messages
88
Hi,

Your formula worked to get the dynamic range, noted in green in your example. I have a picture that references the table currently like "=U5:AJ5" and this works, so I am trying to get the table referenced in the picture to automatically update. Any way to do this with your formula? When I reference an indirect cell, I am getting an error. Thanks, should have been more descriptive in the initial post.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,774
Members
414,018
Latest member
quang118

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