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!

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



  • or like this:


    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


  • when I use these, it starts referring to the current sheet instead of the summary sheet. – Yasmeen Elladen Jul 18 '19 at 19:42
  • @YasmeenElladen answer updated. try now – user0 Jul 18 '19 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 '19 at 19:58
  • @YasmeenElladen I see. in that case try 2nd fx from updated answer – user0 Jul 18 '19 at 20:02
  • thank you! that worked! how about for C2 in the summary sheet? – Yasmeen Elladen Jul 18 '19 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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