Keeping Stats across multiple sheets, including potential new sheets being added each week
Our take
Managing data across multiple sheets can quickly become a cumbersome task, especially when dealing with dynamic datasets like tracking Wins, Losses, and Draws for multiple players across various practices and games. The scenario presented by the user highlights a common struggle many face when scaling spreadsheet operations. As they attempt to manually update formulas to incorporate new sheets, the initial promise of efficiency fades into a tedious routine. This issue is echoed in discussions around optimizing spreadsheet management, such as in articles like excel with a lot of data - how to solve it and Why are my formulas 0?.
The user’s current approach, relying on SUMIF formulas that reference each practice sheet individually, is a classic case of spreadsheet overload. While it works, it lacks the scalability and flexibility that modern data management tools can provide. Each time a new practice sheet is added, the burden of updating dozens of formulas can outweigh any time saved through the initial setup. Recognizing the limitations of traditional spreadsheet methods is key in moving towards a more efficient solution. Techniques such as the Book End method or the use of Named Ranges can transform how users interact with their data, allowing them to focus on insights rather than repetitive tasks.
Implementing a more streamlined solution not only saves time but also reduces the potential for errors. For instance, by using Named Ranges, users can create dynamic references that automatically include new sheets without the need for constant adjustments. This method enhances clarity and reduces clutter, making it easier to maintain an accurate overview of the data. Additionally, exploring tools specifically designed for multi-sheet data management can simplify this process. As the landscape of spreadsheet technology evolves, users are encouraged to embrace these innovative solutions that prioritize efficiency and user experience.
The broader significance of this development lies in the shift towards smarter, more agile approaches to data management. As organizations increasingly rely on data-driven decisions, the need for accessible yet powerful tools becomes paramount. This shift opens up opportunities for new technologies that can handle complex data scenarios with ease. For example, integrating artificial intelligence with spreadsheet functionalities could revolutionize how users analyze and visualize their data, moving beyond the constraints of traditional formulas.
Looking ahead, it’s essential for users to remain open to exploring these transformative solutions. As data continues to grow in complexity, the tools we use must adapt accordingly. The question that emerges is: how can we further empower users to transcend the limitations of outdated practices and leverage innovative technologies to enhance productivity and decision-making? Embracing this evolution will not only streamline workflows but also foster a more engaging and productive data environment for all.
I'm trying to create a sheet that tracks Wins/Losses/Draws across 15 players, with multiple practices and multiple games within those practices.
So far I have a new sheet for each practice titled "Practice1" "Practice2", etc. within these sheets I have 4 columns, Name, Win, Loss, and Draw. In the A column are the players names, and because there are multiple games with different partners and opponents each week, their names show up multiple times, and in different lines each sheet. Currently I have a sumif formula in a "Standings" sheet, which for the Wins section in Column B looks like
=SUMIF(Practice1!A1:A50,"NAME",Practice1!B1:B50)+SUMIF(Practice2!A1:A50,"NAME",Practice2!B1:B50)+SUMIF(Practice3!A1:A50,"NAME",Practice3!B1:B50)
I have this set up for each player name, as well as for certain activities we do frequently, which I keep within specific ranges in each sheet for consistency, so with 15 players and 5 different activities I have 75 similar formulas going just for the Wins column.
The issue is every practice I have to add a sheet, which means I have to update each formula to include the new sheet, so it ultimately isn't saving me that much time. Is there a smoother more efficient way for me to do this? I've read a bit about Book End technique and Named Ranges but having some trouble wrapping my head around it.
Please let me know if there's anything I can clarify or additional info that may be helpful. Thank you!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Keeping a running total of data from one sheet, in anotherHi all. I'm after some help, or pointers as I couldn't think of the correct search terms to find out what I want to do. I have an Excel (M365) spreadsheet (sheet 1) which currently has about 1500 lines of data on it, with another 100 or so being added every month. On each line, there are 30 different bits of data. Most of this data will never change once it has been added, but there are about 6 fields on each line which MUST be updated every 4 months due to expiry. Sadly, due to the data source there is no way to do this automatically or via an API, so I need to check it manually. I also need to keep a running tally of some of the data that is in the sheet. For each line, the main fields that I'm interested in are the manufacturer (1 of 67), the model number (unlimited to a certain extent), serial number, two different locations (both 1 of 48), and a few others where the criteria is effectively yes or no There will also be a not known option for each. Due to the data, there is also some duplication which is required and we don't want the previous record to be overwritten. Sometimes there will be two records relating to the same item, sometimes more. For instance, we will have a record from the first occasion an item is seen, and then duplicates for every other occasion. Most of the data on the duplicate records will be the same but the initial date of entry will be different, along with a reference number. On another sheet (sheet 2) within the same workbook, I have a summary of the data, all of which is manually calculated. It includes the total number of records, the number of unique records, the number of records which are duplicates (i.e. the serial number appears twice or more), the number of items that have a particular result in one of the fields (ie. field yes/no/unknown) I also have the manufacturer details and the number of records linked to them. I want to extend this to the location details and number of records linked to them, so that I can say there are this number of records for this manufacturer, or this number of records in that location. Calculating all the stuff on the summary page is time consuming. What would be the correct formulas to use, so that when I put a new record on sheet 1 (or update one of the records), it automatically updates sheet 2, or what functions should I be looking for? I haven't got a drop down/data validation box for the manufacturers, but I could do this as I can't imagine we are likely to add any options to them. Likewise for the locations, as there will never be more than the 48 locations. This was confusing enough for me to type out, so hopefully it makes sense to someone else. I can possibly create a sterile copy of the spreadsheet if it helps submitted by /u/d4nfe [link] [comments]
- How to add different cells based on people's names change columnsWe play a card game and lunch and we've started to keep track of stats in excel, I'm pretty happy with how I've made it so far but there are 2 things I haven't figured out on my own. How to make the monthly stats update after each game, I've been copying a template downwards so all 5-8 players are all in the same columns but sometimes someone won't play or won't be at work so people's names move around, if i could tell excel to go to the "wrongs" row under this person's name, that would be good but I don't know how. We need a way to assign a value to 1st through 8th and then at the end of the month, we have an objective way to say who did the best that month. I added a couple of pictures below just to help visualize sort of. Thanks. https://preview.redd.it/sfakovmnc6vg1.png?width=1294&format=png&auto=webp&s=0cfc6ac34da4e6548f7ef517d2b821c1f35d5527 https://preview.redd.it/v42rdagoc6vg1.png?width=901&format=png&auto=webp&s=0e42b4c4998aa44e5c37431eee833334f780bd04 submitted by /u/SuperIke505 [link] [comments]
- Proper maintenance of game activity tableI'm part of a team of a rather small hobby game that we've been running for many years now. We've been running simple activity summaries in hours spent like this monthly, ever since beginning in April 2017. They simply go like this: GM Matt - 65.7h M Aiden - 54.3h K Hawk - 32.1h K Tell - 30.0h M Poe - 27.6h T Tribux - 25.4h M Vair - 24.5h K Rodian - 23.3h K Eventide - 20.1h T Crab - 14.4h M Voss- 14.3h M Teir - 9.1h M Vallin - 4.4h T Forrte - 0.5h They're posted monthly so there are similar tables for May 2017, June 2017 and so forth up to April 2026. I want to start storing them all in Excel, this is what I did so far: https://preview.redd.it/n9oem7xh2zug1.png?width=1529&format=png&auto=webp&s=95855988839af802d4d6830df3996943eecdc85f The sum part for each year is simply: =SUM(D3:L3) and so on. Then there's a final column "ALLTIME" where I sum everything to the left except the columns with yearly sums. This works so far, but I'm wondering if there's a better approach that's gonna be more comfortable in the future if I want to extract more data. I have zero experience with data management and Excel, this is what came intuitively to me, but I want to know if there isn't something that will bite me in the ass in the future. submitted by /u/Informal-Ordinary387 [link] [comments]
- Trouble coordinating auxiliary sheet in an online workbook to sheet w/ primary dataI have an online workbook that is used to record, monitor, and manage a full year's academic schedule for the college I work for. I have recently become the one in charge of this workbook, and I have spent many hours improving it and making it both more automated and more foolproof. This workbook has several sheets that, at times, reference each other. One sheet is basically the primary data set that shows the actual schedule with 20+ columns of details per row. Another sheet is there for the purposes of tracking and managing non-course-related releases and work that would reduce faculty workload. As such, this data is also listed on the primary sheet ("Master Schedule"). The problem I am having is this; on the auxiliary sheet that is used for tracking non-course-related work of the faculty ("Release Tracking"), all the columns in this table are auto generated based on the data on the Master Schedule, except for column I. Column I is where I manually select (from a data-validated list) a status (pending, accepted, denied, etc.) to assign to the release. The reason I want this data on Release Tracking instead of Master Schedule is because that the number of instances when the condition that triggers something to populate on Release Tracking is only about 3-5% of the total data on Master Schedule. Plus, Master Schedule is already super wide, so avoiding adding more columns to that table is highly preferable. If you haven't guessed already, my trouble is that when the Master Schedule is resorted or the row order of that table is otherwise changed, the status in I of Release Tracking do not move with the rows on that table. I did forget to mention that both Master Schedule and Release Tracking are both formatted as proper tables. I have a hidden helper column in the Master Schedule table that, when a particular condition is met, triggers a unique ID that another hidden helper column in the Release Tracking table can use to populate that table. That formula is: =IF(C12="N/A","MS_"&ROW(),"") The formula in the hidden helper column of Release Tracking is: =IFERROR(INDEX(master_schedule_table[Index ID],AGGREGATE(15,6,(ROW(master_schedule_table[ [ CRN] ])-ROW(INDEX(master_schedule_table[ [ CRN] ],1,1)) + 1)/(master_schedule_table[ [ CRN] ]="N/A"),ROWS($J$2:J2))),"") The formula in the A column of Release Tracking is: =IF(J2="","",IFERROR(INDEX(master_schedule_table, MATCH(J2, master_schedule_table[Index ID], 0), 12), "")) And the rest of the columns (B:H) follow this pattern except that they reference the appropriate correlative column on the Master Schedule. I have spent probably 15-20 hours trying to figure out a solution, but everything I try that allows me to keep the functionality and design of the workbook the way I want it, fails because, ultimately, the formulas on Release Tracking involve elements of relative positioning and because I cannot find a way to write the ID-generating formula in the helper column of the Master Schedule to create an ID with at least a static element and triggers the ID to come and go as the condition in column C of the Master Schedule comes and goes. To summarize what I want, I need a solution that 1) avoids the problem of the rows on Release Tracking changing when the Master Schedule's rows are changed or reordered (thereby linking my status selections to rows other than the one I want that status to be linked to), 2) allows me to manage/change/update the status on Release Tracking instead of Master Schedule, and 3) at least filter the rows on Release Tracking. Release Tracking doesn't necessarily have to be a full table, but for practical purposes, I need to at least be able to filter those rows. Is this possible with an online Excel workbook or am I just spinning my wheels and wasting my time trying to make this happen? submitted by /u/AndrewRyanPatrick [link] [comments]