Forum Discussion
Excel Formula Help
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.
thats awesome Sir! thank you so much for your help!
How would it look if i wanted to add a letter in front of the module number? cause i think i am going to have to a letter in front of them for example P73, C77 etc.