×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

different sheet name in cell

different sheet name in cell

different sheet name in cell

(OP)
The post about how to put a sheet name in a cell works great for the current sheet, but I can't figure out how to reference another sheet name (on sheet "A" I want cell B2 to reference the name of sheet "B")

Which part of this formula references the sheet? (if any)

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

Does anyone know how to do this?

thanks!

RE: different sheet name in cell

I think you just need to put the sheet "B" reference in the formula like this:-
=MID(CELL("filename",B!A1),FIND("]",CELL("filename",B!A1))+1,30)

RE: different sheet name in cell

(OP)
Thanks, I tried it and it looks like excel thinks "B" is another workbook, because instead of entering a value in the cell, a directory window opens with the message: "update values: B", and it wants an existing filename.

If the header/footer values were set to display path and filename for each sheet, and the whole workbook were printed, how would it reference each sheet?

RE: different sheet name in cell

as noted in the other thread,

Did you try:

=cell("filename",'SheetB'!B2)

and

=MID(CELL("filename",'SheetB'!B2),FIND("]",CELL("filename",'SheetB'!B2))+1,30)


I dont' know about the header/footer question.  I dont' think you can put equations into the header/footer boxes inthe page setup dialog.

RE: different sheet name in cell

(OP)
Thanks - and yes I have tried both, but xls thinks "B" or "B2" is another filename and opens an explorer window...  still searching for this one.

RE: different sheet name in cell

kza,

Of course you have to replace the 'SheetB' with the sheet you are referencing to. So if the other sheet name is for example Test, then the formula becomes:
=CELL("filename",'Test'!B2)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: different sheet name in cell

(OP)
Thanks, Joerd - that works and it makes sense, but seems to defeat the purpose of it all, because if the sheet name changes then it is not automatically updated in the cell referencing it?  I guess I am trying to do something like that, but this works for now.  Maybe I have to do a macro.

regards,
Kirsten

RE: different sheet name in cell

KZA
The punctuation is key.  Using double quotes "name" indicates a file, whereas using single quotes 'name' designates a worksheet.

As far as the sheet name changing, the reference should update as well.  Instead of typing in the reference, enter it into the equation by going to the cell and clicking it.  This will ensure that the syntax is correct.

In my experience Excel almost always updates references automatically if the sheet name changes.  Have you tested what happens when you change the sheet name?

This is not any different than any other worksheet cross reference, so I you are still having problems please cut/paste and poste the equation you are using so we can see what's going on.

RE: different sheet name in cell

(OP)
yeah!!  thanks for pointing that out to me - and you are right, updating the sheet name is reflected in the cell referencing it.

used formula below (sheet name 'OPTION B')

=MID(CELL("filename",'OPTION B'!B2),FIND("]",CELL("filename",'OPTION B'!B2))+1,30)

thank you so much.

kza

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources