I want to refer to a worksheet name that will be specified by user, i will be using a formula something like vlookup, currently i can select the cells for that formula.. but i need to make the sheet name become flexible to user. is there anyway to do that?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to refer to a worksheet name ?
- Thread starter xiaojo
- Start date
phantom1975
MrExcel MVP
- Joined
- Jun 3, 2002
- Messages
- 3,962
WELCOME TO THE BOARD!
This formula will return the sheet name:
=MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
This formula will return the sheet name:
=MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
Upvote
0
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
Say your normal formula on Sheet1 returns the value from cell B1 on Sheet 2, like this:
='Sheet 2'!B1
If cell A1 on Sheet1 contains "Sheet 2" (no quotes) you can use this:
=INDIRECT("'"&$A$1&"'!B1")
so that the sheet to be used is a variable.
One problem with this method is that cell reference (the B1 part) will not adjust automatically when you copy the formula, because it is just a text string.
='Sheet 2'!B1
If cell A1 on Sheet1 contains "Sheet 2" (no quotes) you can use this:
=INDIRECT("'"&$A$1&"'!B1")
so that the sheet to be used is a variable.
One problem with this method is that cell reference (the B1 part) will not adjust automatically when you copy the formula, because it is just a text string.
Upvote
0
