This post from Tom Urtis reminded me of a crazy Excel puzzle I once solved. The concept was similar except for one tiny twist! First of all, let’s review the original puzzle from Tom:
In sheet ‘Summary’ Tom wants a way to view data from one of the four regions. Tom’s solution is based on Data Validation and the Indirect function.
Notice how the data in each different sheet is located in exactly the same location. Read Tom’s post.
What’s The Twist?
The data is in a different location in each sheet! Should we politely insist that the data be rearranged to fit into the exact same location in each sheet? In a perfect world that would be ideal. A further twist was the the amount of rows varied between sheets and new sheets could be added at any time!
Is A Solution Possible?
Data and formulas were all over the place and we needed a way to extract only this specific summary information. There were already about 50 sheets and additional sheets could be added at any time. Just a slight twist right? Is it worth the time to consider a solution? Where would we start?
What’s The Pattern?
It almost seems like a lost cause….but wait…
Is there a consistent pattern across all sheets? (I’ll use Tom’s data)
If the word “Item” is found in a cell followed to the right by “Qtr 1” followed by “Qtr 2” then we’ve found the top part of our data!
What about the last row as the number of rows varies between sheets?!?
We expect to see the word “Total” followed to the right by formulas that start with “=Sum“. We also need a way to check and see if there is another area that follows the same pattern (hopefully not but 1 array formula can tell us).
(read the solution below or if you’re like me scroll down and grab the Excel file to see how it all works!)
Solution!
Let’s look for our column headers.
Assumptions
- 3 conditions are enough (looking for ‘Item’, ‘Qtr 1’, ‘Qtr 2’). We could add ‘Qtr 3’, ‘Qtr 4’ & ‘Total’
- The spelling of ‘Item’, ‘Qtr 1’, etc must be consistent across the sheets
- Searching a smaller range (A1:CL5000) is sufficient. We could increase ranges if necessary
STAGE 1: We’ll start with the array formula below. It works only on sheet ‘North Region’. Once we’ve tested this we’ll replace the sheet references with the INDIRECT function that will point to the sheet name (selected from a drop down list).
{=MIN(IF((‘North Region’!$A$1:$CL$5000=B$2)*(‘North Region’!$B$1:$CM$5000=C$2)*(‘North Region’!$C$1:$CN$5000=D$2),ROW(‘North Region’!$A$1:$CL$5000),””))}
What does this array formula do? It gives us the row number where this pattern is found (we also have a very similar formula that gives us the column number as we need both coordinates). In sheet ‘North Region’ we find our column headers starting in cell J12 (row 12 and column 10).
Notice how each range slides one cell to the right. $A$1:$CL$5000 –> $B$1:$CM$5000 –> $C$1:$CN$5000
This is important as we’re looking for ‘Item’ followed one cell to the right by ‘Qtr 1’ and another cell to the right ‘Qtr 2’.
Now we look for the total row.
Yes, once we know where the column headers are we could simply drag down Index functions to extract our data and stop once we see the ‘Total’. However, with 1 more array formula we can dynamically determine the ‘stop’ row (where our totals are).
{=MIN(IF((‘North Region’!$A$1:$CL$5000=B$6)*(IFNA(LEFT(FORMULATEXT(‘North Region’!$B$1:$CM$5000),4)=”=SUM”,FALSE)),ROW(‘North Region’!$A$1:$CL$5000),””))}
Similar to the first formula except I’m using Excel’s Formulatext and Left functions to search within the formula text!
If we find the word Total followed to the right by formula text =SUM then we can assume that we’ve dynamically found the last row.
Once again, we could add another condition looking for =SUM in the next cell to the right as well.
STAGE 2: Now we replace the hard coded sheet reference ‘North Region’ using Excel’s INDIRECT function to build the sheet reference (we have a drop down list in which we select the sheet name). Remember that the requirement is that our data could be anywhere in any sheet!
=MIN(IF((INDIRECT(“‘”&$B$31&”‘!$A$1:$CL$5000”)=B$32)*(INDIRECT(“‘”&$B$31&”‘!$B$1:$CM$5000”)=C$32)*(INDIRECT(“‘”&$B$31&”‘!$C$1:$CN$5000″)=D$32),ROW($A$1:$CL$5000),””))
Finally…we combine INDEX and INDIRECT functions to extract the data! Simple counters to the left and above make it a bit easier.
=IF($A45=””,””, INDEX(INDIRECT(“‘”&$B$31&”‘!$A$1:$CL$5000”),$B$33+$A45,$B$34+B$43))
Final Thoughts
My formulas definitely aren’t super easy but it’s still better than manually searching for the summary data in each of the sheets. Also, please keep in mind that this is more of an emergency solution. Array formulas can be slow to calculate. When used sparingly they are amazing as they can answer complex questions!
The best long term solution would be to have all of the data re-arranged into a single sheet. Then the formulas would be lighter and a pivot table could be used. If this isn’t possible then at least standardize the layout structure in all of the different sheets.
Download My Excel file
Download here or via my OneDrive (filename ‘Extracting Data That Could Be Anywhere In Any Sheet‘)
About Me
My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.
Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and blog posts.
Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.