How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Michael Hannigan Your Own Question

Michael Hannigan
Michael Hannigan, Office Specialist
Category: Microsoft Office
Satisfied Customers: 11189
Experience:  25+ Years Experience in Computers and Electronics. Microsoft Certified MCSE, MCDBA, MCP, Microsoft Partner
23511658
Type Your Microsoft Office Question Here...
Michael Hannigan is online now
A new question is answered every 9 seconds

I have an excel workbook with 50 sheets that are all

Customer Question

I have an excel workbook with 50 sheets that are all formatted the same way. I want to create a summary sheet that looks for a specific value in a column and returns all of the sheet names that contain that specified value. I would like to avoid using VBA if possible.
Submitted: 1 year ago.
Category: Microsoft Office
Customer: replied 1 year ago.
I should mention that it doesn't need to return the sheet name if that is more difficult. The sheet name is ***** ***** value of cell A1 in each sheet, so it can return that value as well if that's easier.
Customer: replied 1 year ago.
More information if need be:
The workbook is designed as an inventory for all the dealers within my province as a way for us to track how many of our products are in the marketplace vs products of our competitors. It is something that we fill in monthly in our passing by these dealerships.
I have set up each sheet with drop down lists of products based on the brand that is selected. The sheet then categorizes the products into 4 different categories based on lists that I have named and whether to product selected is "counted" in that list.
I am wanting to build a sheet that shows where our products are, and how many are located of that type at which locations.
In all of the sheets, the "truckload augers" are categorized in column Q. The "swing away augers" are categorized in column R. These are the two categories that I want to track if we have any in our system, where they are, and then the quantity currently at that location (which is already in another column on each sheet).
For example, I want to look at all sheets in the range of column Q and see if the number 1 shows up. If it does, I want to return the value of A1 in all of the sheets that contained a number 1 in column Q.
Expert:  Michael Hannigan replied 1 year ago.

Hello. My name is***** can help you with your question.

This would actually be a simple task to perform with VLookup. This is an Excel function that allows you to look in a certain range of cells for a particular text string. If the string is found then the cell value of the corresponding column is returned. This is normally used for looking up something like a price on a sheet with part numbers and prices for example. You could search that range for the part number and get back the price that corresponds that part number. But you could also set up so that the only corresponding cell that gets returned is the one with the name of the sheet. Then it would either return that value or none at all. There are other ways you can do this with formulas as well. I generally prefer to use formulas if it can be done that way because they don't break. And you really don't need VBA to do this – you have several options.

Mike