Sheet name

[Guest]

I am using =MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,255) to return
the shet name. The sheets are Jan-Dec. I have copied the formula from one
sheet to the next. But once all copied and I go back to Jan! it has Dec in
the cell rather than Jan. What amI doing wrong?
Thanks in advance
Gayle

 

[Max]

One guess ..
You might have grouped the sheets in the process ..
Try press F9 in the Jan sheet
The formula should compute and return the correct sheetname

 

[Guest]

Thanks Max
If I click a new sheet and F9 then then all sheets are updated with the
active sheet date. The sheets are not grouped. Its like the formula is
linked to the current sheet.

Thanks again

One guess ..
You might have grouped the sheets in the process ..
Try press F9 in the Jan sheet
The formula should compute and return the correct sheetname
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

I am using =MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,255) to return
the shet name. The sheets are Jan-Dec. I have copied the formula from one
sheet to the next. But once all copied and I go back to Jan! it has Dec in
the cell rather than Jan. What amI doing wrong?
Thanks in advance
Gayle

 

[Peo Sjoblom]

You need to save the workbook (file>save as)
else you get the active sheet name

Regards,

Peo Sjoblom

Thanks Max
If I click a new sheet and F9 then then all sheets are updated with the
active sheet date. The sheets are not grouped. Its like the formula is
linked to the current sheet.

Thanks again

One guess ..
You might have grouped the sheets in the process ..
Try press F9 in the Jan sheet
The formula should compute and return the correct sheetname
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

I am using =MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,255) to return
the shet name. The sheets are Jan-Dec. I have copied the formula from one
sheet to the next. But once all copied and I go back to Jan! it has Dec in
the cell rather than Jan. What amI doing wrong?
Thanks in advance
Gayle

 

[Max]

Afraid I'm not able to replicate your error.
The formula works fine on every sheet
that it is copied into. Maybe re-check that the formula
is copied correctly in the target cell in each sheet?

The behaviour you posted seems to point
to either grouped sheets and/or manual calculation mode
(but the F9 press should have recalculated / cleared this)

Anyway, no harm checking the calc mode as well:
Click Tools > Options > Calculation tab
Is it in Automatic mode? Check the box and click OK
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

Thanks Max
If I click a new sheet and F9 then then all sheets are updated with the
active sheet date. The sheets are not grouped. Its like the formula is
linked to the current sheet.

Thanks again

One guess ..
You might have grouped the sheets in the process ..
Try press F9 in the Jan sheet
The formula should compute and return the correct sheetname
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

I am using =MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,255) to return
the shet name. The sheets are Jan-Dec. I have copied the formula from one
sheet to the next. But once all copied and I go back to Jan! it has Dec in
the cell rather than Jan. What amI doing wrong?
Thanks in advance
Gayle

 

[Guest]

Even after the file is saved, it still is not calculating the correct sheet
name. Recalc is set to auto. I created a scratch file and put the same
formula there and it was exhibiting the same behaviour. WHen I switched back
to my original file the cell in question had the contents of the cell from
the scratch file.

You need to save the workbook (file>save as)
else you get the active sheet name

Regards,

Peo Sjoblom

Thanks Max
If I click a new sheet and F9 then then all sheets are updated with the
active sheet date. The sheets are not grouped. Its like the formula is
linked to the current sheet.

Thanks again

One guess ..
You might have grouped the sheets in the process ..
Try press F9 in the Jan sheet
The formula should compute and return the correct sheetname
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
I am using =MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,255) to
return
the shet name. The sheets are Jan-Dec. I have copied the formula from one
sheet to the next. But once all copied and I go back to Jan! it has Dec in
the cell rather than Jan. What amI doing wrong?
Thanks in advance
Gayle

 

[Max]

You need to save the workbook (file>save as)
else you get the active sheet name

Peo, I remembered this reqt for the formula,
but I got #VALUE! instead if book wasn't saved
when I tested, hence excluded this possibility ..

 

[Peo Sjoblom]

You forgot to add a cell reference to your formula

I missed that, try this

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,34)

no need for 255 characters btw since a sheet name can only have 34 I believ,
saves one character <g>


Regards,

Peo Sjoblom

 

[Guest]

Thanks so much for your help----That got it!

You forgot to add a cell reference to your formula

I missed that, try this

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,34)

no need for 255 characters btw since a sheet name can only have 34 I believ,
saves one character <g>


Regards,

Peo Sjoblom

Even after the file is saved, it still is not calculating the correct sheet
name. Recalc is set to auto. I created a scratch file and put the same
formula there and it was exhibiting the same behaviour. WHen I switched back
to my original file the cell in question had the contents of the cell from
the scratch file.

You need to save the workbook (file>save as)
else you get the active sheet name

Regards,

Peo Sjoblom


Thanks Max
If I click a new sheet and F9 then then all sheets are updated with the
active sheet date. The sheets are not grouped. Its like the formula is
linked to the current sheet.

Thanks again

:

One guess ..
You might have grouped the sheets in the process ..
Try press F9 in the Jan sheet
The formula should compute and return the correct sheetname
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
I am using =MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,255) to
return
the shet name. The sheets are Jan-Dec. I have copied the formula from
one
sheet to the next. But once all copied and I go back to Jan! it has
Dec in
the cell rather than Jan. What amI doing wrong?
Thanks in advance
Gayle