Create named absolute reference to a cell in a table?

[macfuller]

I have a date table of financial indexes. Example of the S&P below (with some funky xl2bb remnant of the formula in there)...

S&P 500						S&P ±%				Date
4,401.67						]-$E$16)/$E$16|nf:0.0%]0.0%				14-Feb-22
4,384.65						]-$E$16)/$E$16|nf:0.0%]-0.4%				26-Feb-22
4,328.87						]-$E$16)/$E$16|nf:0.0%]-1.7%				5-Mar-22
4,575.52						]-$E$16)/$E$16|nf:0.0%]3.9%				28-Mar-22
4,392.59						]-$E$16)/$E$16|nf:0.0%]-0.2%				16-Apr-22
4,271.78						]-$E$16)/$E$16|nf:0.0%]-3.0%				23-Apr-22
4,123.34						]-$E$16)/$E$16|nf:0.0%]-6.3%				6-May-22
3,926.76						]-$E$16)/$E$16|nf:0.0%]-10.8%				18-May-22

The +/- column shows the change in the S&P from the first date I started tracking, in this case Feb 14 2022. The cell E16 has the value on that date, and subsequent cells take the S&P value on that date and track the variance using
=([@[S&P 500]]-$E$16)/$E$16

So is there a way to refer to that cell by some consistent name for formulas outside that table, or within the table if for some reason I chose to sort the table by another column than date? I have to use MINIFS or AGGREGATE functions to look up the earliest date and then XLOOKUP to get the S&P value for that date. If I'd like to base my +/- on some other date (e.g. the first tracking date of the current year) or add a column I have to modify the formulas in every row. It sure would be handy if I could have an absolute reference for it!

​

​

 

[Eric W]

Not really! Excel tables have a lot of advantages, but they have some drawbacks too. A big one is that there's no good way to reference any row other than the one you're on (with @). There are some tricks you can do, like:

Book3
	E						K				O	P	Q						W				AA
15	S&P 500						S&P ±%				Date		S&P 500						S&P ±%				Date
16	4401.67						0				2/14/2022		3926.76						-0.10789314				5/18/2022
17	4384.65						-0.003867				2/26/2022		4123.34						-0.06323282				5/6/2022
18	4328.87						-0.016539				3/5/2022		4271.78						-0.02950925				4/23/2022
19	4575.52						0.0394964				3/28/2022		4392.59						-0.00206285				4/16/2022
20	4392.59						-0.002063				4/16/2022		4575.52						0.039496373				3/28/2022
21	4271.78						-0.029509				4/23/2022		4328.87						-0.01653918				3/5/2022
22	4123.34						-0.063233				5/6/2022		4384.65						-0.00386671				2/26/2022
23	3926.76						-0.107893				5/18/2022		4401.67						0				2/14/2022
Sheet5

Cell Formulas
Range	Formula
K16:K23	K16	=LET(v,INDEX([S&P 500],1),([@[S&P 500]]-v)/v)
W16:W23	W16	=LET(v,INDEX([S&P 500],MATCH(MIN([Date]),[Date],0)),([@[S&P 500]]-v)/v)

LET will let you define your value just once, which will shorten the formula. And you can use INDEX([column],1) to get the first row. Or even INDEX([column],ROWS([column])) to get the last column. It only gets trickier from there.

 

[Alex Blakenburg]

I would not see that as an issue related to using tables. A standard range will suffer exactly the same issue if you reference a row inside the sort range and then sort the range.
Also I would argue that the information will become almost meaningless after the sort when it is no longer obvious what your point of reference is.

Wouldn't you be better off making it much clearer and simpler eg

Book2
	E	F	G
12	Earliest Date	14/02/2022
13	Earliest Value	4401.67
14
15	S&P 500	S&P ±%	Date
16	4401.67	0	14/02/2022
17	4384.65	-0.00386671	26/02/2022
18	4328.87	-0.01653918	5/03/2022
19	4575.52	0.03949637	28/03/2022
20	4392.59	-0.00206285	16/04/2022
21	4271.78	-0.02950925	23/04/2022
22	4123.34	-0.06323282	6/05/2022
23	3926.76	-0.10789314	18/05/2022
Sheet2

Cell Formulas
Range	Formula
F12	F12	=MIN(Table1[Date])
F13	F13	=XLOOKUP($F$12,Table1[Date],Table1[S&P 500])
F16:F23	F16	=([@[S&P 500]]-$F$13)/$F$13

 

[macfuller]

Two helpful answers - thank you. Hard to choose which to mark as the answer, but I think Eric made the point first that there just ain't such a feature in Excel.

I track multiple equity indices, so that's currently 5 reference points to maintain under Alex's recommendation. That solution is effective but were I to add additional indices as well as Treasuries and FX exchange rates the redundancy of extracting reference values from 10+ columns bothers me .

Still, electrons are cheap (in the words of the great Chandoo)...