Inserting Rows and the affect on Functions
 

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!

Inserting Rows and the affect on Functions
 

Mike O. <Mike wrote...
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.

Inserting Rows and the affect on Functions
 

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

"Harlan Grove" wrote:

Mike O. <Mike wrote...
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.

Inserting Rows and the affect on Functions
 

?B?TWlrZSBPLg==?= <Mike wrote in
:

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())))