VLOOKUP and Sheet Name

Sajid Hussain

New Member
Joined
Oct 29, 2022
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFKL
1
2NoNameFile #Contractual Job TitleEmploye Contact #
301RIMSAN2498ElectricianNot Available
402SAYED5689HelperNot Available
503HUSSAIN1284Safety SupervisorNot Available
604ABDUR RAZZAQ4342Site ForemanNot Available
705AFAQ AHMAD1584Site SupervisorNot Available
806DHAN BAHADUR CHET3549TimekeeperNot Available
907USMAN1257PlumberNot Available
1008ISMAIL5652ElectricianNot Available
1109ABDULWAHAB8986HelperNot Available
1210ABU TAHER 2829Skilled WorkerNot Available
1311ANIL BABU PONNAYYAN2586Heavy DriverNot Available
1412BISHNU PRASAD SHRESTHA5758MasonNot Available
1513BUDDHI RAM DARAI2858CarpenterNot Available
1614JAGADISH PRASAD4276Skilled WorkerNot Available
1715JIYA HUSHAIN MIYA5798CarpenterNot Available
1816KARNA BAHADUR RAM3268FabricaterNot Available
1917MAITA BAHADUR5688WelderNot Available
2018MOHAMED ARSHATH2689HelperNot Available
2119MOHAMMED KHALIL6685MasonNot Available
2220MONSAR ALI3829Skilled WorkerNot Available
2321NAGESHWAR MAHATO8988HelperNot Available
24
25
00
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:C23Expression=SEARCH("Other",$K3)textNO
A3:C23Expression=SEARCH("Office",$K3)textNO
A3:C23Expression=SEARCH("Yes",$K3)textNO
A3:C23Expression=SEARCH("Sub. Cont.",$K3)textNO
C3:C23Cell ValueduplicatestextNO
C3:C23Cell ValueduplicatestextNO



Exampl Sheet.xlsx
ABCDEFGHIJK
1
2Job-Skills/Craft Training In HouseAttend Date
3Cranes & Rigging Safety22-Oct-22
4Electric Equip. & Power Tools28-May-22
50808
6Name:ISMAILVLOOKUP formula in cell # C6, 7 and 8
7File #:5652With Cell # B5 (Manually added sheet #) is pulling data perfectly from Sheet 00, using VLOOKUP
8Designation:ElectricianWith Cell # A5 (Systemically added sheet #) is not working
9
10Office Cont:0008649576
11Emp. Cont:Not Available
12
13Employee Commitments:
14Understand & Follow the Procedures
15Attend Toolbox Talk & Safety Meeting
16Report Unsafe Conditions, Near Miss, Incidents / Accidents
17Practice of Safe Behavior
18Follow Work Permit Procedures
19Comply Safety Requirements
20
21Record Maintained & Printed by:
22Sajid Hussain, Safety Supervisor
23
24Printed on:29-Oct-22
25
08
Cell Formulas
RangeFormula
A5A5=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
B24,B21:B22,B13:B19,B11,B10:C10,B6:B8B6='00'!G6
C6C6=VLOOKUP(B5,'00'!$A$3:Table7[Name],2,0)
C7C7=VLOOKUP(B5,'00'!$A$3:Table7[File '#],3,0)
C8C8=VLOOKUP(B5,'00'!$A$3:Table7[Contractual Job Title],4,0)
C11C11=VLOOKUP(B5,'00'!$A$3:Table7[Employe Contact '#],5,0)
C24C24=TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:D24Cell ValueduplicatestextNO
Cells with Data Validation
CellAllowCriteria
D3:D24List='00'!$I$3:$I$35
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
ABC
58
6NameISMAIL
7File #5652
8Designation Electrician
9
10Office Cont:0
11Emp. ContNot Available
Sheet1
Cell Formulas
RangeFormula
C6C6=VLOOKUP($B$5,Table7,2,FALSE)
C7C7=VLOOKUP($B$5,Table7,3,FALSE)
C8C8=VLOOKUP($B$5,Table7,4,FALSE)
C10C10='00'!H10
C11C11=VLOOKUP($B$5,Table7,5,FALSE)
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top