Use filename result as match lookup value

echo2xray

New Member
Joined
Oct 14, 2015
Messages
19
I have a sheet named "Tracker" with about 80 items and columns that go from A to AA. My intention is to create a template that will populate basic information from the tracker sheet. Each item is numbered in the A column and I plan to add 80 additional sheets all named "1", "2", "3" etc... Once I have the first sheet working.

So, I have =MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,5) in cell A1 on my sheet named "1". Then I added =INDEX(Tracker!W3:W82,MATCH(A2,Tracker!A3:A82,0)) to pull in the name from the W column. I get #N/A result. If I type "1" instead of using the filename formula it gives a good result.

I am trying to avoid typing on each sheet, is there a way to automate this so when I add a sheet and name it "2" it will pull in the information for the next item?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I have =MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,5) in cell A1 on my sheet named "1". Then I added =INDEX(Tracker!W3:W82,MATCH(A2,Tracker!A3:A82,0))

There is an issue of A1 or A2 but perhaps that is just a typo in your forum question?

Most likely the problem is that your formula is producing a text value of "1" whereas in column A of Tracker you have numerical values.

Try adding '+0' to your A1 or A2 formula like this to convert those text numbers to numerical values.

Excel Formula:
=MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,5)+0
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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