Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Vlookup then interpolate,return corresponding value, if not, return other value Wzaman Excel Worksheet Functions 1 December 14th 10 01:17 AM
VLOOKUP - columnar sheet with multiple matches - need to return a dawgfan Excel Worksheet Functions 7 October 9th 09 02:09 AM
Vlookup and return the value from the look up value sheet Burmalion Excel Worksheet Functions 1 April 9th 09 04:39 PM
VLookUp - Does the VLookUp return the exact information? Cpviv Excel Worksheet Functions 2 October 28th 08 09:57 AM
VBA Syntax for VLOOKUP to return array of return values Alan Beban[_3_] Excel Programming 7 August 5th 03 11:41 AM


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

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017