Inserting Rows and the affect on Functions

[Mike O.]

Hi,

I have a workbook with 3 tabs. The third tab uses the SUM function to sum
the values in tabs 1 and 2. It's set up so that the cell A3 in tab 3 looks
to cell A3 in tabs 1 and 2.

I need to add a row in all the tabs but the SUM function isn't recognizing
that I've added a row. It still looks at A3 when I need it to look at A4.

I have many such SUM functions to update and am looking for a better way
than manually changing the SUM functions.

Any ideas? Thanks in advance for your help!

 

[Harlan Grove]

I have a workbook with 3 tabs.  The third tab uses the SUM function to sum
the values in tabs 1 and 2.  It's set up so that the cell A3 in tab 3 looks
to cell A3 in tabs 1 and 2.

I need to add a row in all the tabs but the SUM function isn't recognizing
that I've added a row.  It still looks at A3 when I need it to look at A4.

....

What's the actual formula you're using in tab 3? If your formula is
=SUM('tab 1:tab 2'!A3), are you selecting all tabs, thereby grouping
them, then inserting a row above row 3 in all tabs in a single
operation? If not, that's your problem.

 

[Mike O.]

That's it! I was not selecting all tabs when I inserted the row. Thanks so
much for your help!

 

[Liliana]

Hi,

I have a workbook with 3 tabs. The third tab uses the SUM function to
sum the values in tabs 1 and 2. It's set up so that the cell A3 in
tab 3 looks to cell A3 in tabs 1 and 2.

I need to add a row in all the tabs but the SUM function isn't
recognizing that I've added a row. It still looks at A3 when I need
it to look at A4.

I have many such SUM functions to update and am looking for a better
way than manually changing the SUM functions.

Any ideas? Thanks in advance for your help!

Instead of inserting a line at your formula you could insert a line within
the range to be summed. Alternatively, this will sum column A to the cell
immediately above:

=SUM(A1:INDIRECT(ADDRESS(ROW()-1,COLUMN())))