Sheet name in a formula

[Alank]

Version: 2008
Operating System: Mac OS X 10.4 (Tiger)
Processor: Intel

Is it possible to use the name of a sheet in a formula? I have a sheet for each week of a diary. Each sheet is named as the first day of the week eg 21/09/2009. I want to use that date to name the next sheet and to enter it into a cell. Any ideas? Thanks.

 

[CyberTaz]

I'm not sure I follow you -- The slash (/) is an illegal character in a
sheet name. If you even try to name a sheet 21/09/2009 the slashes will be
ignored & you'll wind up with 21092009 as the sheet name.

Do you actually mean that you have the date entered into a cell on the
sheet, whatever the sheet name (on the sheet's tab at the bottom) might
happen to be? And that you want a cell on a different sheet to display that
date+one day? Or are you asking for something else?

Please give a more specific description of exactly what you need to achieve.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Phillip Jones, C.E.T.]

There are whole host of characters that are illegal for names. (same
series of items that would be illegal in UNIX.

to name a few: * - + / \ : :: > < ; and ,

The safest is *underscore* its acceptable. example: 2_12_2012

 

[Alank]

I'm not sure I follow you -- The slash (/) is an illegal character in a

sheet name. If you even try to name a sheet 21/09/2009 the slashes will be
ignored & you'll wind up with 21092009 as the sheet name.

Do you actually mean that you have the date entered into a cell on the
sheet, whatever the sheet name (on the sheet's tab at the bottom) might
happen to be? And that you want a cell on a different sheet to display that
date+one day? Or are you asking for something else?

Please give a more specific description of exactly what you need to achieve.

Regards |:>)
Bob Jones
[MVP] Office:Mac

Sorry I've not done a good job of describing what I'm trying to achieve!

Each sheet in the diary represents a week.

I produce new sheets by copying an original sheet called let's say 1 Jan 2009(point taken about illegal characters). There is also a cell in the original sheet containing 1 Jan 2009.

I then have to rename the new sheet 8 jan 2009 and then change the content of the cell to 8 Jan 2009 as well.

I want to be able to automatically change the date in the cell and the name of the sheet either as i copy them or by creating a formula that calculates the date in the sheet by how many copies of the sheet have been made and somehow changes the name of the sheet as well.

I can create a series of dates on a single sheet at weekly intervals but not across several sheets.

I suspect the renaming of the sheets is a separate issue.

Is that any clearer? I'm worried it's not!

Thanks for your help Bob

 

[Jim Gordon MVP]

Sorry I've not done a good job of describing what I'm trying to achieve!

Each sheet in the diary represents a week.

I produce new sheets by copying an original sheet called let's say 1 Jan 2009(point taken about illegal characters). There is also a cell in the original sheet containing 1 Jan 2009.

I then have to rename the new sheet 8 jan 2009 and then change the content of the cell to 8 Jan 2009 as well.

I want to be able to automatically change the date in the cell and the name of the sheet either as i copy them or by creating a formula that calculates the date in the sheet by how many copies of the sheet have been made and somehow changes the name of the sheet as well.

I can create a series of dates on a single sheet at weekly intervals but not across several sheets.

I suspect the renaming of the sheets is a separate issue.

Is that any clearer? I'm worried it's not!

Thanks for your help Bob

Hi,

I don't think you can use sheet names all by themselves in formulas. You
can refer to cell ranges (which you could define as the entire sheet if
you want to). It seems to me what you are trying to do could be achieved
by deciding upon a cell in each sheet to have the cell contents match
the sheet name. Then create a named range using the cell containing the
sheet hame, and then you can use that in cell formulas.

I hope you can follow my train of thought.

-Jim

 

[CyberTaz]

I'm afraid I have no idea how this could be done... At least not without
VBA. What I can tell you is that sheet tabs are not cells, so they cannot
contain formulas, Nor is there any function that returns the name of a sheet
or counts the number of sheets in a book.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Alank]

Thanks for everyone's help. Looks like there's no easy solution!