Forum Discussion
Excel Formula Help
Here's one way to get that data to your separate sheets. I only did it with the first (MRD ABC123) because my first recommendation would be to "un-prettify" your output sheets until you get them working. You can always add color and other whiz-bang details later, but doing that up front can impede simple functionality.
The way I would extract the data is by means of the FILTER function. It's one of what are called Dynamic Array functions, but it doesn't work--you'll get error messages--putting the output into an Excel Table (as you'll experience if you try putting the comparable formulas into the other sheets).
the FILTER function is very powerful in extracting data from an array such as your raw data is. If you look closely here, you'll see that there are only two cells into which I typed a formula. The dynamic array functions fill adjacent and following cells as needed. That effect is called "spilling" and it is blocked in a table. One formula would have worked had all the output data been contained in a single array of cells, but that one column was off by itself.
There too, though, you've made that raw data unnecessarily pretty. You don't need to, for heaven's sake. It's raw data. Leave it plain. You don't need all those empty rows at the bottom.
Another suggestion: Unless you specifically NEED the separate output sheets, you could easily create a single output sheet (they're called "dashboards") that extracts the relevant data for the different departments based on a variable input in a drop down selection. Then you could show whichever one you want simply by selecting "MRD ABC123" or one of the other designations.
One of us can show you how to do that if it would make sense in your situation.
Hi mathetes​ thank you so much for the help and the explanation!! This is the exact formula i was getting help from others on here but it wasnt working and now that you explained it, it was because i was entering that formula on a table. haha and i get what you are saying about making it look nice haha. this sheet has been used prior but i got tired of inputting everything myself. so this sheet was created before i wanted to automatically calculate everything lol. i would love to learn about dashboards as well.
This is for sure not the most elegant solution. This mainly serves to demonstrate how a single page can be used to summarize data from different districts by changing a variable using the drop down data validation method.
I know there can be a more dynamic array version of the formula in the first column, but my knowledge of the dynamic array functions is still growing. So I used a combination of INDEX and MATCH to access that Module data from your raw data set. If I have time later today, I'll do some research on how to consolidate that two dimensional array into a single column and then access that as a one-dimensional array, all within a single formula.
In the meantime, this demonstrates the power of a single dashboard to simplify reporting using different, variable-based, retrievals from a single all-inclusive database.
I like that! thank you so much Sir. ill have to play with it some more but it does get rid of other tabs. So ill see what makes more sense for me. Thank you again for this!
I do have 2 questions:
- whats the budget data and budget dollars used for on the tables tab?
- I also noticed that some module numbers dont show up on the Dashboard tab. they show 0
Here's the "better example." I figured out how to extract those module numbers into a single array (the BYROW function) and then could access those for that first column.
However, some of the numbers in that section were stored as text, and as such didn't work. I had to convert them to numbers. Don't know how the originals originate, but that's something you need to pay attention to. Also, if they could just come to you as a single column in the first place...
To your question about those budget dollars---I just put those in because in your sample spreadsheet you clearly have something along those lines--you'd deleted any actual values from your sheet. I just entered some arbitrary values. Look at the top line in the output (Dashboard)... you'll see they appear there, different values for each district.