Help with Vlookup refrensing number that is assigned to sheet name

[benntw]

I have a really weird issue. In my file each sheet name is a 5 digit project number. On the sheet I have this formula to show the sheet name " =MID(CELL("filename",B3),FIND("]",CELL("filename",B3))+1,255) " . This displays correctly.

I have a Projects sheet that I have all the project numbers with the names of the projects next to them. What I am trying to do is do a vlookup from the sheet name in the cell and using the projects sheet as the range to give me the project name. It returns just an empty cell. I tried using text instead of a project number and it worked. But using numbers formatted as general or numbers does not work. I even tried =IFERROR(VLOOKUP(TEXT($B3, "#.#0"),'Projects'!A$2:$D$600,4,FALSE),"") and this does not work. Anyone no how to fix the format so a vlookup can recognize it ?

The reason I am going through this headache is I have a macro to add a new project. I put the number in the msg box and hit enter and it copies the template sheet and renames it with that project number. On the spreadsheet I have references to that project number to populate certain columns to cut down on the amount of hand entries for that code.

Hopefully someone can help me out with this.

 

[DushiPunda]

Can you provide us with a sample of your data? You can insert a table (in the Advanced view (click Go Advanced) to make it look exactly like your data looks. Should be able to help out more.

	1	2
A	Your	Data
B	Goes	Here

<tbody>
</tbody>

 

[benntw]

My sheet is not formatted as a table. Columns A - D has all the look ups. My issue is when I put text in the columns it finds it on a vlookup. If I use numbers it will not find it. That is why I tried the text formula in the vlookup to see if it would work and it did not.

This formula puts the name of the tab into a cell - =MID(CELL("filename",B3),FIND("]",CELL("filename",B3))+1,255). This displays my project number by tab just fine and when I use this to vlookup from it , it will work if it is a word , but not a number.

 

[DushiPunda]

It's not formatted as a table? I'm not sure why you're telling me that, but these forums provide the functionality to input data in the form of a table. It doesn't matter how your sheet is formatted. If you have data on the sheet, then you can emulate your sheet using this functionality.

For example, if you have data in Columns A-D, as you say you do...then show us an example of the data like so:

	A	B	C	D
1	Your	Data	Goes	Here
2	Your	Data	Goes	Here

<tbody>
</tbody>

1. What is in cell B3?
2. What does your "=MID(CELL("filename",B3),FIND("]",CELL("filename",B3))+1,255)" formula return?
2a. If it doesn't return the expected value, what are you expecting it to return?
3. What does your VLOOKUP formula return?
3a. If it doesn't return the expected value, what are you expecting it to return?

Take the data (or simulated data) and put it into a table (as I illustrated above) and show us. Give us expected results and actual results. It is extremely hard to help you without seeing your data.

To quote Hiker95 on another post:

alinawaz,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data worksheets, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

Also, the MrExcel HTML Maker might make it a little easier for you to show us your data:

https://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

 

[Aladin Akyurek]

Does one the following work for you?

1.

=IFERROR(VLOOKUP(REPLACE(CELL("filename",$B$3),1,FIND("]",CELL("filename",$B$3)),""),'Projects'!A$2:$D$600,4,0),"")

2.

=IFERROR(VLOOKUP(REPLACE(CELL("filename",$B$3),1,FIND("]",CELL("filename",$B$3)),"")+0,'Projects'!A$2:$D$600,4,0),"")

 

[benntw]

Number 2 works perfect. Thank you very much.

 

[Aladin Akyurek]

Number 2 works perfect. Thank you very much.

You are welcome. Thanks for the update.