Sheet Name + VLookUp

[VeryBasic]

Hi I have used the following to copy a sheet name into cell
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,1000)

Now I want to use the cell data (sheet name) to perform a Vlookup and return information. I keep getting an error because the cell data (sheet name) is not being recognized. How can I make the sheet name recognizable for the VLookup

Thanks

 

[Sixthsense]

Your current formula will arrive active sheet name as result.

What is the use of using the active sheet name in your vlookup() formula?

Since you can refer it without specifying the active sheet name.

 

[VeryBasic]

Hi Thanks for your reply and good question. I am coordinating an auction - on the day peopel will write their bid onto an individual bid sheet for each item. I have a master list with a item number, product description, value etc. I then need to create the "bid sheet" for each individual item. I thought that if I rename the sheet to the item number and then that will use Vlookup to find and the other details into the relevant cell. If there is a better way happy to hear it.

The idea was to copy the Bid sheet and rename the sheet to the next item number which will then enter the relevant info for that item

 

[Sixthsense]

Excel 2010
	A	B
1	Item Num	Name
2	2001	a
3	2002	b
4	2003	c
Master

Excel 2010
	A	B
1	Item Num	Name
2	2001	a
2001

Cell Formulas
Range	Formula
A2	=MID(CELL("filename"),FIND("]",CELL("filename"))+1,1000)
B2	=VLOOKUP(--A2,Master!A:B,2,FALSE)

 

[VeryBasic]

It works - however when I go back to the Master list and then back to the Bid sheet the Mid Cell formula changes and refers to the Master Sheet name and not the item sheet name?? How do I lock the Mid Cell formula so it will only read the sheet name and not change?

 

[Sixthsense]

Because cell() function is volatile which will get the last change cell sheet name as result.

To avoid this conflict you have to pass the same sheet reference in the next argument of cell function like this...

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,1000)

 

[VeryBasic]

Perfect thank you Sixthsense! You have saved a mountain of rewriting!