formula issue

I have a formula that I'm using to pull data from a cell on multiple sheets...The formula is =SUM(Week1!$L$52) This formula I want to use across the same row, changing the week number in each cell, but not the cell reference...In other words...the cell next to it would be =SUM(Week2!$L$52), next one would be week 3, and so on. Is there a shortcut to doing this so that It will update across the row all the way to 14?? I have 60 rows of formulas, and it would be easier to just enter each one ONE time, and copy and paste somehow across the sheet but keep the formula reference correct.

duane,

thanks for the reply. I'm not going across columns here. I'm taking the formula across a row.... Like say ROW 22...my formula starts with column C and goes across to column P. The only thing I want it to change is the week number in each of the 14 columns. but don't want to have to type it out 14 times for each row..(60 rows)

Well, I'm not very well versed in excel at all. I did a little searching in the help file in excel and its the only thing I could come up with to give me what I wanted. I tried your formula and went in and entered some data on week 1, but the total didn't transfer to the weekly totals sheet. any ideas?

Works!!!! Thank You Very Much...

If you enter the formula like this:

=INDIRECT("week"&COLUMNS($A:A)&"!L52")

Then you're not dependent on which column the formula is entered in and
don't need to calculate an offset:

=INDIRECT("week"&COLUMN()-2&"!L52")

If the above formula were entered in cell Z1 then it would evaluate to:

=Week24!L52

The first formula will evaluate to:

=Week1!L52

no matter what cell it's entered in.

Biff

"AlienBeans" <[email protected]> wrote
in message news:[email protected]...
>
> Works!!!! Thank You Very Much...
>
>
> --
> AlienBeans
> ------------------------------------------------------------------------
> AlienBeans's Profile:
> http://www.excelforum.com/member.php...o&userid=32352
> View this thread: http://www.excelforum.com/showthread...hreadid=546580
>