2

I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the formula doesn't update so I would have to update the formula each time in the summary sheet. Is there any way to fix this?

This is my formula now: =indirect(""&A33&"H44") where A33 is referencing the sheet name and H44 is what I want to be updated when I add a row in the sheet being referenced. I hope that makes sense!

Improve this question
1
  • docs.google.com/spreadsheets/d/… This is an example, Lets say I add a row above row 4 in the S5 sheet, I need the formula in the summary sheet to update from =INDIRECT("'"&A2&"'!B4") tp =INDIRECT("'"&A2&"'!B5"). Thanks
    – Yasmeen Elladen
    Commented Jul 18, 2019 at 19:40

1 Answer 1

Reset to default
0

  • try like this:

    =SUM(INDIRECT(A2&"!B5:B"))

    0

  • or like this:

    =SUM(INDIRECT(A2&"!B5:B"&ROW('S5'!B7)))

    note: inserted row needs to be added before the last value in the range. eg. with this formula you will need to add row between B6 & B7 so the formula would rewrite itself to B8. if you add it after B7 the formula won't extend

    0

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.