naming a sheet the same as a cell in that sheet
 

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

naming a sheet the same as a cell in that sheet
 

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


"des-sa" wrote:

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

naming a sheet the same as a cell in that sheet
 

A simplified answer to your question. Right click the sheet tabview
codecopy/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

"Suleman Peerzade" wrote in
message ...
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


"des-sa" wrote:

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

naming a sheet the same as a cell in that sheet
 

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


On Wed, 16 Jul 2008 03:10:47 -0700, des-sa
wrote:

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