reference to sheets

[ann]

Hello,

I have five sheets and they are named by weeks (ex: 5-7-2006 to 5-13-2006, 5-14-2006 to 5-20-2006, etc.). In another sheet, Weekly table, I need to put these sheet names in the first row. Basically, whatever name you give to the five sheets, it should automatically update the first row of the Weekly table sheet.

I tried to do thru macro: Go to the first sheet, right click and select Rename and select Copy and paste the name of the sheet in the first row (A1); Go to the second sheet, right click and select Rename and select Copy and paste the second sheet name in A2. If I change the name of the sheet, the macro looks for the old name of the sheet. So this doesn't work

How can I automatically update A1, A2, etc with the name of the sheets that I give in Excel?

Please help me find a formula (no VBA)!!

Thanks,

Ann

[Bearacade]

I could be wrong, but I am pretty certain that this can't be done without VBA

[Jim Thomlinson]

To the best of my knowledge there is no such formula. VBA is the only option
for that one...
--
HTH...

Jim Thomlinson


"ann" wrote:

>
> Hello,
>
> I have five sheets and they are named by weeks (ex: 5-7-2006 to
> 5-13-2006, 5-14-2006 to 5-20-2006, etc.). In another sheet, Weekly
> table, I need to put these sheet names in the first row. Basically,
> whatever name you give to the five sheets, it should automatically
> update the first row of the Weekly table sheet.
>
> I tried to do thru macro: Go to the first sheet, right click and
> select Rename and select Copy and paste the name of the sheet in the
> first row (A1); Go to the second sheet, right click and select Rename
> and select Copy and paste the second sheet name in A2. If I change the
> name of the sheet, the macro looks for the old name of the sheet. So
> this doesn't work
>
> How can I automatically update A1, A2, etc with the name of the sheets
> that I give in Excel?
>
> Please help me find a formula (no VBA)!!
>
> Thanks,
>
> Ann
>
>
> --
> ann
> ------------------------------------------------------------------------
> ann's Profile: http://www.excelforum.com/member.php...o&userid=17129
> View this thread: http://www.excelforum.com/showthread...hreadid=554338
>
>

[Dave Peterson]

You have more responses in .misc.

ann wrote:
>
> Hello,
>
> I have five sheets and they are named by weeks (ex: 5-7-2006 to
> 5-13-2006, 5-14-2006 to 5-20-2006, etc.). In another sheet, Weekly
> table, I need to put these sheet names in the first row. Basically,
> whatever name you give to the five sheets, it should automatically
> update the first row of the Weekly table sheet.
>
> I tried to do thru macro: Go to the first sheet, right click and
> select Rename and select Copy and paste the name of the sheet in the
> first row (A1); Go to the second sheet, right click and select Rename
> and select Copy and paste the second sheet name in A2. If I change the
> name of the sheet, the macro looks for the old name of the sheet. So
> this doesn't work
>
> How can I automatically update A1, A2, etc with the name of the sheets
> that I give in Excel?
>
> Please help me find a formula (no VBA)!!
>
> Thanks,
>
> Ann
>
> --
> ann
> ------------------------------------------------------------------------
> ann's Profile: http://www.excelforum.com/member.php...o&userid=17129
> View this thread: http://www.excelforum.com/showthread...hreadid=554338

--

Dave Peterson