Cell Reference
 

1st - let me describe my workbook: My first worksheet contains a table that
is a summary of the other worksheets. Let's call it "Summary" worksheet. The
other worksheets contains monthly manhours worked during the past 12 months.
Every worksheet contains the manhours of an office in a different city.
In the Summary table i have the reference to the other worksheets as E.g.

=SUM(Belfast!B20)

where Belfast is the worksheet name.

This works fine, but at the end of the month I have to take out the top line
and add one line at the bottom for the new month, thus every record move one
level up. I.o.w. March is now at the bottom (in B20) and after monthsend
March would be in B19. my problem is after deleting and inserting rows my
formula looks like this "=SUM(Belfast!B19)" and i have to go and change it
back to B20 by hand and I'm sure there must be a better way of accomplishing
this.

Cell Reference
 

Try to use absolute row reference like this:
=SUM(Belfast!B$19)

Regards,
Stefi

€žHerman_KLF€� ezt Ã*rta:

1st - let me describe my workbook: My first worksheet contains a table that
is a summary of the other worksheets. Let's call it "Summary" worksheet. The
other worksheets contains monthly manhours worked during the past 12 months.
Every worksheet contains the manhours of an office in a different city.
In the Summary table i have the reference to the other worksheets as E.g.

=SUM(Belfast!B20)

where Belfast is the worksheet name.

This works fine, but at the end of the month I have to take out the top line
and add one line at the bottom for the new month, thus every record move one
level up. I.o.w. March is now at the bottom (in B20) and after monthsend
March would be in B19. my problem is after deleting and inserting rows my
formula looks like this "=SUM(Belfast!B19)" and i have to go and change it
back to B20 by hand and I'm sure there must be a better way of accomplishing
this.

Cell Reference
 

Tried that Stefi, didn't work.
Sorry, i should have said that, but that is as far as i got.


"Stefi" wrote:

Try to use absolute row reference like this:
=SUM(Belfast!B$19)

Regards,
Stefi

€žHerman_KLF€� ezt Ã*rta:

1st - let me describe my workbook: My first worksheet contains a table that
is a summary of the other worksheets. Let's call it "Summary" worksheet. The
other worksheets contains monthly manhours worked during the past 12 months.
Every worksheet contains the manhours of an office in a different city.
In the Summary table i have the reference to the other worksheets as E.g.

=SUM(Belfast!B20)

where Belfast is the worksheet name.

This works fine, but at the end of the month I have to take out the top line
and add one line at the bottom for the new month, thus every record move one
level up. I.o.w. March is now at the bottom (in B20) and after monthsend
March would be in B19. my problem is after deleting and inserting rows my
formula looks like this "=SUM(Belfast!B19)" and i have to go and change it
back to B20 by hand and I'm sure there must be a better way of accomplishing
this.

Cell Reference
 

Try
=INDIRECT("Belfast!B"&20)
Using Sum for a single cell has no reason!
Stefi


€žHerman_KLF€� ezt Ã*rta:

Tried that Stefi, didn't work.
Sorry, i should have said that, but that is as far as i got.


"Stefi" wrote:

Try to use absolute row reference like this:
=SUM(Belfast!B$19)

Regards,
Stefi

€žHerman_KLF€� ezt Ã*rta:

1st - let me describe my workbook: My first worksheet contains a table that
is a summary of the other worksheets. Let's call it "Summary" worksheet. The
other worksheets contains monthly manhours worked during the past 12 months.
Every worksheet contains the manhours of an office in a different city.
In the Summary table i have the reference to the other worksheets as E.g.

=SUM(Belfast!B20)

where Belfast is the worksheet name.

This works fine, but at the end of the month I have to take out the top line
and add one line at the bottom for the new month, thus every record move one
level up. I.o.w. March is now at the bottom (in B20) and after monthsend
March would be in B19. my problem is after deleting and inserting rows my
formula looks like this "=SUM(Belfast!B19)" and i have to go and change it
back to B20 by hand and I'm sure there must be a better way of accomplishing
this.

Cell Reference
 

=INDIRECT("Belfast!B20")

If you want to be able to copy this cell (within the same sheet) and have it
adjust row & column, could change to:
=INDIRECT("Belfast!"&COLUMN(B1)&ROW(A20),FALSE)


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Herman_KLF" wrote:

Tried that Stefi, didn't work.
Sorry, i should have said that, but that is as far as i got.


"Stefi" wrote:

Try to use absolute row reference like this:
=SUM(Belfast!B$19)

Regards,
Stefi

€žHerman_KLF€� ezt Ã*rta:

1st - let me describe my workbook: My first worksheet contains a table that
is a summary of the other worksheets. Let's call it "Summary" worksheet. The
other worksheets contains monthly manhours worked during the past 12 months.
Every worksheet contains the manhours of an office in a different city.
In the Summary table i have the reference to the other worksheets as E.g.

=SUM(Belfast!B20)

where Belfast is the worksheet name.

This works fine, but at the end of the month I have to take out the top line
and add one line at the bottom for the new month, thus every record move one
level up. I.o.w. March is now at the bottom (in B20) and after monthsend
March would be in B19. my problem is after deleting and inserting rows my
formula looks like this "=SUM(Belfast!B19)" and i have to go and change it
back to B20 by hand and I'm sure there must be a better way of accomplishing
this.