Offset Function use in Range

[zalik22]

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!

 

[DRSteele]

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.

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	relevant range	D5:M5
4
5	indirect	#VALUE!	6	6	4	32	25	2	0	3	36	99	0
6	summation	213

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula C5 =INDIRECT(C3) C6 =SUM(INDIRECT(C3)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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>

 

[zalik22]

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.