looking for macro to search workbook

Stevesmail

New Member
Joined
May 3, 2021
Messages
10
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello

I thought I would ask you good people a question on a workbook I'm developing for my place of employment. We have many SDS sheets for the many chemicals we have at the workplace. The company didn't have very good organization with the sheets which can be a safety and fire hazard because of not finding SDS in good time.

I offered to organize these and so I'm developing an excel workbook. In the workbook, I'm going to have several tabs. These SDS sheets will be organized based on manufacturer of the chemicals. I have column A with the mfg name, 2nd column has chemical name and the last column will have a hyperlink to the SDS file on the company server. The tabs will be set up as A, B, C, etc

What I've been looking for is a Macro that can do a search in the whole workbook for the manufacture name or chemical name which is in the first 2 columns. First tab will the search page where I'll put a search button and clear button on the page. What I'm wanting is for a person to either look for the SDS by clicking on the tab corresponding to the manufacturer and scroll through the manufacturers to find what they are looking for or go to the search page and type in the manufacture name or chemical name and either go to the cell automatically from the search in the workbook or bring a copy of the searched line onto the search page which will be faster to access the SDS. I haven't found a macro to do what I need it to do yet and thought someone here could guide me in the right direction. Thanks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
Hello,​
no clue what could be any 'SDS sheet' …​
 

Stevesmail

New Member
Joined
May 3, 2021
Messages
10
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello,​
no clue what could be any 'SDS sheet' …​
Hi Marc

SDS sheets are the same as MSDS sheets. The SDS sheets are irrelevant in my question though in what I'm wanting the spreadsheet to do with a Macro. Access to the SDS (or MSDS) would be available to employees via a hyperlink to the company server. Here is a screenshot of the layout of the workbook. As you can see I have tabs. When the workbook is done, it'll have tabs Search, 123, A-Z. From the search page, I want to have the search macro to go directly to a company or chemical name cell in whatever tab that information lies. On the far right of the spreadsheets are the hyperlinks to the company server. Thanks for your help :)
sds screenshot 5321.png
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
So according to this screenshot which columns must be used for the search ?​
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows

ADVERTISEMENT

Trying to understand your workbook design. The Search tab would initiate a search (by either manufacturer or chemical), correct? Would the search be across all the other tabs? Are the alphabetic tabs lists of manufacturers sorted by name? Or would they have both manufacturer and chemical name? What is the purpose of the 123 tab?
 

Stevesmail

New Member
Joined
May 3, 2021
Messages
10
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Trying to understand your workbook design. The Search tab would initiate a search (by either manufacturer or chemical), correct? Would the search be across all the other tabs? Are the alphabetic tabs lists of manufacturers sorted by name? Or would they have both manufacturer and chemical name? What is the purpose of the 123 tab?
Hi VW12. Thanks for trying to understand. Yes the search tab would have a search button which I know how to do. The search button would have a macro tied to it to do a search in either column A or C in all of the tabs. I want an employee to either search for the manufacturer or search for the chemical name. I would like that search to either take the person to either go to the specific cell of either the mfg or chemical name or copy that line onto the search tab where they can just simply click onto the hyperlink and go to the SDS. The search would go across all tabs. The tabs are lists of manufacturers sorted alphabetically by name. Column A is the manufacturer and Column B is the chemical name. Column E is the hyperlinks. The purpose of the 123 tab is for manufacture names that start with a number, for example "3M". Hope this all helps.
 

Stevesmail

New Member
Joined
May 3, 2021
Messages
10
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

So according to this screenshot which columns must be used for the search ?​
Hi Marc. As I mentioned to VW12, I would like the employee to search for an SDS either by the manufacturer or by chemical name. I would have 2 buttons set up by manufacturer or by chemical name. So to answer your question, column A for manufacturer and column C for chemical name. An employee might not know the manufacturer so I would like to give them the option of chemical name if at all possible. So this would probably take 2 macros for each button I think. Thanks for your help with this
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
Another question, how big is this SDS data set? Are there thousands or hundreds or what? Wanting to consider several directions for designing this. I am not so sure this can be done without VBA but that is the direction I usually lean any.
 

Stevesmail

New Member
Joined
May 3, 2021
Messages
10
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Another question, how big is this SDS data set? Are there thousands or hundreds or what? Wanting to consider several directions for designing this. I am not so sure this can be done without VBA but that is the direction I usually lean any.
Hi VW412. Not sure of the exact count of sds,but it would be hundreds
 

Stevesmail

New Member
Joined
May 3, 2021
Messages
10
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Good morning everyone. Just checking to see if there's any progress on this and if there's anything more I can provide. Thanks
 

Forum statistics

Threads
1,147,568
Messages
5,741,874
Members
423,692
Latest member
Bhanu1988

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top