At (@) notation inconsistent?

[JenniferMurphy]

I have been using the @ notation a lot since it was suggested to me here. It has made a lot of things easier.

I just encountered a situation where it does not appear to. Can anyone help me understand what is going on?.

In this table, I assigned the name "Values" to the range D8:D12, the literal values). When I tried access that range using the @ notation (@Values), which I thought would resolve to the value ij the cell in that range that is on the same row. It works in some situations, but not in others.

In Column E, I use @Values to get the member of the named range that is on the same row. The formulas are shown in Column H. It works perfectly.

Columns F & G should return the same values, but they don't. In F, I use @Values, which fails. In G, I use a cell reference. It works.

What is going on? What am I doing wrong?

 

[JenniferMurphy]

If Values was a single cell (in this case D8) & xl evaluate @VALUE before anything else, then using this formula =@Values:D$12 would create a spill range, rather than using the implicit intersection.

But Values is not a single cell. And even if it were, then I would expect a error if the formula referencing it was not on the same row.

I don't really understand spill. I guess I need to study it more.