Interesting "trick" to get around a table problem

[JenniferMurphy]

I came across a problem I often have with tables, or maybe it's the way I use tables. I often want to start a table with a constant and then perform some calculation, like adding 1, in each subsequent row, like this:

If I create the table with the constants in Row 1, then add the expressions in Row 2, they work, but they do not propagate. I have to manually drag the expressions down.

Medical Logs.xlsx
	F	G
3	Date	Number
4	6/01/24	1
5	6/02/24	2
6	6/03/24	3
7
8
Sheet2

Cell Formulas
Range	Formula
F5:G6	F5	=F4+1

I just discovered a trick that gets around this. It I create the first 2 rows before converting it to a table, it works. Here it as a range:

Medical Logs.xlsx
	C	D
3	Date	Number
4	6/01/24	1
5	6/02/24	2
Sheet2

Cell Formulas
Range	Formula
C5:D5	C5	=C4+1

Here it is after converting it to a table:

Medical Logs.xlsx
	C	D
3	Date	Number
4	6/01/24	1
5	6/02/24	2
Sheet2

Cell Formulas
Range	Formula
C5:D5	C5	=C4+1

And here it is after I just tabbed to 3 new rows. They propagated automatically.

Medical Logs.xlsx
	C	D
3	Date	Number
4	6/01/24	1
5	6/02/24	2
6	6/03/24	3
7	6/04/24	4
8	6/05/24	5
Sheet2

Cell Formulas
Range	Formula
C5:D8	C5	=C4+1

Any explanation why one works and the other doesn't?

 

[Alex Blakenburg]

An excel table tries to work out what formula to use as the default for the rows.
By entering the constant first you are telling it that the default for the row is a constant.
Simply enter the formula first (even if it errors out) then overwrite the formula in row 1 and enter your constant.

If you have already set it up the other way around, just copy the the formula to all rows in the table then overwrite the formula in row 1 with the constant.

Having said that its not exactly best practice. Have you considered using something like the below so that you have the same formula in all rows.

20240715 Table ListObject Incrementing a value in a row JenniferMurphy.xlsx
	B	C
4	Date	Number
5	1-Jun-24	1
6	2-Jun-24	2
7	3-Jun-24	3
8	4-Jun-24	4
9	5-Jun-24	5
Test

Cell Formulas
Range	Formula
B5:B9	B5	=DATE(2024,6,0)+ROW()-ROW(Table1[[#Headers],[Date]])
C5:C9	C5	=ROW()-ROW(Table1[[#Headers],[Number]])

 

[JenniferMurphy]

An excel table tries to work out what formula to use as the default for the rows.

IMHO, it could do a much better job of that. But that would require a better design.

Simply enter the formula first (even if it errors out) then overwrite the formula in row 1 and enter your constant.

Now that's a brilliant solution that is probably obvious to everyone else.

If you have already set it up the other way around, just copy the the formula to all rows in the table then overwrite the formula in row 1 with the constant.

Another great suggestion.

Having said that its not exactly best practice. Have you considered using something like the below so that you have the same formula in all rows.

Nope, but I'll study it.

Thanks

PS: Re tables, I have so many suggestions. One is multiple totals rows. That one comes up all the time. Another is starter or helper rows above the table. That would solve this problem and a lot of others.

 

[Alex Blakenburg]

I doubt that anyone would find those quirks obvious