Sheet Name into a Cell

[raccoon588]

i have this formula

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

It brings in the name of the sheet perfectly. But when i use that cell as a source of data for other formulas it does not work. It doesn't seem to be able to read the value in the cell. Example: the sheet is called 1. I've pulled that into cell C2 using the above formula. Now i enter a vlookup formula into another cell and use C2 as the value i am looking up. This does not work. Is there a fix for this?

 

[steve the fish]

It would work. What formula are you using?

 

[decadence]

I believe you need to make that cell (C2 in this case) a value instead of a formula

Try this

[c2].Value = [c2].Value

Or Perhaps if you are trying to get the sheet name into a Cell

Use this

 [c2].Value = ActiveSheet.Name

 

[raccoon588]

Im using this

=IFERROR(VLOOKUP(C2,Dashboard!A6:K71,3,FALSE),"")

if i use this =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) to pull in the sheet name "1" my vlookup doesnt work. if i type a 1 into the cell manually the vlookup works fine.

 

[decadence]

The filename cell needs to be a value instead of formula, when you type 1 in the cell you are putting a value in that cell, Copy and paste values for the filename formula and your vlookup will work.

 

[jproffer]

It could be it's leaving extra spaces. Try trimming that formula result...like this:

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

I didn't try a VLOOKUP with it, but it worked within a formula

 

[steve the fish]

Ok. The original formula will be producing a text value even if it's a number. Vlookup won't see a textual number and a real number as the same thing. You can just add zero to c2 if it's always a number.

 

[steve the fish]

This should fix it.


=IFERROR(IFERROR(VLOOKUP (C2+0,Dashboard!A6:K71,3,FALSE),VLOOKUP(C2,Dashboard!A6:K71,3,FALSE)),"")

 

[raccoon588]

I need it to be dynamic. This will be a master Sheet. People will make copies of this sheet and rename the sheet with a number, That number will then need to populate cell C2 and be usable as a number.

 

[raccoon588]

This works perfect, thank you