Remember Me?

#1
November 20th 12, 08:25 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2012 Posts: 168
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))&"'!A2200"),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))&"'!A2200"),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
December 21st 12, 05:55 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2012 Posts: 1
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))&"'!A2200"),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))&"'!A2200"),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
December 22nd 12, 12:03 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2012 Posts: 168
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))&"'!A2200"),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))&"'!A2200"),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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Wzaman Excel Worksheet Functions 1 December 14th 10 01:17 AM dawgfan Excel Worksheet Functions 7 October 9th 09 02:09 AM Burmalion Excel Worksheet Functions 1 April 9th 09 04:39 PM Cpviv Excel Worksheet Functions 2 October 28th 08 09:57 AM Alan Beban[_3_] Excel Programming 7 August 5th 03 11:41 AM

All times are GMT +1. The time now is 10:58 AM.