Dynamic Sheet Referencing

[Rob Diamant]

I have a workbook that has a sheet for each week. I am looking for a way
(formula) that will allow me to create a summary sheet with Column A storing
a valid Sheet Name and Column B DSUM'ing the totals from the sheet listed in
Column A.

Of course I can do this manually each week as I add a new sheet, but what
fun is that.

Any suggestions would be greatly appreciated.

Rob

 

[Earl Kiosterud]

Rob,

If the sheet name is in A2, and the total in the named sheet is in A1, you can use:

=INDIRECT("'"&A2&"'!"&"A1")
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Rob Diamant]

Thanks, it took a little extra effort but got it working

Column A contains a date that corresponds to each Friday of the year.

=DSUM(INDIRECT("'[E0107991.XLS]"&TEXT(A17,"yyyymmdd")&"'!A5"):INDIRECT("'[E0107991.XLS]"&TEXT(A17,"yyyymmdd")&"'!H50"),"Hours",$A$1:$F$2)

Rob

 

[Earl Kiosterud]

Rob,

I tried to take your formula apart, but such a formula is full if hieroglyphics, and I kept
falling out of my chair. But I'm glad you got it working.
--
Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------

Thanks, it took a little extra effort but got it working

Column A contains a date that corresponds to each Friday of the year.

=DSUM(INDIRECT("'[E0107991.XLS]"&TEXT(A17,"yyyymmdd")&"'!A5"):INDIRECT("'[E0107991.XLS]"&TEXT(A17,"yyyymmdd")&"'!H50"),"Hours",$A$1:$F$2)

Rob

Rob,

If the sheet name is in A2, and the total in the named sheet is in A1, you can use:

=INDIRECT("'"&A2&"'!"&"A1")
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...