-1

I have two spreadsheets:

A budget sheet, with estimated costs and actual costs. Replicated here: https://docs.google.com/spreadsheets/d/1J8wPicVyfs98QXRXRCHZoq-Y6Uoi75Iv8WitGja4Pic/edit#gid=0

A cost tracking sheet, with the actual costs and reference to receipts. Replicated here: https://docs.google.com/spreadsheets/d/1YclB3V3817Q-RGwuzrOXoZkarwFszaTqwUFurtYtLVo/edit#gid=0

Currently I am importing the 'actual costs' in the budget sheet, by referencing rows from the 'actual costs' in the tracking sheet

So my budget sheet references the cost tracking sheet this way: =(importrange("spreadsheet_key","sheet_name!I76"))

This works fine, however, if I insert a new row into my cost tracking sheet above some of the values I am already referencing, it will not dynamically update.

So if I insert a new row above: =(importrange("spreadsheet_key","sheet_name!I76"))

I then have to manually change it in the other sheet too for each item: =(importrange("spreadsheet_key","sheet_name!I77"))

Is anyone aware of a way I can link two sheets and still be able to insert rows without this breaking?

Thanks

Kim Bear
  • 11
  • 3

1 Answers1

0

Sounds like you want a sheet that updates when new values are entered into a separate sheet? I think a short script similar to the one proposed in this question should work for you.

Detect user inserting row or column in a google spreadsheet and reacting in a script

Erik Lewis
  • 1
  • 1
  • Thanks! I will be trying out some of these solutions. Essentially, the cost tracking sheet is ordered by date. The budget is ordered by categories of type of purchase. The budget includes estimated costs and pulls the actual costs from the cost tracking sheet. So when a purchased has been made, it is logged in the cost tracking by month and then that information needs to be fed into the budget sheet, to see how on track we are with our budget. – Kim Bear May 25 '18 at 08:33
  • I've tried playing with some of the scripts from the other post. My spreadsheets are huge and it takes a while to run. I'm not very experienced with Google script at the moment. I've put the script on the budget spreadsheet and I am trying to figure out how to get it to look for an edit from the cost tracking spreadsheet – Kim Bear Jun 01 '18 at 22:58