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

  • Thread starter Thread starter Ant
  • Start date Start date
A

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
 
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)
 
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.
 
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
Click to expand...


.
Click to expand...
 
Take a look at David McRitchie's BuildTOC macro:

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

ant said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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)


ant said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
a
way


.
Click to expand...
.
Click to expand...
 
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.


Peo Sjoblom said:
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)


ant said:
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



.
Click to expand...
Click to expand...
Click to expand...
 
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)


Stephen Dunn said:
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.


Peo Sjoblom said:
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)


ant said:
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



.
Click to expand...
Click to expand...
Click to expand...
 
Hi Peo,

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


Peo Sjoblom said:
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)


Stephen Dunn said:
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.


Peo Sjoblom said:
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



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


Stunn said:
Hi Peo,

I agree, it was just the word *need* that niggled me a little. Never
mind...
Click to expand...
<snip>
 
I figured that out <g>

--
Regards,

Peo Sjoblom

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