•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Extract Data Across 3 separate sheets, and combine in a 4th sheet, filtered by criteria.
Our take
Managing schedules across multiple sites can be a daunting task, especially when relying on legacy spreadsheets. Imagine effortlessly consolidating job data from three distinct sheets into a single, organized fourth sheet—automatically filtered by due dates and prioritized for individual employees. With AI-driven solutions, you can streamline this process, eliminating manual data entry and enhancing productivity. In this guide, we’ll explore a straightforward approach to achieve this automation, ensuring you can focus on what truly matters: delivering exceptional service to your clients. Let's dive in!
I'm working on a scheduling document where I have manufacture jobs being undertaken across three sites, each of which have their own sheet to track jobs with information including the due date, client name, employee, and some job relevant codes as well as some tick boxes (nine columns per table).
I am attempting to create a 3 more sheets to track jobs across all 3 sites undertaken by a single employee to be used a tool for good prioritising. I would like to be able to take the full rows of information from the existing three sheets and have them automatically populate the 4th, and be able to sort the 4th sheet by a due date column.
I have played with =FILTER functions and tables converted to ranges, but haven't found a solution where the the table can be filtered and self-populatin from the 3 sheets at the same time. It's either one or another, and following a previous post havbe tried using suggested formula such as =FILTER, and =LET.
I ahave attached screenshots below of what the document is somewhat like at the moment. In Them there are two site 3 sheets. The alternative is in a f Layout similar to the one currently used on that site. This new workbook is to replace an old workbook with no conditional formatting and lacking necessarry info, wher hoghlighting and data input was all completely manual.
Sheet 2, Column E is now 'Delivery Method'
Any help would be appreciated. Thank you.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- 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]
- Tracking totals over all sheets, but from different cellsHi, I'm trying to keep an automated running total on working hours for my project. I have one sheet per day, each with a cell that sums the total hours for that day (reads 40 in my screenshot), dating back to October. I've been tracking this manually with a rather bulky SUM function on a dedicated sheet, and it works just fine, but I'd like to try to automate the grand total to sum each day as it's created, rather than manually adding the cell each time I create a new sheet. The issue I'm running into is that the daily total doesn't land on the same cell every day; I am constantly adding & removing rows as people come & go from the project. There are also multiple cells that read total hours; one for the subcontractor sections, one for my own company's section, and the bottom one which sums the whole day - this is the one I'm trying to track. Is there a way to filter for this cells contents on each sheet based on the Daily Total cell in the bottom right perhaps, even though they're not in the same row? As an aside, I'm not allowed to change the layout to make this easier; this is a standard form at my company and the formatting needs to stay the same. https://preview.redd.it/3giox6bkvtng1.png?width=819&format=png&auto=webp&s=916dd43f9584d1ac3491770ac180ca0b445688d0 submitted by /u/Mental-Screen-1127 [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]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#google sheets#natural language processing for spreadsheets#financial modeling with spreadsheets#rows.com#real-time data collaboration#big data management in spreadsheets#conversational data analysis#intelligent data visualization#AI formula generation techniques#self-service analytics tools#Excel compatibility#business intelligence tools#scheduling document#manufacture jobs#due date#three sites#job tracking#self-populating