Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and return sheet name also
Using Excel 2010
Using the first array-entered formula below works perfect to return a value from one of eight sheets contained in the Named Range "MySheets". (Minus Peo's name) VLOOKUP(F1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),F1)0) ,0))&"'!A2:D200"),3,0) -Peo Sjoblom I can return the sheet name along with the original return value by appending the first formula with this second one. Note that I now go to column 4 for the return value and all of column 4 on every sheet is that sheet's name, repeated 200times. Kinda messy. &VLOOKUP(F1,INDIRECT("'"&INDEX(MySheets,MATCH(1 ,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),F1)0) ,0))&"'!A2:D200"),4,0) This next formula returns a sheet's name, stripped of the full path. I can only make it work on the same sheet that the formula in entered. Is there a way to include this formula with the first vlookup formula so it will know to return the sheet name where the lookup value was found, be it sheet1 to sheet8? This would then unclutter the eight sheet of 200 names on each sheet. MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,LEN(CELL("FILENAME",A 1))-FIND("]",CELL("FILENAME",A1))) -Chip Pearson site Thanks. Regards, Howard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and return sheet name also
On Tuesday, November 20, 2012 3:25:16 AM UTC-5, wrote:
Using Excel 2010 Using the first array-entered formula below works perfect to return a value from one of eight sheets contained in the Named Range "MySheets". (Minus Peo's name) Where you ever able to solve this? I am facing the same problem... VLOOKUP(F1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),F1)0) ,0))&"'!A2:D200"),3,0) -Peo Sjoblom I can return the sheet name along with the original return value by appending the first formula with this second one. Note that I now go to column 4 for the return value and all of column 4 on every sheet is that sheet's name, repeated 200times. Kinda messy. &VLOOKUP(F1,INDIRECT("'"&INDEX(MySheets,MATCH(1 ,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),F1)0) ,0))&"'!A2:D200"),4,0) This next formula returns a sheet's name, stripped of the full path. I can only make it work on the same sheet that the formula in entered. Is there a way to include this formula with the first vlookup formula so it will know to return the sheet name where the lookup value was found, be it sheet1 to sheet8? This would then unclutter the eight sheet of 200 names on each sheet. MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,LEN(CELL("FILENAME",A 1))-FIND("]",CELL("FILENAME",A1))) -Chip Pearson site Thanks. Regards, Howard |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and return sheet name also
On Friday, December 21, 2012 9:55:53 AM UTC-8, wrote:
On Tuesday, November 20, 2012 3:25:16 AM UTC-5, wrote: Using Excel 2010 Using the first array-entered formula below works perfect to return a value from one of eight sheets contained in the Named Range "MySheets". (Minus Peo's name) Where you ever able to solve this? I am facing the same problem... VLOOKUP(F1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),F1)0) ,0))&"'!A2:D200"),3,0) -Peo Sjoblom I can return the sheet name along with the original return value by appending the first formula with this second one. Note that I now go to column 4 for the return value and all of column 4 on every sheet is that sheet's name, repeated 200times. Kinda messy. &VLOOKUP(F1,INDIRECT("'"&INDEX(MySheets,MATCH(1 ,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),F1)0) ,0))&"'!A2:D200"),4,0) This next formula returns a sheet's name, stripped of the full path. I can only make it work on the same sheet that the formula in entered. Is there a way to include this formula with the first vlookup formula so it will know to return the sheet name where the lookup value was found, be it sheet1 to sheet8? This would then unclutter the eight sheet of 200 names on each sheet. MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,LEN(CELL("FILENAME",A 1))-FIND("]",CELL("FILENAME",A1))) -Chip Pearson site Thanks. Regards, Howard Yes, I have a working solution (workaround) which may not be suitable to all. I procceded all possible return values on Sheet1 to Sheet5 with A, B, C, D, E respectively. When a value is returned I used a worksheet change event to seperate the sheet code letter (A...E) to a cell and the "real" return value to another cell. Then a simple vlookup using the A to E cell as the lookup value against sheet1 to sheet 5 table array to return the sheet name to the cell adjacent the "real" return value. So you have the proper return value and the sheet name next to each other. If anyone wants a copy, to look over and make any suggestions I'm glad to send you one. I believe my e-mail address is present somewhere in this thread as someone has already contacted me privaely. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Vlookup then interpolate,return corresponding value, if not, return other value | Excel Worksheet Functions | |||
VLOOKUP - columnar sheet with multiple matches - need to return a | Excel Worksheet Functions | |||
Vlookup and return the value from the look up value sheet | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
VBA Syntax for VLOOKUP to return array of return values | Excel Programming |