Keeping a running total of data from one sheet, in another
Our take
Hi 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
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to deal with a bulky spreadsheet that is starting to hit the limits of Excel?Hello all, I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways. What is the problem So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good. Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal. For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" "). To get to the point I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k. These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands. These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet. 'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide. Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions: Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'. Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless. Alternatively: should I use LET whenever a repeated lookup is needed in the same formula? Really looking for to your answers! submitted by /u/EvolvedRevolution [link] [comments]
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- Not sure how to automate counting of monitoring info gained from microsoft form, any guidance?I'd like to preface this with the facts that I'm 1. using excel for the web, and 2. very new to using excel. I work with a group of volunteers, and we have to take in monitoring information from each client we work with (think age ethnicity disability etc). At the end of the year we have to report this info to another institution. For the past few years the volunteers have been manually counting this data which takes around 10+ hours. My idea was to switch things up by having volunteers input data into a microsoft form linked to an excel file. One sheet for raw data, 12 more for each month of the year. My goal is to make it so excel automatically updates the month's count when a new client's data is added. Previously all data has been counted under the name of the volunteer who took it. The final reports look something like this: John Smith Jane Doe Age: 18-25 5 0 25-60 0 4 60+ 0 1 Ethnicity: White 2 1 Black 2 2 Mixed 1 3 Disabled: Yes 3 1 No 2 4 I have the easy part (Microsoft Form linked to Excel) done, but I'm stuck on how to get the information from the raw data 1. into the monthly sheets 2. have this information automatically add up over the year. I'm looking primarily for guidance on how to do this and what functions I should look into. I have made a table of the info in the raw data sheet which I think should help with being able to move it across the sheets. Based on research so far I think COUNTIFs may be something to experiment with? As said I'm a complete newbie, but I want to know how to do this And how the process works rather than just being handed an answer. Happy to provide more info if needed. Any resources/guidance would be really appreciated 🙏 submitted by /u/incrediblycalmwithit [link] [comments]
- Data Automation - Pulling data from a sheet but also blocking cellsMy main purpose for this is to be able to create a trackable booking spreadsheet, the main input page is where the kids are booked onto sessions this is then checked against when they enrol and leave. I have Data in my 'Input' sheet this is split into 5 sessions per day 5 days. Column A is the name Column B:F is Monday 5 sessions Column G:K is Tuesday 5 sessions and so on I then have a sheet 'Apr 26' which then pulls this data into the monthly sheet The data i have in my input sheet can have anywhere between 1 cell to 12 cells per session, my formula works if there is 2 cells or more per session but doesnt work if only 1 cell it puts that cells data over multiple cell. Excel Formula Code (PasteBin) Excel Doc Link - ignore sheets past May 26 as they dont have the updated code or conditional formatting in. Excel Doc Link (Non Macro) This is my main input data sheet This is the sheet and cell (highlighted) that the formula relates to As you can see in the above images on Monday on the ASC 5pm column it multiples the data (child 1) If I have 2 lots of data in the ASC 5pm Monday column on the input sheet the formula works properly. Input sheet is how sessions per child are booked. sessions can only have 12 in however on certain days due to staffing can only have a certain number in (say 9) the formula im having trouble with is on sheet Apr 26 on column F it repeats the 'child 1' cell multiple times rather than just doing It once but as you can see on column K the formula works but just not when its got 1 cell of data Version Excel 2024 on Mac but needs to work on windows as well submitted by /u/BarrowBluebird [link] [comments]