Max

Final solution:

=VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32),Sheet2!A1:C5,3)

The "a1" after filename is automatically updating each sheet.

Thanks

Ernst

"Ernst" wrote:

Max, thanks for your help (spent the last two nights looking through MS
inside out).

This has formed the basis I can work from. I will need to develop the
formula a little further. To take advantage of this method I now add more
data to sheet2 for other depts eg dept21, dept22 etc and by copying the
Dept20 worksheet and renaming it dept21 the vlookup automatically picks up
the correct data from sheet2 for dept21.

Ernst

"Ernst" wrote:

I have a wookbook with two sheets, named "Dept20" and sheet2. On sheet2 I
have my raw data; Cell A1 "Dept20", Cell B1 4,500.
On sheet Dept20 I want to write a vlookup that can reference the data on
sheet2 by relating to the name of the active worksheet rather than typing
"Dept20" in the vlookup formula.

Any ideas?

Thanks

Ernst