How to check if offset is referencing the correct range?

[wiseone]

Hi,

I have a complicated Offset which is supposed to reference a row of 52 columns based on a reference cell, which will then be used for other calculations.

After i type in the offset in my formula, is there a way to check that it is referencing the correct range?

One method I know would be to make a named range of the offset, then check the location of the named range.. However its a formula that gets copied down to reference other key cells to change the offset, so that would only work for 1 row, and I'd like to check that its working on all rows instead of just one. (every row will have a different offset range).

Any other ideas?

I am aware offset is a volatile function and the con's associated.

Thanks in advance!

 

[John_w]

Try =CELL("address",YOUR_COMPLICATED_OFFSET_FORMULA).

See https://exceljet.net/formula/get-address-of-lookup-result.

 

[wiseone]

Try =CELL("address",YOUR_COMPLICATED_OFFSET_FORMULA).

See https://exceljet.net/formula/get-address-of-lookup-result.

This worked perfectly! thanks much!

 

[wiseone]

This worked perfectly! thanks much!

I spoke too soon...this formula only returns the first cell (top-left) of the range....any other simple ways?

I found this online, but its not simple: https://exceljet.net/formula/<wbr>address-of-last-cell-in-range

 

[Jonmo1]

You could copy your offset formula, and paste it into the Name Box (to the left of the formula bar) and press enter.

 

[wiseone]

yup, i mentioned that up front...making a named range....however, if I have a formula where the offset changes for every cell it's dragged into, I'd have to have a named range of every cell which is not practical.

 

[Jonmo1]

I didn't say make a named range.

Copy the actual offset formula.
Paste it into the Name Box
Press Enter
It will highlight the area created by the offset.