Can a cell value be set equal to a spreadsheet name

[amp4cats]

I am attempting to set a cell in a spreadsheet equal to a title of another
worksheet. Is this possible to do?

 

[JLatham]

Yes and no? This is almost one of those "you have to know the answer before
we will tell you the answer" type of things.
A formula that will give you the sheet name of a sheet in the workbook is
this one (entered into a cell on 'Sheet1' and pointed to a cell in another
sheet in the workbook [Sheet3])

=MID(CELL("address",Sheet3!A1),FIND("]",CELL("address",Sheet3!A1))+1,FIND("!",CELL("address",Sheet3!A1))-FIND("]",CELL("address",Sheet3!A1))-1)

If you'll put this formula into another cell on Sheet1 you will see the need
for the FIND() functions in it:
=CELL("address",Sheet3!A1)

So you have to know the name of the other sheet before you can ask for the
name of that other sheet. But if the name of the other sheet is changed
later, the formula will show the new name.

Another way to do this is using VB code to work through the list of sheets
in the workbook and put their names into cells in a particular workbook:

Sub ListSheetNames()
Dim anySheet As Worksheet

Worksheets("Sheet1").Select
Range("A1").Select
For Each anySheet In Worksheets
ActiveCell = anySheet.Name
ActiveCell.Offset(1, 0).Activate
Next
End Sub

 

[David Biddulph]

=MID(CELL("filename",Sheet2!A1), FIND("]", CELL("filename", Sheet2!A1))+ 1,
255)
will give "Sheet2" as a result.

 

[amp4cats]

Thank you, it work like a charm.
Exactly what I needed.

Yes and no? This is almost one of those "you have to know the answer before
we will tell you the answer" type of things.
A formula that will give you the sheet name of a sheet in the workbook is
this one (entered into a cell on 'Sheet1' and pointed to a cell in another
sheet in the workbook [Sheet3])

=MID(CELL("address",Sheet3!A1),FIND("]",CELL("address",Sheet3!A1))+1,FIND("!",CELL("address",Sheet3!A1))-FIND("]",CELL("address",Sheet3!A1))-1)

If you'll put this formula into another cell on Sheet1 you will see the need
for the FIND() functions in it:
=CELL("address",Sheet3!A1)

So you have to know the name of the other sheet before you can ask for the
name of that other sheet. But if the name of the other sheet is changed
later, the formula will show the new name.

Another way to do this is using VB code to work through the list of sheets
in the workbook and put their names into cells in a particular workbook:

Sub ListSheetNames()
Dim anySheet As Worksheet

Worksheets("Sheet1").Select
Range("A1").Select
For Each anySheet In Worksheets
ActiveCell = anySheet.Name
ActiveCell.Offset(1, 0).Activate
Next
End Sub

I am attempting to set a cell in a spreadsheet equal to a title of another
worksheet. Is this possible to do?

 

[amp4cats]

When attempting your solution, I received a #VALUE! error

Yes and no? This is almost one of those "you have to know the answer before
we will tell you the answer" type of things.
A formula that will give you the sheet name of a sheet in the workbook is
this one (entered into a cell on 'Sheet1' and pointed to a cell in another
sheet in the workbook [Sheet3])

=MID(CELL("address",Sheet3!A1),FIND("]",CELL("address",Sheet3!A1))+1,FIND("!",CELL("address",Sheet3!A1))-FIND("]",CELL("address",Sheet3!A1))-1)

If you'll put this formula into another cell on Sheet1 you will see the need
for the FIND() functions in it:
=CELL("address",Sheet3!A1)

So you have to know the name of the other sheet before you can ask for the
name of that other sheet. But if the name of the other sheet is changed
later, the formula will show the new name.

Another way to do this is using VB code to work through the list of sheets
in the workbook and put their names into cells in a particular workbook:

Sub ListSheetNames()
Dim anySheet As Worksheet

Worksheets("Sheet1").Select
Range("A1").Select
For Each anySheet In Worksheets
ActiveCell = anySheet.Name
ActiveCell.Offset(1, 0).Activate
Next
End Sub

I am attempting to set a cell in a spreadsheet equal to a title of another
worksheet. Is this possible to do?

 

[JLatham]

I'm a bit confused - you said it worked, then it didn't work? Or were you
responding to David's post when you said it didn't work?
Which worked? Which failed?

When attempting your solution, I received a #VALUE! error

Yes and no? This is almost one of those "you have to know the answer before
we will tell you the answer" type of things.
A formula that will give you the sheet name of a sheet in the workbook is
this one (entered into a cell on 'Sheet1' and pointed to a cell in another
sheet in the workbook [Sheet3])

=MID(CELL("address",Sheet3!A1),FIND("]",CELL("address",Sheet3!A1))+1,FIND("!",CELL("address",Sheet3!A1))-FIND("]",CELL("address",Sheet3!A1))-1)

If you'll put this formula into another cell on Sheet1 you will see the need
for the FIND() functions in it:
=CELL("address",Sheet3!A1)

So you have to know the name of the other sheet before you can ask for the
name of that other sheet. But if the name of the other sheet is changed
later, the formula will show the new name.

Another way to do this is using VB code to work through the list of sheets
in the workbook and put their names into cells in a particular workbook:

Sub ListSheetNames()
Dim anySheet As Worksheet

Worksheets("Sheet1").Select
Range("A1").Select
For Each anySheet In Worksheets
ActiveCell = anySheet.Name
ActiveCell.Offset(1, 0).Activate
Next
End Sub

I am attempting to set a cell in a spreadsheet equal to a title of another
worksheet. Is this possible to do?