Excel: Reference a formula in a cell from another worksheet where rows are added

0
votes

help is appreciated on this topic!

  • I am trying to reference cell B10 from Sheet2 to cell A1 in Sheet1.
  • Cell B10 contains the formula SUM(B1:OFFSET(B10,-1,0)), which sums values from B1:B9.
  • Daily, I add a new row to Sheet2 such that the Column B range increases by +1, from B1:B9 to B1:B10, and the formula from B10 moves to B11.
  • I now need cell A1 in Sheet1 to reference cell B11 in Sheet2.

I've tried using offset() and indirect() but haven't figured this out.

Thanks for help!

1
excelexcel-formulaexcel-2016vba

1 Answers

0
votes

There are (at least) two ways to handle this:

1) Every time you insert the row, make sure you place your cursor on the formula cell before manually inserting row. If you do, the formula in Sheet1!A1 will automatically follow suit, and you can add whatever you need in the new cell (created from the new row). For example, inserting a row at B10 will adjust the formula in A1 to say =Sheet2!B11.

2) Realizing that point 1 may not be suitable to your process, place this formula in Sheet1A1: =OFFSET(Sheet2!B1,COUNT(Sheet2!B1:B100)-1,0)
You can adjust the B100 to whatever you need. Just be sure there's nothing in between the the final formula cell and the last row in the this formula range.