Formula to auto update cells below when inserting a new row

Hi, I'm sure this must be simple but cant work it out:

I have a formula that fills down in the usual way referring to data in another sheet i.e 'Sheet1' A1 =Sheet2!A1 & 'Sheet1' A2 =Sheet2!A2 etc. Now if I insert a new row somewhere in the middle is it possible to make the formula update without having to drag the formula to refill the whole spreadsheet? Ideally by just updating the inserted row would have a knock on effect to the formulas in the rows below.

For example: *Note I've simplified things, but this is the principle of what I want to do - dragging the formula to update all the cells isn't going to work for me.*

Sheet1 Rows A1:A30 are picking up data from Sheet2 A1:A30.
I insert a new row on Sheet1 at A16 and drag the formula to update A16 from above so that A16=Sheet2!A16.
Now Sheet1 A17 also =Sheet2!A16 as it has retained it's previous formula, I want this to update automatically to Sheet1 A17=Sheet2!A17 and so on for the rows below.

Maybe there's a setting that enables formulas to update on being moved?

Thanks for any help!

Hi onpoint,

welcome to the forum.

Please change the thread title to reflect the query you are asking.. see the forum rules:-

http://www.excelforum.com/forum-rule...rum-rules.html


Regards,
DILIPandey
<click on below * if this helps>

A1: =INDIRECT("Sheet2!" &CELL("address",A1))

Copy down and across. You'll still need to copy the formula to the new row but the rows below will automatically adjust.


Regards, TMS

@dilipandey: apologies for responding over your request for a title change. Busy checking the solution and didn't refresh the thread.

@onpoint: please amend your thread title as requested.

Regards, TMS

I'm a little confused. The formula I provided is for use in sheet1 and allows you to insert a row in sheet1 and it uses the cell reference on sheet1.

If you insert rows in sheet2, the cell references in sheet1 will automatically update.

I'm not sure that you can do what you want with a simple link and I suspect that it will quickly get out of sync.

If the data that you have has a key field, a unique reference, then it would be better to use VLOOKUP or INDEX/MATCH to maintain the links between the two sheets.

Regards, TMS

I'm sorry, I can't think of any way of doing that. Not to say it can't be done, I just don't have any idea how to do it.

Why do you want to do it? Can you post a sample workbook with some examples of what you are trying to achieve.

Regards, TMS

I suspect that you will need VBA for this.

Regards, TMS

near as I can figure this would be the closest non vba way. it uses a helper column showing the row number you want and then a lot of INDEX formulas. the row number cells (except the first one) are calculated using =INDIRECT("R[-1]C",0)+1 so they will always be one more than the row immediately above