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?

 

[Fluff]

You would need to use something like

=SUM(INDEX(@Values,):D$12)

If you use the evaluate formula button the formula tab, you can see why your formula doesn't work.

 

[ISY]

Equivalent formula

=SUM((@D:D):D$12)

 

[JenniferMurphy]

You would need to use something like

=SUM(INDEX(@Values,):D$12)

If you use the evaluate formula button the formula tab, you can see why your formula doesn't work.

OK, here are the results of Evaluate on the formula in F10. Now I have even more questions:

Apparently, the SUM function doesn't care how many cell references are separated by colons:

Weird.

So apparently, the solution to my problem is: "=sum((@Values):D$12). This is what you explained, but without the extra comma. Why did you add that?

 

[JenniferMurphy]

Equivalent formula

=SUM((@D:D):D$12)

I guess that works, I didn't try it, but it doesn't use my named range, which is the whole point.

 

[Fluff]

Because the implicit intersection works on a range, xl has to decide what that range is.
So first the named range is replaced by the actual range, then the union is done & finally the intersect is found.

By using index, or putting brackets around the @Values the intersect is calculated first & then the union is done, because it's treated as a separate calculation.

 

[JenniferMurphy]

Because the implicit intersection works on a range, xl has to decide what that range is.
So first the named range is replaced by the actual range, then the union is done & finally the intersect is found.

By using index, or putting brackets around the @Values the intersect is calculated first & then the union is done, because it's treated as a separate calculation.

This makes no sense to me. "@Values" should be an intact unit (or identity) equal to "D10" (or "$D$10") in this case. That is what should be done first, IMNSHO.

 

[Fluff]

It has never worked that way & in my opinion shouldn't.
If the named range was a single cell & it worked the way you want it to, then there would be problems.

+Fluff 1.xlsm
	D	E	F
7
8	1	#SPILL!	1
9	2	#SPILL!	2
10	3	#SPILL!	3
11	4	#SPILL!	4
12	5	1	5
13		2
14		3
15		4
16		5
Main

Cell Formulas
Range	Formula
E8:E16	E8	=(@Values):D$12
F8:F12	F8	=Values:D$12

Named Ranges
Name	Refers To	Cells
Values	=Main!$D$8:$D$8	F8:F12, E8:E16

 

[JenniferMurphy]

It has never worked that way & in my opinion shouldn't.
If the named range was a single cell & it worked the way you want it to, then there would be problems.

I'm afraid you are now over my head. I don't understand the point you are making.

 

[Fluff]

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.