linking and updating formulas when inserting copied worksheets

[cheeko]

Hi,

Can anyone help with the following?

I have, for instance, a cell (A1) on sheet 1 which draws the SUM of data values from sheet 2 within a certain row (row 3) (=SUM(Sheet2!3:3)). This row 3 in sheet 2 is the SUM of cells a1:a2, b1:b2, c1:c2, etc (=SUM(A1:A2)).

Everything works fine as I add values to cells a1:a2, b1:b2, c1:c2, etc in sheet 2 as this updates row 3 (=SUM(A1:A2)) and thus the cell A1 in sheet 1 updates.

However I would like to make a copy of sheet 2 in the same workbook as sheet 3 for example but now have the formula in A1 of sheet 1 to automatically update this copied sheet 3, rather doing it manually as there's many sheets I would be adding.

i.e. automatically cell A1 in sheet 1 from (=SUM(Sheet2!3:3)) to (=SUM(Sheet2!3:3)+SUM(Sheet3!3:3)).

I hope this makes sense. Is this possible? Thanks

eg1.png
eg2.png
eg3.png
eg4.png

[Pete_UK]

Set up two blank sheets, one called start and the other called end, and position these either side of Sheet2 but with Sheet1 outside of the start and end range. Then you can copy Sheet2 and position the new sheet also within the range defined by start and end, and then you can use this formula in A1 of Sheet1:

=SUM('start:end'!3:3)

it will sum row 3 from any sheet within (and including) the sheets start and end.

Hope this helps.

Pete

[cheeko]

I've never seen this 'start:end' formula in excel before, If it was possible in excel I had a feeling it would be something simple like this.

Yes it worked. Thanks Pete, you're a legend!

[Pete_UK]

Glad to be able to help. You can easily set up what-if scenarios by dragging sheets into or out of that "sandwich" formed by the start and end sheets.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[cheeko]

Ya I've tried a few different, albeit simple equations, for now but the result is what I'm looking for. I can and will tailor it to my needs but as long as I can simply add copy sheets without adjusting formulas afterwards this will help and do me justice for my application.

[cheeko]

Hi guys,

I want to take this ('start:end') function Pete gave me to use a little further. Pete's original suggestion worked well but what I want to do now is this:

sheet1.png
sheet3.png
sheet4.png
worksheets.png

I want a given cell in sheet1 to produce the sum of factors within a given column/row arrangements and equations from sheet3 and sheet4.

I've tried all the arguments I can think of in their proper places including the ' arguments before and after start:end but nothing seems to work. My resulting answer is usually #REF!

Can suggestions and help?

Thanks,
cheeko