calling a cell value from another worksheet

[papadoc]

This may have been the wrong way to go about doing this project but i
is the way I have taken anyway..........

I have 52 consectuive sheets named (week 1 - week 52) on each sheet i
column A starting with cell A4 is a list of names which will vary wee
to week. i.e. (this week (ws sheet 1) has 54 total names next (ws shee
2) has 60 total names.....when a new name is added to the list an entir
column is inserted for data manipulation for a 7 day work week bein
columns B thru H. At the bottom of the list of names are 3 rows wit
forumlas....

Total This Week (totals the total number of people working for tha
day)
Total Last Week (takes the total of previous week from previous shee
and displays it on current sheet)
Difference (Takes difference of two numbers)

Total This Week .... formula works fine

Total Last Week.... does not..... ='week1!B54' (this works just fin
if the list never grows... because I insert an entire row when a nam
is added to the list it shifts the formulas down on the page but th
place where the formula is looking is no longer there so I need a wa
to set up where the formula for Total Last Week will go to the row o
the last name on the list and go down 1 row to obtain the proper ro
and cell location of the information..

I am totaly stumped on this one so any help would be greatfull......

thanks in advanc

 

[Excelenator]

How about naming the ranges you want to add? NAme the cell that
contains "Total This Week" on all sheets "TWTotal" (this week total or
anything that will be desriptive to you) then simply use the formula

+Week1!TWTotal

When you insert or delete rows the total should still pull the right
data.

This may have been the wrong way to go about doing this project but it
is the way I have taken anyway..........

I have 52 consectuive sheets named (week 1 - week 52) on each sheet in
column A starting with cell A4 is a list of names which will vary week
to week. i.e. (this week (ws sheet 1) has 54 total names next (ws sheet
2) has 60 total names.....when a new name is added to the list an entire
column is inserted for data manipulation for a 7 day work week being
columns B thru H. At the bottom of the list of names are 3 rows with
forumlas....

Total This Week (totals the total number of people working for that
day)
Total Last Week (takes the total of previous week from previous sheet
and displays it on current sheet)
Difference (Takes difference of two numbers)

Total This Week .... formula works fine

Total Last Week.... does not..... ='week1!B54' (this works just fine
if the list never grows... because I insert an entire row when a name
is added to the list it shifts the formulas down on the page but the
place where the formula is looking is no longer there so I need a way
to set up where the formula for Total Last Week will go to the row of
the last name on the list and go down 1 row to obtain the proper row
and cell location of the information..

I am totaly stumped on this one so any help would be greatfull......

thanks in advance