formula issue

[AlienBeans]

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]

if you place this in column A and copy across to column B, etc you will
get the value from cell L52 on sheet week1, then week2, etc. Does not
appear to require the sum function.

=INDIRECT("week"&COLUMN()&"!L52",TRUE)

 

[AlienBeans]

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)

 

[duane]

in column C and copied through column P

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

will result in =week1!L52, =week2!L52, etc

not sure why you want to use the SUM function for just one cell

 

[AlienBeans]

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?

 

[duane]

the formula will only pick up entries in cell L52 so double check the
data you entered.

 

[AlienBeans]

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

 

[Biff]

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