looking for macro to search workbook

[Stevesmail]

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

 

[Marc L]

Hello,​

​

no clue what could be any 'SDS sheet' …​

 

[Stevesmail]

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

 

[Marc L]

​

So according to this screenshot which columns must be used for the search ?​

 

[vw412]

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]

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]

​

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]

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]

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]

Good morning everyone. Just checking to see if there's any progress on this and if there's anything more I can provide. Thanks