How to maintain range when adding new rows

[didierkassas]

hello everyone

I am facing some issues on a document that I created for work.

some of my staff are adding rows at the bottom and it is causing the formulas to break.

any idea how i can make sure that the formula still pics up the data even if the row is added at the bottom?

regards

[Pepe Le Mokko]

Have you tried using absolute references to define your range ?

[AliGW]

Convert your data ranges to tables.

[didierkassas]

i did but is does not sort the issue out

if a range is B15:B20 and someone adds a row in B21, B21 does not get added to the range.

i need the formula to change to B15:B21

[Glenn Kennedy]

It SHOULD do that, but success may depend on other formulae in the table. Please reads the yellow banner (top) and post a sample showing the problem.

[nick.williams]

If you don't want to convert your data ranges to tables, can you set the range to include blank cells at the bottom (either the whole column, or if not feasible then many rows below where it is likely to have data entered)?