Cell Reference

[Herman_KLF]

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.

 

[Stefi]

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

Regards,
Stefi

„Herman_KLF†ezt írta:

 

[Herman_KLF]

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

 

[Stefi]

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


„Herman_KLF†ezt írta:

 

[Luke M]

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