Forum Discussion
Excel Formula Help
Hi everyone again, so I am trying to pull data from one tab (DATA) to another tab (MRD ABC123, SLRD ABC123, etc..) there are examples in the first 2 tabs (MRD & SLRD ABC123) of how i am trying to pull this data over. I have tried formulas and have had some help on here as well to try some formulas but nothing is wanting to work.
The data on the DATA tab is being copied and pasted from a Microsoft Forms. the columns i want to transfer the data to their perspective tabs is in Orange (Column F - R)
Any help on this would be greatly appreciated.
I have attached the spreadsheet below
3 Replies
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.
