Absolute cell references when range changes

[travellerva]

I have a table of dates and values and several formulae that operate on the data in the table. When I insert a new row in the table, I want the formulas to retain the absolute cell addresses. I can't use RC format as the workbook is extensive and uses the common A1 reference format elsewhere. This is not a VBA question but rather a referencing question for formulas in the worksheet.

Thanks for any help.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have a table ..

A formal Excel table (ListObject created via Ctrl+T or Insert ribbon tab -> Table) or just a normal worksheet range that you are referring to as a table?

.. and several formulae that operate on the data in the table.

Could we have examples of those formulas?

Better, could we have an XL2BB mini sheet with sample table and formulas and explain again in relation to the mini sheet?

 

[travellerva]

Thank you for your reply. It does not seem possible to load XL2BB in my Excel for Mac (365 V16.59). There is no File>Options; it is in Insert>Add-ins. When there, there is no Browse option so locating the XL2BB.xlsm file is not possible. Double-clicking the XL2BB.xlsm file results in no visible action and XL2BB does not appear in the My Add-Ins list.

An example of the formula I'm using is:
=(SUMPRODUCT($B$8:$G$8,$K$4:$P$4)/SUMPRODUCT($B$30:$G$30,$K$4:$P$4))-1

After inserting a row into RC 8, the formula becomes:
=(SUMPRODUCT($B$9:$G$9,$K$4:$P$4)/SUMPRODUCT($B$31:$G$31,$K$4:$P$4))-1

I need the formula to remain as B8:G8 etc. and B30:G30 etc.

I've tried using relative references such as $B8:$G8 but the same result occurs after the row insert.

Does that help explain it better?

Thanks.

 

[mohadin]

Hi
Try

=(SUMPRODUCT(indirect("B8:G8"),$K$4:$P$4)/SUMPRODUCT(indirect("B30:G30"),$K$4:$P$4))-1

 

[travellerva]

This is the table I referred to: Formula is in B1

	0.018​
											24.3725​	168.72​	19.3204​	14.344​	0​	0.532​
	5/25/23	97.49	414.61	174.20	71.44	1​	26.05
	5/24/23	97.85	411.16	175.54	71.54	1​	26.06
	5/23/23	98.10	414.13	177.48	72.60	1​	26.62
	5/22/23	97.99	418.76	178.26	73.69	1​	26.88
	5/19/23	98.08	418.58	176.06	73.66	1​	26.71
	5/18/23	98.30	419.23	177.22	73.23	1​	26.76
	5/17/23	98.76	415.15	176.22	73.34	1​	26.90
	5/16/23	98.91	410.26	172.27	73.00	1​	26.54
	5/15/23	99.14	412.93	174.86	73.70	1​	27.22
	5/12/23	99.39	411.62	172.72	73.14	1​	27.25
	5/11/23	99.95	412.16	173.03	73.27	1​	27.26
	5/10/23	99.65	412.80	174.41	73.45	1​	27.56
	5/9/23	99.01	410.93	173.53	73.60	1​	27.29
	5/8/23	99.10	412.80	174.06	73.90	1​	27.42
	5/5/23	99.65	412.56	174.45	73.88	1​	27.58
	5/4/23	99.97	405.21	170.44	72.81	1​	27.13
	5/3/23	100.09	407.93	172.38	72.96	1​	27.01
	5/2/23	99.65	410.86	171.68	72.76	1​	27.48
	5/1/23	98.69	415.53	175.35	73.51	1​	27.48
	4/28/23	99.96	415.93	175.20	73.62	1​	27.70
	4/27/23	99.45	412.41	173.68	73.65	1​	27.35
	4/26/23	99.81	404.34	171.39	72.74	1​	26.74
	4/25/23	100.18	406.17	173.08	72.79	1​	27.00

 

[travellerva]

=(SUMPRODUCT(indirect("B8:G8"),$K$4:$P$4)/SUMPRODUCT(indirect("B30:G30"),$K$4:$P$4))-1

@mohadin: Perfect! That works. Many thanks.

 

[Peter_SSs]

It does not seem possible to load XL2BB in my Excel for Mac (365 V16.59). There is no File>Options;

XL2BB does work with Excel 365 for Mac. I don't use a Mac, but according to this page, instead of File>Options try Tools>Excel Add-ins
Also, please update your Account details as requested above to show 365 for Mac as your version.

You also did not address my question about formal table v normal range, but see if this works. I would avoid the volatile function INDIRECT as an option if possible. I think that this non-volatile option should do the same job for you.

=(SUMPRODUCT(INDEX(B:G,8,0),$K$4:$P$4)/SUMPRODUCT(INDEX(B:G,30,0),$K$4:$P$4))-1