Sheet Name Formul

[rikvny02]

When using the Sheet name formula excel is not allowing me to reference that cell with another formula. Any ideas?

=TEXTAFTER(CELL("filename",A1),"]")

Then I am trying to use a simple vlookup based off the cell with the file name.

=IFERROR(VLOOKUP($C$2,options!$E:$F,2,0),"")

 

[Dave Patton]

It seems to work for me. Try checking the ranges.

T202312a.xlsm
	C	D
2	5a	88
3		88
5a

Cell Formulas
Range	Formula
D2	D2	=VLOOKUP($C$2,$E:$F,2,0)
D3	D3	=IFERROR(VLOOKUP($C$2,Options!$E:$F,2,0),"")

 

[rikvny02]

It seems to work for me. Try checking the ranges.

T202312a.xlsm
	C	D
2	5a	88
3		88
5a

Cell Formulas
Range	Formula
D2	D2	=VLOOKUP($C$2,$E:$F,2,0)
D3	D3	=IFERROR(VLOOKUP($C$2,Options!$E:$F,2,0),"")

Its weird because my formulas are correct. If i remove the formula from c2 and enter the sheet name manually the Vlookups work just fine.

 

[Dave Patton]

Try evaluating each part of the formula. What is in each of the relevant cells?

T202312a.xlsm
	C	D	E	F
1	This Sheet
2	5a	88
3		200
4			5a	88
5			2c	99
6
5a

Cell Formulas
Range	Formula
C2	C2	=TEXTAFTER(CELL("filename",A1),"]")
D2	D2	=VLOOKUP($C$2,E4:F5,2,0)
D3	D3	=VLOOKUP($C$2,Options!E4:F8,2,0)

T202312a.xlsm
	B	C	D	E	F	G
1		This Sheet	Options
2
3
4				5a	200
5				2c	100
6
7
Options

Cell Formulas
Range	Formula
D1	D1	=TEXTAFTER(CELL("filename",A1),"]")

 

[rikvny02]

Do you have the below formula in cell c2
=TEXTAFTER(CELL("filename",A1),"]")

I even tried another version and again no results. =MID(CELL("filename",C2),FIND("]",CELL("filename",C2))+1,255)

 

[Dave Patton]

Please advise what is in each of the relevant cells

Please expand on what you are trying to do.


Please check post #4

 

[rikvny02]

Sorry but I'm at work and can not download the add-in to upload a sample sheet

C2= the employees number
C1= should return the employees name
As you can see the formula in C2 does return the sheet name. but the vlookup formulas do not return any values. If i manually type the emp# (3021) into cell C2 all the formulas populate the correct information. Ive tried to change the cell format and nothing. Im just at a loss.

 

[rikvny02]

I found this answer which makes complete sense, but how do i get around this issue. Unfortunately its important to the end user to have the sheet name be the employee number.

 

[rikvny02]

Solved. All we needed to do was use the Value function.

=VALUE(TEXTAFTER(CELL("filename",A1),"]"))

Dave thanks for all your help.

 

[Dave Patton]

"Solved.". Great that you solved your problem.

It would really help readers of the forum if you showed what you were trying to do and how you solved it.
Dave