I have a workbook with 23 worksheets.

I need the worksheet name to be automatically inserted into a
cell on each worksheet.

Worksheet names are all 5 characters - example is 14-01

I'm using =Right(Cell("Filename"),5)

It works - sort of. But does not automatically calculate. When I
recalculate every worksheet gets the same value (the number of
the active worksheet when I press F9)

Is this just a quirk of Excel, or is there another way to do it?

Thanks for any help.

OK - I solved my own problem.

Change the formula to be:

=Right(Cell("Filename",B5),5) where B5 is the cell where the
formula is.


David wrote:
> I have a workbook with 23 worksheets.
>
> I need the worksheet name to be automatically inserted into a cell on
> each worksheet.
>
> Worksheet names are all 5 characters - example is 14-01
>
> I'm using =Right(Cell("Filename"),5)
>
> It works - sort of. But does not automatically calculate. When I
> recalculate every worksheet gets the same value (the number of the
> active worksheet when I press F9)
>
> Is this just a quirk of Excel, or is there another way to do it?
>
> Thanks for any help.

David

You can use any cell reference.....but you must use one, as you have discovered.


Gord Dibben MS Excel MVP

On Sat, 08 Jul 2006 13:22:31 -0700, David <(E-Mail Removed)> wrote:

>OK - I solved my own problem.
>
>Change the formula to be:
>
>=Right(Cell("Filename",B5),5) where B5 is the cell where the
>formula is.
>
>
>David wrote:
>> I have a workbook with 23 worksheets.
>>
>> I need the worksheet name to be automatically inserted into a cell on
>> each worksheet.
>>
>> Worksheet names are all 5 characters - example is 14-01
>>
>> I'm using =Right(Cell("Filename"),5)
>>
>> It works - sort of. But does not automatically calculate. When I
>> recalculate every worksheet gets the same value (the number of the
>> active worksheet when I press F9)
>>
>> Is this just a quirk of Excel, or is there another way to do it?
>>
>> Thanks for any help.