What I want to do is set up a Workbook with Sheets N01 - N30, and have an
index Sheet such that when N01 in the index is overtyped with 'Fred'
Worksheet N01 is renamend 'Fred'. This way I can set up formulae etc which
will auto update as and when Sheet names are updated. Is this possible?

An earlier reply by Bondi (Thanks Bondi) pointed to
http://www.ozgrid.com/Excel/variable...heet-names.htm, but either I'm not
reading this right or it's not quite what I need.

TIA.

Chris.

If you used the macro I provided to get an index and you had a title in row
1 then

Sub listsheets()
For i = 1 To Worksheets.Count
Cells(i + 1, "a") = Sheets(i).Name
Next i
End Sub

right click sheet tab>view code>insert this. A change to the listed name
will now chg the sheet name.
I would run my original macro before in case you have moved sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Sheets(Target.Row - 1).Name = Target
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Chris Mitchell" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> What I want to do is set up a Workbook with Sheets N01 - N30, and have an
> index Sheet such that when N01 in the index is overtyped with 'Fred'
> Worksheet N01 is renamend 'Fred'. This way I can set up formulae etc
> which will auto update as and when Sheet names are updated. Is this
> possible?
>
> An earlier reply by Bondi (Thanks Bondi) pointed to
> http://www.ozgrid.com/Excel/variable...heet-names.htm, but either I'm
> not reading this right or it's not quite what I need.
>
> TIA.
>
> Chris.
>

Thanks Don.

Our most recent posting must have passed each other in the ether.

"Don Guillett" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> If you used the macro I provided to get an index and you had a title in
> row 1 then
>
> Sub listsheets()
> For i = 1 To Worksheets.Count
> Cells(i + 1, "a") = Sheets(i).Name
> Next i
> End Sub
>
> right click sheet tab>view code>insert this. A change to the listed name
> will now chg the sheet name.
> I would run my original macro before in case you have moved sheets.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column <> 1 Then Exit Sub
> Sheets(Target.Row - 1).Name = Target
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Chris Mitchell" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> What I want to do is set up a Workbook with Sheets N01 - N30, and have an
>> index Sheet such that when N01 in the index is overtyped with 'Fred'
>> Worksheet N01 is renamend 'Fred'. This way I can set up formulae etc
>> which will auto update as and when Sheet names are updated. Is this
>> possible?
>>
>> An earlier reply by Bondi (Thanks Bondi) pointed to
>> http://www.ozgrid.com/Excel/variable...heet-names.htm, but either I'm
>> not reading this right or it's not quite what I need.
>>
>> TIA.
>>
>> Chris.
>>
>
>

You really should stay in the ORIGINAL thread. Send me (address below) your
workbook if you like.

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Chris Mitchell" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks Don.
>
> Our most recent posting must have passed each other in the ether.
>
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> If you used the macro I provided to get an index and you had a title in
>> row 1 then
>>
>> Sub listsheets()
>> For i = 1 To Worksheets.Count
>> Cells(i + 1, "a") = Sheets(i).Name
>> Next i
>> End Sub
>>
>> right click sheet tab>view code>insert this. A change to the listed name
>> will now chg the sheet name.
>> I would run my original macro before in case you have moved sheets.
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Column <> 1 Then Exit Sub
>> Sheets(Target.Row - 1).Name = Target
>> End Sub
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Chris Mitchell" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> What I want to do is set up a Workbook with Sheets N01 - N30, and have
>>> an index Sheet such that when N01 in the index is overtyped with 'Fred'
>>> Worksheet N01 is renamend 'Fred'. This way I can set up formulae etc
>>> which will auto update as and when Sheet names are updated. Is this
>>> possible?
>>>
>>> An earlier reply by Bondi (Thanks Bondi) pointed to
>>> http://www.ozgrid.com/Excel/variable...heet-names.htm, but either I'm
>>> not reading this right or it's not quite what I need.
>>>
>>> TIA.
>>>
>>> Chris.
>>>
>>
>>
>
>

Thanks for the offer Don, I've sent the file to you.

"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You really should stay in the ORIGINAL thread. Send me (address below)
> your workbook if you like.
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Chris Mitchell" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thanks Don.
>>
>> Our most recent posting must have passed each other in the ether.
>>
>> "Don Guillett" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> If you used the macro I provided to get an index and you had a title in
>>> row 1 then
>>>
>>> Sub listsheets()
>>> For i = 1 To Worksheets.Count
>>> Cells(i + 1, "a") = Sheets(i).Name
>>> Next i
>>> End Sub
>>>
>>> right click sheet tab>view code>insert this. A change to the listed name
>>> will now chg the sheet name.
>>> I would run my original macro before in case you have moved sheets.
>>>
>>> Private Sub Worksheet_Change(ByVal Target As Range)
>>> If Target.Column <> 1 Then Exit Sub
>>> Sheets(Target.Row - 1).Name = Target
>>> End Sub
>>>
>>> --
>>> Don Guillett
>>> SalesAid Software
>>> (E-Mail Removed)
>>> "Chris Mitchell" <(E-Mail Removed)> wrote in
>>> message news:(E-Mail Removed)...
>>>> What I want to do is set up a Workbook with Sheets N01 - N30, and have
>>>> an index Sheet such that when N01 in the index is overtyped with 'Fred'
>>>> Worksheet N01 is renamend 'Fred'. This way I can set up formulae etc
>>>> which will auto update as and when Sheet names are updated. Is this
>>>> possible?
>>>>
>>>> An earlier reply by Bondi (Thanks Bondi) pointed to
>>>> http://www.ozgrid.com/Excel/variable...heet-names.htm, but either
>>>> I'm not reading this right or it's not quite what I need.
>>>>
>>>> TIA.
>>>>
>>>> Chris.
>>>>
>>>
>>>
>>
>>
>
>

File returned to Chris with desires

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 3 Or Target.Column <> 2 Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Sheets(Target.Row).Name = Target
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Chris Mitchell" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks for the offer Don, I've sent the file to you.
>
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> You really should stay in the ORIGINAL thread. Send me (address below)
>> your workbook if you like.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Chris Mitchell" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Thanks Don.
>>>
>>> Our most recent posting must have passed each other in the ether.
>>>
>>> "Don Guillett" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> If you used the macro I provided to get an index and you had a title in
>>>> row 1 then
>>>>
>>>> Sub listsheets()
>>>> For i = 1 To Worksheets.Count
>>>> Cells(i + 1, "a") = Sheets(i).Name
>>>> Next i
>>>> End Sub
>>>>
>>>> right click sheet tab>view code>insert this. A change to the listed
>>>> name will now chg the sheet name.
>>>> I would run my original macro before in case you have moved sheets.
>>>>
>>>> Private Sub Worksheet_Change(ByVal Target As Range)
>>>> If Target.Column <> 1 Then Exit Sub
>>>> Sheets(Target.Row - 1).Name = Target
>>>> End Sub
>>>>
>>>> --
>>>> Don Guillett
>>>> SalesAid Software
>>>> (E-Mail Removed)
>>>> "Chris Mitchell" <(E-Mail Removed)> wrote in
>>>> message news:(E-Mail Removed)...
>>>>> What I want to do is set up a Workbook with Sheets N01 - N30, and have
>>>>> an index Sheet such that when N01 in the index is overtyped with
>>>>> 'Fred' Worksheet N01 is renamend 'Fred'. This way I can set up
>>>>> formulae etc which will auto update as and when Sheet names are
>>>>> updated. Is this possible?
>>>>>
>>>>> An earlier reply by Bondi (Thanks Bondi) pointed to
>>>>> http://www.ozgrid.com/Excel/variable...heet-names.htm, but either
>>>>> I'm not reading this right or it's not quite what I need.
>>>>>
>>>>> TIA.
>>>>>
>>>>> Chris.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>