Autofill query

[shangar]

Hello

I wonder if you can help with this, I suspect, rather basic query.

My scenario is as follows -
At the top of my spreadsheet I have a list of totals, these are based in B2 down to B20.
These totals are linked to G23 for B2, down to G41 for B20. To link these I enter = in the formula bar & then click on the relevant cell.
I then insert a new set of rows, moving G23 down to G44, G24 down to G45, etc
Cell B2 is then updated to the following by adding a + to the end of the formula & then clicking on the relevant cell –
=R[42]C[5]+R[21]C[5]
Autofill is used to update B3 down to B20

Occasionally, I need to insert additional rows, these are not used for referencing the totals. So, G23 remains the target of B2. G25 then becomes the target of B3. G27 then becomes the target of B4. Down to G59 for B20.

At this point if I update the formula for B2, I then find that I can no longer use Autofill as this wishes to reference G24 for B3 instead of G25. I can update this fairly painlessly, although manually, but when I come to insert new rows, moving everything down my spreadsheet, Autofill cannot cater for the set of cells which have the additional lines in them.

I hope the above makes sense, please excuse my lack of Excel skills. Thanks for reading, and thanks in advance for any assistance.

[kev_]

I assume that you are inserting rows to put something in another column.
Would deleting the empty cells in the column where you want the autofill sort your problem?
Select the cells to be deleted, then RightClick \ Delete \

this pops up..

DeleteRelevantcells.jpg

change the default to:
Shift cells UP

and then looks like this...

DeleteRelevantCellsAFTER.jpg

and autofill works as expected on cells A1 to A11

[shangar]

Yes, the additional rows are being included for more descriptive text. Unfortunatley I need the empty cells where they are...that's given me an idea, I could merge the cells, or at worst resize them to contain all the required data, as a result this would keep the important cells, referenced by the totals, contiguous.

Thanks for your reply. I shall trial these additional thoughts that I've had, prompted by your suggestion.

[kev_]

I do not know hpw you are using the inserted data.
Is it necessary to insert additional rows?
Are you aware of the {Alt}{Enter} trick to get multiple lines of text into one cell?

Alt enter.jpg

[shangar]

The penny dropped that this was an alternatiive for me after your initial post. I suspect that I do not need something complex, just to adjust layout to achieve my goal.

Thanks for your assistance with this.