Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel 2003 - referenceing the active worksheet page in vlookup
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 |
#2
|
|||
|
|||
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 -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- "Ernst" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
import .CSV file to XP EXcel 2003 worksheet, the seperator is com. | New Users to Excel | |||
why would page 1 of an excel worksheet print smaller of page 2 if. | Excel Discussion (Misc queries) | |||
Excel 2003 crashes when I close any worksheet | Excel Discussion (Misc queries) | |||
using a reference in vlookup with excel 2003 | Excel Worksheet Functions |