Formula references changing when adding a row to a table.

[drewberts]

I have a table set up on a sheet called "Input" so that data comes in automatically from a form app via zapier - this works well. One row is one set of form data and represents one month work of info and is auto added by creating a new row in the table automatically.

In a separate sheet called "Data" I pull that data into a table which is laid out as columns (one row in the input sheet is one column in the data sheet). Each column is a month and I have the year set out in advance (so 12 columns laid out) and each column references back to the row in question e.g. column A has all the data from row A in the input sheet.

The problem is when a new row gets added to the input sheet the next blank column formula references (in the data sheet) all change by one row value and don't reference the correct row anymore in the input sheet. I know and understand why it does this but that's not what I want to achieve as I have to manually change the formulas each month and I'm looking for the info to be automatic.

Both sets of data need to be tables as the info is used elsewhere in charts etc.

I guess the question I'm asking is how to get around this problem so that the references stay correct - I hope I've explained that so it makes sense.

 

[Peter_SSs]

Perhaps you could use XL2BB to show us some (small) dummy sample data from each sheet, what formulas you currently have and explain again in relation to that specific sample data. You could also use XL2BB to show us what results you want as opposed to what results you currently have.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[drewberts]

Perhaps you could use XL2BB to show us some (small) dummy sample data from each sheet, what formulas you currently have and explain again in relation to that specific sample data. You could also use XL2BB to show us what results you want as opposed to what results you currently have.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Thanks Peter - I'll give it a go.

 

[drewberts]

Things have moved on a bit so here goes with a better explanation.

Here is the table which auto populates by adding a row each time a 3rd party form is submitted (using Zapier) - this works well.

Monthly KPI Sheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K
8	Month End	Cut Off Date	Date Submitted	Tender Amount	Tenders Won	Tender Enquiries	Tenders Sent	Tenders Won No	Average Tender Return Time	Client Retention	Tender Hrs
9	Sep 2024	18/09/2024	24/09/2024	£ 200,000	£ 100,000	35	20	15	3	25	20.0
10	Oct 2024	18/09/2024	24/09/2024	£ 180,000	£ 120,000	50	50	25	4	35	40.0
11	Nov 2024	18/09/2024	24/09/2024	£ 300,000	£ 150,000	85	60	25	5	50	60.0
Sales

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:K4,D9:K11	Cell Value	<0	text	NO
A9:K11	Cell	contains a blank value	text	NO

Here is the table that puts the data from the first table into columns

Monthly KPI Sheet.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4	Item	Type	Previous	Sep 24	Oct 24	Nov 24	Dec 24	Jan 25	Feb 25	Mar 25	Apr 25	May 25	Jun 25	Jul 25	Aug 25	Average	Total
5	Tender Amount	£	-	£ 200,000	£ 180,000	£ 300,000										£ 226,667	£ 680,000
6	Tenders Won	£	-	£ 100,000	£ 120,000	£ 150,000										£ 123,333	£ 370,000
7	Tender Enquiries	Num	-	35	50	85										57	170
8	Tenders Sent	Num	-	20	50	60										43	130
9	Tenders Won No	Num	-	15	25	25										22	65
10	Tender Submit Average	Days	-	3	4	5										4	12
11	Client Retention	Num	-	25	35	50										37	110
12	Tender Hrs	Num	-	20	40	60										40	120
KPI

Cell Formulas
Range	Formula
F5	F5	=IF(Sales!$D$9="","",Sales!$D$9)
G5	G5	=IF(Sales!$D$10="","",Sales!$D$10)
H5	H5	=IF(Sales!$D$11="","",Sales!$D$11)
I5	I5	=IF(Sales!$D$12="","",Sales!$D$12)
J5	J5	=IF(Sales!$D$13="","",Sales!$D$13)
K5	K5	=IF(Sales!$D$14="","",Sales!$D$14)
L5	L5	=IF(Sales!$D$15="","",Sales!$D$15)
M5	M5	=IF(Sales!$D$16="","",Sales!$D$16)
N5	N5	=IF(Sales!$D$17="","",Sales!$D$17)
O5	O5	=IF(Sales!$D$18="","",Sales!$D$18)
P5	P5	=IF(Sales!$D$19="","",Sales!$D$19)
Q5	Q5	=IF(Sales!$D$20="","",Sales!$D$20)
R5:R12	R5	=AVERAGEIF(tabKPI2[@[Sep 24]:[Aug 25]], ">0", tabKPI2[@[Sep 24]:[Aug 25]])
S5:S12	S5	=SUMIF(tabKPI2[@[Sep 24]:[Aug 25]], ">0", tabKPI2[@[Sep 24]:[Aug 25]])
F6	F6	=IF(Sales!$E$9="","",Sales!$E$9)
G6	G6	=IF(Sales!$E$10="","",Sales!$E$10)
H6	H6	=IF(Sales!$E$11="","",Sales!$E$11)
I6	I6	=IF(Sales!$E$12="","",Sales!$E$12)
J6	J6	=IF(Sales!$E$13="","",Sales!$E$13)
K6	K6	=IF(Sales!$E$14="","",Sales!$E$14)
L6	L6	=IF(Sales!$E$15="","",Sales!$E$15)
M6	M6	=IF(Sales!$E$16="","",Sales!$E$16)
N6	N6	=IF(Sales!$E$17="","",Sales!$E$17)
O6	O6	=IF(Sales!E$18="","",Sales!E$18)
P6	P6	=IF(Sales!F$19="","",Sales!F$19)
Q6	Q6	=IF(Sales!G$20="","",Sales!G$20)
F7	F7	=IF(Sales!$F$9="","",Sales!$F$9)
G7	G7	=IF(Sales!$F$10="","",Sales!$F$10)
H7	H7	=IF(Sales!$F$11="","",Sales!$F$11)
I7	I7	=IF(Sales!$F$12="","",Sales!$F$12)
J7	J7	=IF(Sales!$F$13="","",Sales!$F$13)
K7	K7	=IF(Sales!$F$14="","",Sales!$F$14)
L7	L7	=IF(Sales!$F$15="","",Sales!$F$15)
M7	M7	=IF(Sales!$F$16="","",Sales!$F$16)
N7	N7	=IF(Sales!$F$17="","",Sales!$F$17)
O7	O7	=IF(Sales!F$18="","",Sales!F$18)
P7	P7	=IF(Sales!G$19="","",Sales!G$19)
Q7	Q7	=IF(Sales!H$20="","",Sales!H$20)
F8	F8	=IF(Sales!$G$9="","",Sales!$G$9)
G8	G8	=IF(Sales!$G$10="","",Sales!$G$10)
H8	H8	=IF(Sales!$G$11="","",Sales!$G$11)
I8	I8	=IF(Sales!$G$12="","",Sales!$G$12)
J8	J8	=IF(Sales!$G$13="","",Sales!$G$13)
K8	K8	=IF(Sales!$G$14="","",Sales!$G$14)
L8	L8	=IF(Sales!$G$15="","",Sales!$G$15)
M8	M8	=IF(Sales!$G$16="","",Sales!$G$16)
N8	N8	=IF(Sales!$G$17="","",Sales!$G$17)
O8	O8	=IF(Sales!G$18="","",Sales!G$18)
P8	P8	=IF(Sales!H$19="","",Sales!H$19)
Q8	Q8	=IF(Sales!I$20="","",Sales!I$20)
F9	F9	=IF(Sales!$H$9="","",Sales!$H$9)
G9	G9	=IF(Sales!$H$10="","",Sales!$H$10)
H9	H9	=IF(Sales!$H$11="","",Sales!$H$11)
I9	I9	=IF(Sales!$H$12="","",Sales!$H$12)
J9	J9	=IF(Sales!$H$13="","",Sales!$H$13)
K9	K9	=IF(Sales!$H$14="","",Sales!$H$14)
L9	L9	=IF(Sales!$H$15="","",Sales!$H$15)
M9	M9	=IF(Sales!$H$16="","",Sales!$H$16)
N9	N9	=IF(Sales!$H$17="","",Sales!$H$17)
O9	O9	=IF(Sales!H$18="","",Sales!H$18)
P9	P9	=IF(Sales!I$19="","",Sales!I$19)
Q9	Q9	=IF(Sales!J$20="","",Sales!J$20)
F10	F10	=IF(Sales!$I$9="","",Sales!$I$9)
G10	G10	=IF(Sales!$I$10="","",Sales!$I$10)
H10	H10	=IF(Sales!$I$11="","",Sales!$I$11)
I10	I10	=IF(Sales!$I$12="","",Sales!$I$12)
J10	J10	=IF(Sales!$I$13="","",Sales!$I$13)
K10	K10	=IF(Sales!$I$14="","",Sales!$I$14)
L10	L10	=IF(Sales!$I$15="","",Sales!$I$15)
M10	M10	=IF(Sales!$I$16="","",Sales!$I$16)
N10	N10	=IF(Sales!$I$17="","",Sales!$I$17)
O10	O10	=IF(Sales!I$18="","",Sales!I$18)
P10	P10	=IF(Sales!J$19="","",Sales!J$19)
Q10	Q10	=IF(Sales!K$20="","",Sales!K$20)
F11	F11	=IF(Sales!$J$9="","",Sales!$J$9)
G11	G11	=IF(Sales!$J$10="","",Sales!$J$10)
H11	H11	=IF(Sales!$J$11="","",Sales!$J$11)
I11	I11	=IF(Sales!$J$12="","",Sales!$J$12)
J11	J11	=IF(Sales!$J$13="","",Sales!$J$13)
K11	K11	=IF(Sales!$J$14="","",Sales!$J$14)
L11	L11	=IF(Sales!$J$15="","",Sales!$J$15)
M11	M11	=IF(Sales!$J$16="","",Sales!$J$16)
N11	N11	=IF(Sales!$J$17="","",Sales!$J$17)
O11	O11	=IF(Sales!J$18="","",Sales!J$18)
P11	P11	=IF(Sales!K$19="","",Sales!K$19)
Q11	Q11	=IF(Sales!L$20="","",Sales!L$20)
F12	F12	=IF(Sales!$K$9="","",Sales!$K$9)
G12	G12	=IF(Sales!$K$10="","",Sales!$K$10)
H12	H12	=IF(Sales!$K$11="","",Sales!$K$11)
I12	I12	=IF(Sales!$K$12="","",Sales!$K$12)
J12	J12	=IF(Sales!$K$13="","",Sales!$K$13)
K12	K12	=IF(Sales!$K$14="","",Sales!$K$14)
L12	L12	=IF(Sales!$K$15="","",Sales!$K$15)
M12	M12	=IF(Sales!$K$16="","",Sales!$K$16)
N12	N12	=IF(Sales!$K$17="","",Sales!$K$17)
O12	O12	=IF(Sales!K$18="","",Sales!K$18)
P12	P12	=IF(Sales!L$19="","",Sales!L$19)
Q12	Q12	=IF(Sales!M$20="","",Sales!M$20)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Cell Value	duplicates	text	NO

You'll see that the 1st cell in the Dec 24 column (I5) in the 2nd sheet references row 12 in the first sheet (which is the next row). The issue is that when a new row is auto added to the first table all the formulas in the I column get pushed across to the J column in the 2nd table (J, becomes K etc).

How do I maintain the reference in the correct columns in table 2 when auto adding a row to table 1?

 

[Peter_SSs]

Thanks for the XL2BB samples. Try this in the table on the KPI sheet in a copy of your workbook

1. Remove all the formulas from the "month" columns
2. Put this formula in F5 =IFERROR(INDEX(table1,COLUMNS($F5:F5),3+ROW()-ROW(tabKPI2[#Headers])),"") (check both table names in this formula). BTW, the 3 in this formula is because we are ignoring the first 3 columns of the table in 'Sales' when extracting the required data.
3. The required formulas/values should spill down column F
4. Select F5 and copy/drag that formula across the first row of all the other 'month' columns.
5. Select G5:Q5 and Copy/Paste over itself. The other column formulas should now all spill down the table columns.
6. Now try adding new rows in the 'Sales' table

BTW, you shouldn't need AVERAGEIF or SUMIF in columns R & S on 'KPI' as shown below

drewberts.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4	Item	Type	Previous	Sep 24	Oct 24	Nov 24	Dec 24	Jan 25	Feb 25	Mar 25	Apr 25	May 25	Jun 25	Jul 25	Aug 25	Average	Total
5	Tender Amount	£	-	200000	180000	300000										226,667	680,000
6	Tenders Won	£	-	100000	120000	150000										123,333	370,000
7	Tender Enquiries	Num	-	35	50	85										57	170
8	Tenders Sent	Num	-	20	50	60										43	130
9	Tenders Won No	Num	-	15	25	25										22	65
10	Tender Submit Average	Days	-	3	4	5										4	12
11	Client Retention	Num	-	25	35	50										37	110
12	Tender Hrs	Num	-	20	40	60										40	120
KPI

Cell Formulas
Range	Formula
F5:Q12	F5	=IFERROR(INDEX(table1,COLUMNS($F5:F5),3+ROW()-ROW(tabKPI2[#Headers])),"")
R5:R12	R5	=AVERAGE(tabKPI2[@[Sep 24]:[Aug 25]])
S5:S12	S5	=SUM(tabKPI2[@[Sep 24]:[Aug 25]])

 

[drewberts]

Thanks Peter - that's excellent and works perfectly with the info I supplied, however...

After Row 12 in the tblKPI the next 6 rows pull data from a different table (tblMKPI), then rows 19-27 pull data from tblOKPI and then rows 28-39 from tblFKPI. I've amended the table reference in the formula but just get blank calcs - I'm not sure why this is. My formulas are :-

Rows 5-12 =IFERROR(INDEX(tabSKPI,COLUMNS($F5:F5),3+ROW()-ROW(tabKPI[#Headers])),"") etc - this works fine
Rows 13-18 =IFERROR(INDEX(tabMKPI,COLUMNS($F5:F5),3+ROW()-ROW(tabKPI[#Headers])),"") etc - #REF
Rows 19-27 =IFERROR(INDEX(tabOKPI,COLUMNS($F5:F5),3+ROW()-ROW(tabKPI[#Headers])),"") etc - #REF
Rows 28-39 =IFERROR(INDEX(tabFKPI,COLUMNS($F5:F5),3+ROW()-ROW(tabKPI[#Headers])),"") etc - #REF

I have it set up like this as I have 4 different sheets (& tables) each of which receives data from a 3rd party form and tblKPI is bringing all the data into one sheet.

What am I missing here (I've only posted the first few columns of data so you get the idea)?

Monthly KPI Sheet.xlsm
	C	D	E	F	G	H	I
5	Tender Amount	£	-	£ 179,910	£ 172,095	£ 201,540	£ 50,590
6	Tenders Won	£	-	£ 121,367	£ 31,935	£ 132,220	£ 14,350
7	Tender Enquiries	Num	-	45	68	58	18
8	Tenders Sent	Num	-	45	43	39	8
9	Tenders Won No	Num	-	28	32	21	3
10	Tender Submit Average	Days	-	5	5	4	6
11	Client Retention	Num	-	34	33	48	13
12	Tender Hrs	Num	-	-	-	-	-
13	LinkedIn Followers	Num	-	#REF!	#REF!	#REF!	#REF!
14	Instagram Followers	Num	-	#REF!	#REF!	#REF!	#REF!
15	Website Visitors	Num	-	#REF!	#REF!	#REF!	#REF!
16	Client Feedback	%	-	#REF!	#REF!	#REF!	#REF!
17	Marketing Hrs	Num	-	#REF!	#REF!	#REF!	#REF!
18	BD Hrs	Num	-	#REF!	#REF!	#REF!	#REF!
19	On Time Delivery	%	-	#REF!	#REF!	#REF!	#REF!
20	Available Hours	Num	-	#REF!	#REF!	#REF!	#REF!
21	Billed Hours	Num	-	#REF!	#REF!	#REF!	#REF!
22	QA Errors No	Num	-	#REF!	#REF!	#REF!	#REF!
23	Hrs Estimate	Num	-	#REF!	#REF!	#REF!	#REF!
24	Hrs Actual	Num	-	#REF!	#REF!	#REF!	#REF!
25	Management Hrs	Num	-	#REF!	#REF!	#REF!	#REF!
26	Quoting Hrs	Num	-	#REF!	#REF!	#REF!	#REF!
27	Job Hrs	Num	-	#REF!	#REF!	#REF!	#REF!
KPI

Cell Formulas
Range	Formula
F5:I12	F5	=IFERROR(INDEX(tabSKPI,COLUMNS($F5:F5),3+ROW()-ROW(tabKPI[#Headers])),"")
F13:I18	F13	=INDEX(tabMKPI,COLUMNS($F13:F13),3+ROW()-ROW(tabKPI[#Headers]))
F19:I27	F19	=INDEX(tabOKPI,COLUMNS($F19:F19),3+ROW()-ROW(tabKPI[#Headers]))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Cell Value	duplicates	text	NO

 

[Peter_SSs]

After Row 12 in the tblKPI the next 6 rows pull data from a different table (tblMKPI), then rows 19-27 pull data from tblOKPI and then rows 28-39 from tblFKPI.

Hmm, that is a much more complicated scenario!!

In my previous post values were being extracted from table1 (tabSKPI) for you I think) from 8 columns: 4,5,6,7,8,9,10,11
What are the 6 table column numbers to get data from in tabMKPI?
What are the 9 table column numbers to get data from in tabOKPI?
What are the 12 table column numbers to get data from in tabFKPI?

I've amended the table reference in the formula but just get blank calcs - I'm not sure why this is.

Because the formula will be looking for rows in the other tables that do not exist.

 

[drewberts]

Thanks Peter for persevering. Apologies for adding in a complication, I just assumed I could rename the table names (you know what assumption does...)

I was thinking of simply having 4 sheets (1 for each set of info) and then pulling that into a single sheet - I appreciate that this isn't particularly efficient and probably not a great way forward but could probably work.

That said if you're happy to suggest a tweak to see if we can get it to work, here's the info you need...

In my previous post values were being extracted from table1 (tabSKPI) for you I think) from 8 columns: 4,5,6,7,8,9,10,11 - this is correct, columns D-K
What are the 6 table column numbers to get data from in tabMKPI? For this table it's 6 columns (4,5,6,7,8,9 or D-I) staring at row 9 which is first row of the table
What are the 9 table column numbers to get data from in tabOKPI? For this table it's 9 columns (4,5,6,7,8,9,10,11,12 or D-L) staring at row 9 which is first row of the table
What are the 12 table column numbers to get data from in tabFKPI? For this table it's 13 columns (4,5,6,7,8,9,10,11,12,13,14,15,16 or D-P) staring at row 9 which is first row of the table

Thanks again

 

[Peter_SSs]

Thanks for the additional detail. Try this.
Proceed as per the numbered steps in post #5 except that at step 2 use this formula in F5

=LET(r,ROW()-ROW(tabKPI2[#Headers]),tblNo,MATCH(r,{1,9,15,24}),IFERROR(INDEX(CHOOSE(tblNo,tabSKPI,tabMKPI,tabOKPI,tabFKPI),COLUMNS($F5:F5),3+r-CHOOSE(tblNo,0,8,14,23)),""))