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
6
  • share a copy of your sheet
    – user0
    Jul 18, 2019 at 17:13
  • 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
    Jul 18, 2019 at 19:40
  • sheet is private
    – user0
    Jul 18, 2019 at 19:41
  • fixed, you can edit now. Thank you!
    – Yasmeen Elladen
    Jul 18, 2019 at 19:43
  • sheet is still private
    – user0
    Jul 18, 2019 at 19:46

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
8
  • when I use these, it starts referring to the current sheet instead of the summary sheet.
    – Yasmeen Elladen
    Jul 18, 2019 at 19:42
  • @YasmeenElladen answer updated. try now
    – user0
    Jul 18, 2019 at 19:54
  • Thanks! Unfortunately, it didn't work, I should have mentioned that I have other data underneath it, hence why I need to add rows :( Thank you for trying
    – Yasmeen Elladen
    Jul 18, 2019 at 19:58
  • @YasmeenElladen I see. in that case try 2nd fx from updated answer
    – user0
    Jul 18, 2019 at 20:02
  • 1
    @YasmeenElladen you are welcome and free to not accept the answer if it helped you
    – user0
    Jul 18, 2019 at 21:09

Your Answer

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

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