Glad to hear that it helped!
Thanks for the feedback
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"Ernst" wrote in message
...
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