Hi all,
I have an Excel 2010 file which consists of 150+ sheets. I have 1 sheets which contains a column with values that could be in one or more of the other sheets.
What I need is a script or function that searches the values in the column in all the other sheets and returns the name of the sheet or sheets where the value has been found.
Any help is very welcome!
Bart
Last edited by evolv12; 11-21-2011 at 06:13 AM.
That description is very vague. You need to describe the layout of the other sheets and the location(s) where this "value" might be found. A workbook with several rows of sample values in column one of the source sheet and maybe two or three samples of the sheets to be searched.
Ben Van Johnson
Here is an example of what I'm facing. The attached file contains an overview sheet with products and their locations and multiple sheets with products. I need a script or function that fills the location column on the overview sheet based on the sheets names that contain the product IDs.
hi, evolv12, please check attachment, run code "test"
Can it happen that product can be found on a multiple sheets or just one?
Last edited by watersev; 11-21-2011 at 05:45 AM.
try this for start
Please Login or Register to view this content.
Last edited by tom1977; 11-21-2011 at 06:46 AM.
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
if Product can be found on multiple sheets, it can be done this way around
Please Login or Register to view this content.
Last edited by snb; 11-21-2011 at 06:37 AM.
This works perfectly! Thank you so much!
Sorry for bringing up a rather old thread, however I figured it would be better than starting a new one.
The above excel spreadsheet posted by watersev was tremendously helpful, however I was hoping someone could explain how to tweek something slightly. In watersev's attachement, the code searches only for EXACT cell matches. I was hoping someone could guide me towards manipulating the code to find CONTAINS instead.
For instance, I want a search for "BB" to return 104BB4 and BB243. In this worksheet, you would have to search for the exact term.
Thank you!
Welcome to the Forum, mougiasm,
unfortunately: Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks