Data Automation - Pulling data from a sheet but also blocking cells
Our take
My 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 Doc Link - ignore sheets past May 26 as they dont have the updated code or conditional formatting in.
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
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- Creating an auto-populating visual calendar for multiple departments to view?I coordinate student schedules across multiple hospital units. I log the shifts in an hours log spreadsheet (date, start time, end time, unit, student name, etc.). Every month, I need to send each unit a schedule showing who is coming in and when so they can review and plan coverage. Right now, this part is very manual, and does not lend to tracking any data like how many students on each units, hours, etc., which we need as a department. We have over 100 students each month, so as much automizing as possible would be helpful as its a big process. Ideally, I’d like something that: Pulls directly from the hours log (no retyping or copy/paste) Is easy to read at a glance for unit managers Can handle many units, many months, and many students Lets me filter by unit + month I'm not really an excel genius, and I've been using copilot to help me with some of this and formulations, but It's not providing me with accurate formulas for the actual calendar conversion. If there are any resources on what formulas to use, what platforms that I could maybe look into for how to do this, or just honestly any advice at all. We've gotten the data tracking mostly drafted up and it's able to pull from pivot tables and show the analytics, but the calendar is my biggest hurdle at the moment. submitted by /u/JuggernautFlashy6489 [link] [comments]
- Formatting question for automating data entryIm going to try to articulate what I need and if it’s possible to do inside excel. At my job I have to record the amount of patrons using our facilities. and specify what particular services are being used. at the end of each quarter. (3 month period) I must tally up all the numbers and provide a total for each aspect of our facility as well as the total overall. For example. 1st quarter numbers. 100 patrons used theatre. 250 patrons used Game room 450 patrons used computer lab so on and so forth. Now that you have the gist in your head. Imagine a spreadsheet where the first form is just a data entry sheet. it’s essentially just a box that never changes. You input the numbers for the week, and that data gets automatically moved to a different cell that has the total amount. so that at the end of the quarter I can easily see my total without having to backtrack or tediously add. if anyone has some insight on how I can do this Please reach out. If you have any questions about my wording or understanding exactly what I mean please also reach out. If you read all this I appreciate your time. submitted by /u/Beneficial-Yard-9006 [link] [comments]
- Pulling data from one sheet based on a columnApologies I’m not that experienced with Excel. I have a sheet with dates that a task needs to be completed (sorry for the photo of a screen but work IT won’t let me screenshot). I have a Master List sheet of all dates due (column D adds 48 weeks to Column C and Column E extracts the month from D). I want a sheet for each month and for each month’s sheet to pull the IDs from Column A so we can just look and see which tasks are due in e.g. January. And so that all are visible in the Master List so we don’t end up with duplicate IDs (column A has highlight rules for duplicates). I tried google and think it might need VLOOKUP but couldn’t seem to get it to work. Thanks. submitted by /u/arrianne_ [link] [comments]