auto update formula if rows inserted

In my "example" I have references in D2:E11 which take data from B19:U19. Is it possible to automatically update these formulae if a row is inserted eg above my row 20? The formula in D2 should then change from =OFFSET(B20,-1,0) to =OFFSET(B21,-1,0) and so on. I would like this to occur wherever I insert a row. TIA

Originally Posted by ktchegi

In my "example" I have references in D2:E11 which take data from B19:U19. Is it possible to automatically update these formulae if a row is inserted eg above my row 20? The formula in D2 should then change from =OFFSET(B20,-1,0) to =OFFSET(B21,-1,0) and so on. I would like this to occur wherever I insert a row. TIA

You appear to have neglected to mention that the Offset points to a different sheet, or to mention where the row is being inserted.

Instead of

=OFFSET(input!P20,-1,0)

try setting the Total (A20) to a Named range, and then use

=OFFSET(TOTAL2,-1,15)

etc

added,

however, the 'Offset' should self-adjust as with any Relative formula.

---

Thank you Bryan. In my original post I did mention "wherever I insert a row". The change you made "input!P20" works a treat. All I need is to apply this to literally any row I insert. My actual worksheet needs to be dynamic ie I have to insert the "input blocks" between existing ones, but the "results" sheet remains in it's position - just gets updated with new data. (I have also posted similar at http://www.pcreview.co.uk/forums/thread-3050059.php)

Apologies for bending the rules, but this member is rather desperate to get answers. Point to note - the "other" forum actually asks that posts to other forums be linked:confused

Originally Posted by ktchegi

Thank you Bryan. In my original post I did mention "wherever I insert a row". The change you made "input!P20" works a treat. All I need is to apply this to literally any row I insert. My actual worksheet needs to be dynamic ie I have to insert the "input blocks" between existing ones, but the "results" sheet remains in it's position - just gets updated with new data. (I have also posted similar at http://www.pcreview.co.uk/forums/thread-3050059.php)

Yes, hence the reason for Nameing the Total (A20) cell for the block and using that as a reference point rather than use (say) P20.
As you insert / delete rows the word Total will remain your reference, the define name Total2 mentioned solely because A20 held the second 'total' on the page.

the name for each Total would need to be something meaningful for the block, or just Total1, Total1A, Total1B, Total2 etc.

You would then, of course, need to account for your newly inserted block in the Results sheet.

hth
---

many thanks for assisting me in this. I think this forum is tops