Dynamic Sheet Referencing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob Diamant - 29 Jun 2007 23:06 GMT

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

Reply to this Message

Earl Kiosterud - 29 Jun 2007 23:27 GMT

Rob,

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

=INDIRECT("'"&A2&"'!"&"A1")

Signature

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

>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
[quoted text clipped - 5 lines]
>
> Rob

Reply to this Message

Rob Diamant - 02 Jul 2007 14:28 GMT

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,
>
[quoted text clipped - 13 lines]
>>
>> Rob

Reply to this Message

Earl Kiosterud - 02 Jul 2007 18:45 GMT

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.

Signature

Earl Kiosterud
www.smokeylake.com

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

> Thanks, it took a little extra effort but got it working
>
[quoted text clipped - 18 lines]
>>>
>>> Rob

Reply to this Message