Reference a tab???? Can it be done???

[Ant]

This might sound like a stupid question but is there a way
to reference the name of tabs?


For example on one sheet I just want the name of a
different tab in cell A1

=cell("tab") doesn't work

Any ideas?

Ant

 

[Peo Sjoblom]

One way

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

note that the workbook has to be saved first

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Andy B]

Hi

This:
=RIGHT(CELL("filename",'Car Log'!A1),LEN(CELL("filename",'Car
Log'!A1))-FIND("]",CELL("filename",'Car Log'!A1),1))
will return Car Log - but you might as well just type it in!! Of course, if
you change it from Car Log to something else, it will bring back the new
name.

 

[ant]

Sorry i wasnt clearer.... this works great for the sheet
im im working with... but list say i want a list of all
the sheets i have in my workbook on my page

For example.... i have 20 tabs and on one sheet i want
the name of all those tabs Any Ideas

-----Original Message-----
One way

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

note that the workbook has to be saved first

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

This might sound like a stupid question but is there a way
to reference the name of tabs?


For example on one sheet I just want the name of a
different tab in cell A1

=cell("tab") doesn't work

Any ideas?

Ant

.

 

[JE McGimpsey]

Take a look at David McRitchie's BuildTOC macro:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Sorry i wasnt clearer.... this works great for the sheet
im im working with... but list say i want a list of all
the sheets i have in my workbook on my page

For example.... i have 20 tabs and on one sheet i want
the name of all those tabs Any Ideas

-----Original Message-----
One way

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

note that the workbook has to be saved first

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

This might sound like a stupid question but is there a way
to reference the name of tabs?


For example on one sheet I just want the name of a
different tab in cell A1

=cell("tab") doesn't work

Any ideas?

Ant

.

 

[Peo Sjoblom]

You would need VBA for that

see:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm#BuildTOC





--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Sorry i wasnt clearer.... this works great for the sheet
im im working with... but list say i want a list of all
the sheets i have in my workbook on my page

For example.... i have 20 tabs and on one sheet i want
the name of all those tabs Any Ideas

-----Original Message-----
One way

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

note that the workbook has to be saved first

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

This might sound like a stupid question but is there a way
to reference the name of tabs?


For example on one sheet I just want the name of a
different tab in cell A1

=cell("tab") doesn't work

Any ideas?

Ant

.

 

[Jason Morin]

You could try this macro:

Sub List_Sheets()
Dim i As Integer
Dim ws As Worksheet

i = 0
For Each ws In ActiveWorkbook.Worksheets
With ActiveCell.Offset(i, 0)
.Value = ws.Name
End With
i = i + 1
Next
End Sub

Press ALT+F11, go to Insert > Module, paste in the code
above, and run the macro.

--
HTH
Jason
Atlanta, GA

-----Original Message-----
Sorry i wasnt clearer.... this works great for the sheet
im im working with... but list say i want a list of all
the sheets i have in my workbook on my page

For example.... i have 20 tabs and on one sheet i want
the name of all those tabs Any Ideas

-----Original Message-----
One way

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

note that the workbook has to be saved first

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

This might sound like a stupid question but is there

a

way


.

.

 

[Stephen Dunn]

Hi Peo,

Just to be awkward, I would dispute the word *need*

You could have a cell in each sheet, containing:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-
FIND("]",CELL("filename",A1),1))

Then create a list of references to those cells on the final sheet.

Just a thought.

Steve D.

You would need VBA for that

see:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm#BuildTOC





--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Sorry i wasnt clearer.... this works great for the sheet
im im working with... but list say i want a list of all
the sheets i have in my workbook on my page

For example.... i have 20 tabs and on one sheet i want
the name of all those tabs Any Ideas

-----Original Message-----
One way

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

note that the workbook has to be saved first

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


This might sound like a stupid question but is there a way
to reference the name of tabs?


For example on one sheet I just want the name of a
different tab in cell A1

=cell("tab") doesn't work

Any ideas?

Ant



.

 

[Peo Sjoblom]

Stephen,

it is true that you can solve it like that and maybe I should have included
that solution,
however you have to create links (assuming that the OP have 20 sheets it
would be 20 links) for each formula
to the list sheet.
Being somewhat of a functions person I normally try to solve things w/o VBA
but on occasions I believe it is much more practical to use VBA. This would
certainly come under that category..

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Hi Peo,

Just to be awkward, I would dispute the word *need*

You could have a cell in each sheet, containing:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-
FIND("]",CELL("filename",A1),1))

Then create a list of references to those cells on the final sheet.

Just a thought.

Steve D.

You would need VBA for that

see:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm#BuildTOC





--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Sorry i wasnt clearer.... this works great for the sheet
im im working with... but list say i want a list of all
the sheets i have in my workbook on my page

For example.... i have 20 tabs and on one sheet i want
the name of all those tabs Any Ideas
-----Original Message-----
One way

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

note that the workbook has to be saved first

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


message
This might sound like a stupid question but is there a
way
to reference the name of tabs?


For example on one sheet I just want the name of a
different tab in cell A1

=cell("tab") doesn't work

Any ideas?

Ant



.

 

[Stunn]

Hi Peo,

I agree, it was just the word *need* that niggled me a little. Never
mind...

Stephen,

it is true that you can solve it like that and maybe I should have included
that solution,
however you have to create links (assuming that the OP have 20 sheets it
would be 20 links) for each formula
to the list sheet.
Being somewhat of a functions person I normally try to solve things w/o VBA
but on occasions I believe it is much more practical to use VBA. This would
certainly come under that category..

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Hi Peo,

Just to be awkward, I would dispute the word *need*

You could have a cell in each sheet, containing:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-
FIND("]",CELL("filename",A1),1))

Then create a list of references to those cells on the final sheet.

Just a thought.

Steve D.

You would need VBA for that

see:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm#BuildTOC





--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


Sorry i wasnt clearer.... this works great for the sheet
im im working with... but list say i want a list of all
the sheets i have in my workbook on my page

For example.... i have 20 tabs and on one sheet i want
the name of all those tabs Any Ideas
-----Original Message-----
One way

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

note that the workbook has to be saved first

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


message
This might sound like a stupid question but is there a
way
to reference the name of tabs?


For example on one sheet I just want the name of a
different tab in cell A1

=cell("tab") doesn't work

Any ideas?

Ant



.

 

[Stunn]

Ooops, just realised, I should point out that Stunn = Stephen Dunn at home.

Hi Peo,

I agree, it was just the word *need* that niggled me a little. Never
mind...

<snip>

 

[Peo Sjoblom]

I figured that out <g>

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)