Correct cell reference when inserting new line

[GormO]

I have the following table:

	A	B	C
1​		Temperature	Change in temperature since last day
2​	Monday	20​	0​
3​	Tuesday	22​	=B3-B2​
4​	Wednesday	21​	=B4-B3​

I wish to insert a new line into my table. I do the following inputs:

1. Select row 4
2. CTRL C
3. CTRL +
4. Change the value of what is now cell A5 to "Thursday" and the temperature of B5 to "19".

My formula in cell C5 is now =C6-C4. Not =C6-C5 as I would expect.

Is there a way to ensure that formula in cell C5 will be =C6-C5, if I use the exact inputs as described above in step 1-4? Note that I am not interested in changing how I give my inputs

Cheers

 

[Joe4]

Welcome to the Board!

That is because you are inserting the new row in the middle of your data, which changes your formula references (row 4 is moved down to row 5 and the new data is inserted into row 4).
Do it like this and it will maintain your formulas:
1. Select row 4
2. CTRL C
3. Moved down one row (to row 5)
4. CTRL +
5. Change the value of what is now cell A5 to "Thursday" and the temperature of B5 to "19".

 

[GormO]

The problem is that in reality I am working with a filtered table. It contains sensitive data so I cannot share it directly, but imagine something like "Week 2", "Week 3", "Week 4" etc. In the view I posted in the original post I would then have filtered for "Week 1". If I move down one row and insert, I will insert into the beginning of week 2, rather than in the middle of week 1. Hope that makes sense.

 

[Joe4]

The problem is that in reality I am working with a filtered table. It contains sensitive data so I cannot share it directly, but imagine something like "Week 2", "Week 3", "Week 4" etc. In the view I posted in the original post I would then have filtered for "Week 1". If I move down one row and insert, I will insert into the beginning of week 2, rather than in the middle of week 1. Hope that makes sense.

Are you really working with actual tables, or just a list of tables?
As Excel tables typically use column name references in formulas (not formulas like =B4-B3), which is why I assumed that you are not actually working with an actual Table.

Note that when you go to insert the record, it may ask how you want to move the data, and if you elect to shift rows down, I think it should work the way you want.
If it does not, please post an actual data example that is not working, so I can try to recreate it here.

 

[GormO]

I'm not allowed to run XL2BB due to workplace restrictions unfortunately, but copy this table into excel:

Year	Month	Company	Volume	Change in volume since last month
2022​	1​	Corp1	10​	-
2022​	2​	Corp1	9​	-1​
2022​	3​	Corp1	11​	2​
2022​	4​	Corp1	4​	-7​
2022​	5​	Corp1	7​	3​
2022​	6​	Corp1	11​	4​
2022​	7​	Corp1	11​	0​
2022​	8​	Corp1	12​	1​
2022​	1​	Corp2	4​	-
2022​	2​	Corp2	4​	0​
2022​	3​	Corp2	3​	-1​
2022​	4​	Corp2	4​	1​
2022​	5​	Corp2	7​	3​
2022​	6​	Corp2	4​	-3​
2022​	7​	Corp2	2​	-2​
2022​	8​	Corp2	1​	-1​
2022​	1​	Corp3	1​	-
2022​	2​	Corp3	1​	0​
2022​	3​	Corp3	1​	0​
2022​	4​	Corp3	0​	-1​
2022​	5​	Corp3	1​	1​
2022​	6​	Corp3	1​	0​
2022​	7​	Corp3	4​	3​
2022​	8​	Corp3	1​	-3​

- The "Change in volume since last month" should be a function =[CurrentCell]-[CurrentCell-1].
- Add filters, sort by corp2
- Attempt to add a new line for month 9 for corp2, while the filter is active. My actual database has over 100 corps in it, so this is necessary.

 

[Joe4]

OK, inserting rows into a table that has formulas that reference rows other than itself (i.e. row above it), is going to be problematic, as you are creating a new gap.
I think you choices are to write your formula differently, such as using the OFFSET function, as shown here: Return values from the row above to the current row, or possibly use VBA.

If it were me, I think I might choose to use VBA and create a whole process/button for inserting rows, which they would select where they want to insert the row, then run the macro, which would insert the row and then fix up the formulas for those new rows.

 

[GormO]

Could you help me with an example of using OFFSET in the above case? I'm having a little trouble wrapping my head around the function syntax

 

[Alex Blakenburg]

This should do the same thing as the Offset without actually using Offset (which is volatile)

Book2
	A	B	C	D	E
1	Year	Month	Company	Volume	Change in volume since last month
2	2022	1	Corp1	10	10
3	2022	2	Corp1	9	-1
4	2022	3	Corp1	11	2
5	2022	4	Corp1	4	-7
6	2022	5	Corp1	7	3
7	2022	6	Corp1	11	4
8	2022	7	Corp1	11	0
Sheet2

Cell Formulas
Range	Formula
E2:E8	E2	=INDEX(D$1:D2,ROW(D2),0)-N(INDEX(D$1:D2,ROW(D2)-1,0))

 

[GormO]

Using index works beautifully. Thank you!

 

[Alex Blakenburg]

No problem. Glad we could help.