Gfrktyl

I'm not getting the expected reference
result in the following Scenario

Can someone please explain this.

Create a workbook with 2 x WorkSheets , Sheet1 & Sheet2

Populate Sheet2 Col A1 Down with the following

"Header","Row2","Row3","Row4","Row5","Row6","Row7","Row8","Row9"

Create a Named Range "Header" LOCAL to Sheet2 on Referring to Sheet2!A1

Populate Sheet1 Col A1 Down to Row9 inclusive with the following

=OFFSET(INDIRECT("Sheet2"&"!Header"),0,0,COUNTA(INDIRECT("Sheet2"&"!$A:$A")),1)

Populate Sheet1 B2 Down to B9 inclusive with the following

="Row is " & ROW(OFFSET(INDIRECT("Sheet2"&"!$Header"),0,0,COUNTA(INDIRECT("Sheet2"&"!$A:$A")),1))

The Result I expected

The Result I got

Given that no relative references are used and the "Correct/Expected" row reference is returned by the formula in Sheet1 Col B yet get an unexpected result in Sheet1 Col A; what's going on.

PS
I have to make data validation on one sheet referring to dynamic local references on other sheets work and need to understand the results above to make this fly.

The OFFSET function returns a range. With the arguments you use, it is a 9 x 1 range (height=9 rows, width=1 column) referring to A1:A9 from Sheet2.

The behavior of ranges is sometimes surprising, as you have seen. It differs with the way you use the range.

• When you apply the ROW function to a range, it returns the number of the top row of the range - regardless of the place from where you're using the function. This is the repeated Row is 1 you got in column B.


• But when you refer to the contents of the range - as you do in your formula in column A of Sheet1 - the location where you make the reference matters. Excel will return the contents of the cell within the range that is at the same height as the cell from where you're using the reference.

This is also the case with other references to ranges. For example, if you put

=Sheet2!A:A

in cell D4 on Sheet1, it will link to cell A4 of Sheet2: the one that is at the same height within the range to which you linked, containing Row4. Or if you create a named range Alldata for the cell block A1:A9, then in cell F7

=Alldata

will give you a reference to A7, containing Row7.

Thus, if you only wanted to refer to the cell at the top, you can make it a cell reference instead of a range (that is, a 1x1 range):

=OFFSET(INDIRECT("Sheet2"&"!Header"),0,0,1,1)

or completely leave out the height and width dimensions:

=OFFSET(INDIRECT("Sheet2"&"!Header"),0,0)

or just link to the cell:

=INDIRECT("Sheet2"&"!Header")