Excel 2003 - referenceing the active worksheet page in vlookup

[Ernst]

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

 

[Max]

Here's one try ..

Assume you have

In Sheet2, in A1:C1
----------------------
Dept20 4500 9000

In sheet: Dept20
---------------------
(Workbook is assumed saved)

Put in any starting cell, say in C4:
=VLOOKUP(MID(CELL("filename"),FIND("]",CELL("filename"))+1,32),Sheet2!$A:$C,
COLUMN(B1),0)

Copy C4 across to D4

C4 and D4 will return 4500 and 9000

 

[Ernst]

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]

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

 

[Max]

Glad to hear that it helped!
Thanks for the feedback
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

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

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