VLOOKUP and Sheet Name

[Sajid Hussain]

I have a work sheet where VLOOKUP work properly but it does not work when i add a cell value that contain sheet name using formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) No issue at all, adding sheet name manually in that cell. but adding with formula i got error.

Exampl Sheet.xlsx
	A	B	C	D	E	F					K	L
1
2	No	Name	File #	Contractual Job Title	Employe Contact #
3	01	RIMSAN	2498	Electrician	Not Available
4	02	SAYED	5689	Helper	Not Available
5	03	HUSSAIN	1284	Safety Supervisor	Not Available
6	04	ABDUR RAZZAQ	4342	Site Foreman	Not Available
7	05	AFAQ AHMAD	1584	Site Supervisor	Not Available
8	06	DHAN BAHADUR CHET	3549	Timekeeper	Not Available
9	07	USMAN	1257	Plumber	Not Available
10	08	ISMAIL	5652	Electrician	Not Available
11	09	ABDULWAHAB	8986	Helper	Not Available
12	10	ABU TAHER	2829	Skilled Worker	Not Available
13	11	ANIL BABU PONNAYYAN	2586	Heavy Driver	Not Available
14	12	BISHNU PRASAD SHRESTHA	5758	Mason	Not Available
15	13	BUDDHI RAM DARAI	2858	Carpenter	Not Available
16	14	JAGADISH PRASAD	4276	Skilled Worker	Not Available
17	15	JIYA HUSHAIN MIYA	5798	Carpenter	Not Available
18	16	KARNA BAHADUR RAM	3268	Fabricater	Not Available
19	17	MAITA BAHADUR	5688	Welder	Not Available
20	18	MOHAMED ARSHATH	2689	Helper	Not Available
21	19	MOHAMMED KHALIL	6685	Mason	Not Available
22	20	MONSAR ALI	3829	Skilled Worker	Not Available
23	21	NAGESHWAR MAHATO	8988	Helper	Not Available
24
25
00

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:C23	Expression	=SEARCH("Other",$K3)	text	NO
A3:C23	Expression	=SEARCH("Office",$K3)	text	NO
A3:C23	Expression	=SEARCH("Yes",$K3)	text	NO
A3:C23	Expression	=SEARCH("Sub. Cont.",$K3)	text	NO
C3:C23	Cell Value	duplicates	text	NO
C3:C23	Cell Value	duplicates	text	NO

Exampl Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2				Job-Skills/Craft Training In House	Attend Date
3				Cranes & Rigging Safety	22-Oct-22
4				Electric Equip. & Power Tools	28-May-22
5	08	08
6		Name:	ISMAIL					VLOOKUP formula in cell # C6, 7 and 8
7		File #:	5652					With Cell # B5 (Manually added sheet #) is pulling data perfectly from Sheet 00, using VLOOKUP
8		Designation:	Electrician					With Cell # A5 (Systemically added sheet #) is not working
9
10		Office Cont:	0008649576
11		Emp. Cont:	Not Available
12
13		Employee Commitments:
14		Understand & Follow the Procedures
15		Attend Toolbox Talk & Safety Meeting
16		Report Unsafe Conditions, Near Miss, Incidents / Accidents
17		Practice of Safe Behavior
18		Follow Work Permit Procedures
19		Comply Safety Requirements
20
21		Record Maintained & Printed by:
22		Sajid Hussain, Safety Supervisor
23
24		Printed on:	29-Oct-22
25
08

Cell Formulas
Range	Formula
A5	A5	=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
B24,B21:B22,B13:B19,B11,B10:C10,B6:B8	B6	='00'!G6
C6	C6	=VLOOKUP(B5,'00'!$A$3:Table7[Name],2,0)
C7	C7	=VLOOKUP(B5,'00'!$A$3:Table7[File '#],3,0)
C8	C8	=VLOOKUP(B5,'00'!$A$3:Table7[Contractual Job Title],4,0)
C11	C11	=VLOOKUP(B5,'00'!$A$3:Table7[Employe Contact '#],5,0)
C24	C24	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D24	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D3:D24	List	='00'!$I$3:$I$35

 

[Alex Blakenburg]

I don't understand why you need the sheet name.
You are using Tables and you can access the table directly without referencing a sheet name.

Book1
	A	B	C
5		8
6		Name	ISMAIL
7		File #	5652
8		Designation	Electrician
9
10		Office Cont:	0
11		Emp. Cont	Not Available
Sheet1

Cell Formulas
Range	Formula
C6	C6	=VLOOKUP($B$5,Table7,2,FALSE)
C7	C7	=VLOOKUP($B$5,Table7,3,FALSE)
C8	C8	=VLOOKUP($B$5,Table7,4,FALSE)
C10	C10	='00'!H10
C11	C11	=VLOOKUP($B$5,Table7,5,FALSE)