Dynamic Sheet Referencing

  • Thread starter Thread starter Rob Diamant
  • Start date Start date
R

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
 
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...
 
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,

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

-----------------------------------------------------------------------
Rob Diamant said:
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 said:
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...
Click to expand...
Click to expand...
 
Back
Top