naming a sheet the same as a cell in that sheet

[des-sa]

hi, i have 2 questions.
1. how do i give a sheet the same name as a cell entry in that sheet?
2. then when i enter the =now() formula, the date in that cell is kept as
the current date. is there a way to automatically convert the formula to a
number as soon as the date has been entered or in any other way?
thanks

 

[Suleman Peerzade]

Try this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

This would give you the sheet name exactly as the matter in the cell.

 

[Don Guillett]

A simplified answer to your question. Right click the sheet tab>view
code>copy/paste this. Now if you change cell a1 the sheet name will change
to that and if you put anything in cell a2 the date will be entered in value
form.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ActiveSheet.Name = Target
If Target.Address = "$A$2" Then Target = Date
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Try this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

This would give you the sheet name exactly as the matter in the cell.
--
Thanks
Suleman Peerzade

hi, i have 2 questions.
1. how do i give a sheet the same name as a cell entry in that sheet?
2. then when i enter the =now() formula, the date in that cell is kept as
the current date. is there a way to automatically convert the formula to
a
number as soon as the date has been entered or in any other way?
thanks

 

[Gord Dibben]

If the cell entry is manually entered see Don's reply.

If a calculated entry try this.

Private Sub Worksheet_Calculate()
Me.Name = Range("A1").Value
End Sub

On 2. you can enter a static date using CTRL + ;(semi-colon)


Gord Dibben MS Excel MVP