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=554347

Ann

I know you said no VBA

but I don't know how to do it using a formula

this VBA code will work regardless of the sheet name.
It adds sheet name into A1 on every sheet

Sub SheetNames()
Dim wS As Worksheet
For Each wS In Worksheets
Range("a1").Value = wS.Name
Next wS
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=554347

I tried the VBA and it picks name of the last sheet, but not from the
beginning.


--
ann
------------------------------------------------------------------------
ann's Profile: http://www.excelforum.com/member.php...o&userid=17129
View this thread: http://www.excelforum.com/showthread...hreadid=554347

You said no VBA but I know of no function that returns a sheet name and if
there is such a function
I expect it can return only the active sheet name.
However, this simple sub seems to work OK

Sub macro2()
j = 1
For Each wks In Worksheets
Cells(j, 1).Value = wks.Name
j = j + 1
Next
End Sub

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"ann" wrote in message
...

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=554347

You could put this kind of formula in the cells:

=MID(CELL("filename",'Sheet 2'!A1),
FIND("]",CELL("filename",'Sheet 2'!A1))+1,255)

Change the 'sheet 2' to your original sheet names.

When the user changes names, these formulas will change accordingly. (Yes, you
have to do a little work up front.)

And the workbook has to be saved at least once.



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=554347

--

Dave Peterson

and you want it to automatically update upon renaming of sheets.
You have answers except for that -- I can only see problems with
this. Because you can rearrange sheet tabs besides renaming them.
and then if you rerun the macro then the data will not match the sheettab
name at the top of the row.

If you are going to use dates for sheet tabs, I would highly
recommend that you format them at yyyy-mm-dd
or as yyyy_mmdd as you would have a mess if you attempted
to sort your worksheet tabs with a macro with what you have.
http://www.mvps.org/dmcritchie/excel...#sortallsheets
the same format for dates in filenames is also advisible
http://www.mvps.org/dmcritchie/excel/backup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"ann" wrote in message ...

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=554347

Anne

I see Dave has posted a formula for you

To fix up the error in my code

Range("a1").Value = wS.Name - This puts the name in A1 of the active
sheet

should be
wS.Range("a1").Value = wS.Name
This puts the sheet name in A1 of each sheet


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=554347

Thank you so much!!

This formula works:

=MID(CELL("filename",'Sheet 2'!A1),
FIND("]",CELL("filename",'Sheet 2'!A1))+1,255

Ann


--
ann
------------------------------------------------------------------------
ann's Profile: http://www.excelforum.com/member.php...o&userid=17129
View this thread: http://www.excelforum.com/showthread...hreadid=554347