I need to be able to search all open tabs in a workbook and sum the results. however the tabs change in qty and names so i cannot list the tab details spcifically but the search range remains the same across all pages/tabs. also i would need to search and total based on a few criteria. so basically i have 3 values in 3 cells i need to search for...only when all 3 values are met do i then need to sum a 4th cell value for all instances that match the search. I apologize if this is a little confusing. I've found a macro that will search all open workbooks but it doens't work if multiple returns match the search.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Search Multiple pages/tabs in a workbook and sum multiple results
0
Discussion
It's not confusing at all but it's hypothetical. And it's impossible to give a practical answer to a hypothetical question. Therefore, please go back to edit your post and attach a copy of your workbook from which you have removed private information. Based on the workbook, show us which 3 values must be collected, where to put the result, which further value must be found where, and where to put that result. Once you do all that an answer shouldn't be too hard.
Variatus (rep: 4889) Apr 5, '21 at 8:11 pm
Ok i've uploaded the file. so on the summary tab colum D is where the formula will be. the objective is to search all open tabs in the workbook. i'm wanting a conditional search, so if a match to value in A2 is found and B2 & C2 then i want the Qty(D2) to show a sum of the qty for all matches found. this needs to be a realtime search so that as i add sheets that additional matching data is updtated in the qty totals. for the sheets searching the layout will always be the same so search field only needs to look at b13:k50 on sheet1, sheet1(2) etc. hopefully this makes sense. please ask if you need more info
ahaywar Apr 7, '21 at 11:25 am
Answers
0
Here's the formula that will do the job. Paste it to D4 and copy down.
=SUMPRODUCT(SUMIFS(INDIRECT("'"&$G$2:$G$4&"'!$B$13:$B$50"),INDIRECT("'"&$G$2:$G$4&"'!$E$13:$E$50"),A2,INDIRECT("'"&$G$2:$G$4&"'!$H$13:$H$50"),B2,INDIRECT("'"&$G$2:$G$4&"'!$K$13:$K$50"),C2))However, before it can work, please enter a list of the sheets in the range G2:G4. There may not be any blanks in this range but you can make the list longer or shorter, provided you adjust the range in the formula. Of course, that brings us to two topics.
- Named ranges.
G2:G4 should be a dynamic, named range.- Create a named range, say "Sheets", assign the range G2:G4 to the name and then replace the explicit reference $G$2:$G$4 with the implicit reference Sheets.
- Then make the range the name refers to dynamic, meaning let it take the measure of what you write in that column and expand or shrink the last row of the range. In that way you wouldn't need to adjust the formula when the list grows or shrinks.
- Fill the range with available sheet names automatically.
It may be possible to create such a list using a worksheet function. The problem may be how to exclude the "Summary" sheet (and perhaps others) from the list either at input or output time. The list could also be produced using a macro.
As you see, the topic shifts from your original question to farflung and different subjects. It's your project and you must take control. See how far you get with the basic function I have provided above. Further adaptation to your project's needs, though clearly visible and listed above, can't fit into the Q & A format we are bound to here. Please ask other questions if you need more help.
Discussion
So I guess I’m a little confused as to how my scope of work changed. I'll try again to break down the need and make it clearer. I need to search across all pages in a workbook. page qty's and names will change frequently so I’m thinking a macro to search all open sheets within the workbook. Listing the page names will not work this is the first challenge. next I need to search all sheets and sum all matching results from the qty column of each sheet when the 3 search criteria are found in cell D2 of the summary sheet. this search across all sheets will have 3 different values that will need to match before being included in the sum count. in the sample file i attached these search field criteria was A2,B2,C2. once all three criteria are matched i need the qty value of each match on each sheet to sum together in D2 on the summary page. the idea is that i can change just the 3 search criteria on the summary page (A2,B2,C2) and get instant results across all open sheets within the workbook. I have found a macro that will search all open sheets but stops once the 1st positive result is found however it only searches for 1 search criteria. This macro does have a feature to exclude certain pages if listed into the formula. I will copy and past this macro next.
ahaywar Apr 8, '21 at 5:41 pm
Here is a link the the macro i've been trying to use
Free Excel Macro - Vlookup Function That Searches The Entire Workbook in Excel - UDF - TeachExcel.com https://www.teachexcel.com/free-excel-macros/m-123,vlookup-udf-excel-macro-workbook-worksheets-entire.html#
Free Excel Macro - Vlookup Function That Searches The Entire Workbook in Excel - UDF - TeachExcel.com https://www.teachexcel.com/free-excel-macros/m-123,vlookup-udf-excel-macro-workbook-worksheets-entire.html#
ahaywar Apr 8, '21 at 5:46 pm