Auto populating cells based on a different cell

E

eraust

New Member
Joined
Sep 13, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone

I wonder if anyone can help me with this. I'm trying to create a schedule which auto populates cells based on a "Start Time" cell. The idea is that for multiple running shows, I can see what time the overlaps are at a glance.

Though it seems to work, I'm not sure if the method I've used is the easiest way to do this or if anyone can suggest a better way?

The main challenge I'm facing is that depending on the start times, the shows may run in to the next day (e.g. a show that starts at 22:00 and lasts 5 hours doesn't finish until 03:00 the next day) and I'd like the cells to populate to reflect that. However, I don't want to have the days running across consecutively, I'd like be displayed as they are in the example below so I can see the full week. Can anyone suggest how I could incorporate this into the formula?

Book1
ABCEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Wed14/09/2022Start timeLength (hrs)00:0001:0002:0003:0004:0005:0006:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
2Show 104:005-1-1-1-111111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
3Show 201:004-11111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
4Show 307:005-1-1-1-1-1-1-111111-1-1-1-1-1-1-1-1-1-1-1-1
5Show 404:003-1-1-1-1111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
6
7Thu15/09/2022Start timeLength (hrs)00:0001:0002:0003:0004:0005:0006:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
8Show 114:008-1-1-1-1-1-1-1-1-1-1-1-1-1-111111111-1-1
9Show 201:004-11111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
10Show 305:005-1-1-1-1-111111-1-1-1-1-1-1-1-1-1-1-1-1-1-1
11Show 403:003-1-1-1111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
12
13
14
Schedule
Cell Formulas
RangeFormula
A1,A7A1=TEXT(B1,"ddd")
F2:F5F2=IF($F$1=$C2,1,-1)
G2:G5G2=IF($G$1=$C2,1,IF(COUNTIF($F2:F2,F2)<$E2,F2,-1))
H2:H5H2=IF($H$1=$C2,1,IF(COUNTIF($F2:G2,G2)<$E2,G2,-1))
I2:I5I2=IF($I$1=$C2,1,IF(COUNTIF($F2:H2,H2)<$E2,H2,-1))
J2:J5J2=IF($J$1=$C2,1,IF(COUNTIF($F2:I2,I2)<$E2,I2,-1))
K2:K5K2=IF($K$1=$C2,1,IF(COUNTIF($F2:J2,J2)<$E2,J2,-1))
L2:L5L2=IF($L$1=$C2,1,IF(COUNTIF($F2:K2,K2)<$E2,K2,-1))
M2:M5M2=IF($M$1=$C2,1,IF(COUNTIF($F2:L2,L2)<$E2,L2,-1))
N2:N5N2=IF($N$1=$C2,1,IF(COUNTIF($F2:M2,M2)<$E2,M2,-1))
O2:O5O2=IF($O$1=$C2,1,IF(COUNTIF($F2:N2,N2)<$E2,N2,-1))
P2:P5P2=IF($P$1=$C2,1,IF(COUNTIF($F2:O2,O2)<$E2,O2,-1))
Q2:Q5Q2=IF($Q$1=$C2,1,IF(COUNTIF($F2:P2,P2)<$E2,P2,-1))
R2:R5R2=IF($R$1=$C2,1,IF(COUNTIF($F2:Q2,Q2)<$E2,Q2,-1))
S2:S5S2=IF($S$1=$C2,1,IF(COUNTIF($F2:R2,R2)<$E2,R2,-1))
T2:T5T2=IF($T$1=$C2,1,IF(COUNTIF($F2:S2,S2)<$E2,S2,-1))
U2:U5U2=IF($U$1=$C2,1,IF(COUNTIF($F2:T2,T2)<$E2,T2,-1))
V2:V5V2=IF($V$1=$C2,1,IF(COUNTIF($F2:U2,U2)<$E2,U2,-1))
W2:W5W2=IF($W$1=$C2,1,IF(COUNTIF($F2:V2,V2)<$E2,V2,-1))
X2:X5X2=IF($X$1=$C2,1,IF(COUNTIF($F2:W2,W2)<$E2,W2,-1))
Y2:Y5Y2=IF($Y$1=$C2,1,IF(COUNTIF($F2:X2,X2)<$E2,X2,-1))
Z2:Z5Z2=IF($Z$1=$C2,1,IF(COUNTIF($F2:Y2,Y2)<$E2,Y2,-1))
AA2:AA5AA2=IF($AA$1=$C2,1,IF(COUNTIF($F2:Z2,Z2)<$E2,Z2,-1))
AB2:AB5AB2=IF($AB$1=$C2,1,IF(COUNTIF($F2:AA2,AA2)<$E2,AA2,-1))
AC2:AC5AC2=IF($AC$1=$C2,1,IF(COUNTIF($F2:AB2,AB2)<$E2,AB2,-1))
B7B7=B1+1
F8:F11F8=IF($F$7=C8,1,-1)
G8:G10G8=IF($G$7=$C8,1,IF(COUNTIF($F8:F8,F8)<$E8,F8,-1))
H8:AC8H8=IF(H7=$C8,1,IF(COUNTIF($F8:G8,G8)<$E8,G8,-1))
H9:AC9H9=IF(H7=$C9,1,IF(COUNTIF($F9:G9,G9)<$E9,G9,-1))
H10:AC10H10=IF(H7=$C10,1,IF(COUNTIF($F10:G10,G10)<$E10,G10,-1))
G11:AC11G11=IF(G7=$C11,1,IF(COUNTIF($F11:F11,F11)<$E11,F11,-1))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F8:AC11Cell Value=1textNO
F8:AC11Cell Value=-1textNO
F2:AC5Cell Value=1textNO
F2:AC5Cell Value=-1textNO
Cells with Data Validation
CellAllowCriteria
C2:C5List=$AE$2:$AE$25
C8:C11List=$AE$2:$AE$25
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Wow, it must have taken you forever to set this up. I feel like you may have reach the limits of what you can do using functions, though maybe someone more knowledgeable could figure it out. You could definitely write this as a macro if you're willing to start from scratch. I could try and write one up for you if you like.
 
Upvote 0
DropTables said:
Wow, it must have taken you forever to set this up. I feel like you may have reach the limits of what you can do using functions, though maybe someone more knowledgeable could figure it out. You could definitely write this as a macro if you're willing to start from scratch. I could try and write one up for you if you like.
Click to expand...
I think something like this would work if your willing to drop your functions

VBA Code: 
Sub AutoFillinTime()
Dim WB As Workbook
Set WB = Application.ActiveWorkbook
    Dim WS As Worksheet
    Set WS = WB.ActiveSheet
    
Dim i As Long
Dim NumofCols As Long
Dim NumofRows As Long
Dim lng As Long
Dim Col1 As String
Dim Col2 As String
Dim TotalTime As Long



NumofCols = WS.Cells(1, Columns.Count).End(xlToLeft).Column
NumofRows = WS.Cells(Rows.Count, 2).End(xlUp).Row


For i = 2 To NumofRows
    If Left(WS.Range("B" & i).Value, 4) = "Show" Then
        Starttime = WS.Range("C" & i).Value * 24
        Length = WS.Range("E" & i).Value
        Col1 = Split(Cells(1, Starttime + 6).Address, "$")(1)
        Col2 = Split(Cells(1, Length + 6 + Starttime).Address, "$")(1)
        TotalTime = Starttime + Length
            If TotalTime < 24 Then
            With WS.Range(Col1 & i & ":" & Col2 & i)
                .Value = 1
                .Interior.ColorIndex = 1
                .Font.ColorIndex = 7
            End With
            Else
            With WS.Range(Col1 & i & ":" & "AC" & i)
                .Value = 1
                .Interior.ColorIndex = 1
                .Font.ColorIndex = 7
            End With
            Col2 = Split(Cells(1, TotalTime - 18).Address, "$")(1)
            With WS.Range("F" & i + 1 & ":" & Col2 & i + 1)
                .Value = 1
                .Interior.ColorIndex = 1
                .Font.ColorIndex = 7
            End With
            End If
    End If
Next
        
End Sub
 
Upvote 0
DropTables said:
I think something like this would work if your willing to drop your functions

VBA Code: 
Sub AutoFillinTime()
Dim WB As Workbook
Set WB = Application.ActiveWorkbook
    Dim WS As Worksheet
    Set WS = WB.ActiveSheet
   
Dim i As Long
Dim NumofCols As Long
Dim NumofRows As Long
Dim lng As Long
Dim Col1 As String
Dim Col2 As String
Dim TotalTime As Long



NumofCols = WS.Cells(1, Columns.Count).End(xlToLeft).Column
NumofRows = WS.Cells(Rows.Count, 2).End(xlUp).Row


For i = 2 To NumofRows
    If Left(WS.Range("B" & i).Value, 4) = "Show" Then
        Starttime = WS.Range("C" & i).Value * 24
        Length = WS.Range("E" & i).Value
        Col1 = Split(Cells(1, Starttime + 6).Address, "$")(1)
        Col2 = Split(Cells(1, Length + 6 + Starttime).Address, "$")(1)
        TotalTime = Starttime + Length
            If TotalTime < 24 Then
            With WS.Range(Col1 & i & ":" & Col2 & i)
                .Value = 1
                .Interior.ColorIndex = 1
                .Font.ColorIndex = 7
            End With
            Else
            With WS.Range(Col1 & i & ":" & "AC" & i)
                .Value = 1
                .Interior.ColorIndex = 1
                .Font.ColorIndex = 7
            End With
            Col2 = Split(Cells(1, TotalTime - 18).Address, "$")(1)
            With WS.Range("F" & i + 1 & ":" & Col2 & i + 1)
                .Value = 1
                .Interior.ColorIndex = 1
                .Font.ColorIndex = 7
            End With
            End If
    End If
Next
       
End Sub
Click to expand...
You'll probably have to remove your data validation
 
Upvote 0
DropTables said:
Wow, it must have taken you forever to set this up. I feel like you may have reach the limits of what you can do using functions, though maybe someone more knowledgeable could figure it out. You could definitely write this as a macro if you're willing to start from scratch. I could try and write one up for you if you like.
Click to expand...

Haha, it did take me a while considering I actually adapted it from an example I found. I'm still very much a beginner.

Thanks so much for your solution - I am 100% unfamiliar with VBA, so I watched a couple of quick beginner tutorials today and tried to have a play around with what you wrote. I have lots of learning to do and you've given me a great place to start so thank you!

I'll leave this open for a bit on the off chance anyone can think of a way to do this with functions. Thanks again!
 
Upvote 0
eraust said:
I'll leave this open for a bit on the off chance anyone can think of a way to do this with functions. Thanks again!
Click to expand...

Welcome to the MrExcel Message Board!

Do I understand it correctly? If Show 3 starts at 22:00 and lasts 5 hours, it is also supposed to be marked in the next-day range, right?

Please take a look at the following sample to see if it helps. It is using two different Array formulas only (you need to select the weekly formula range (i.e. F2:AC5 for the first day) then enter the formula into the top-left cell in this range (F2 for the first day), then hit Ctrl + Shift + Enter). I see that you are using Excel 2016, but there are no Dynamic arrays in that version. So, we need to use CSE Array formulas.

Please also note, that the formula is slightly different starting from the second day, because it also looks for the Shows that start on the previous day but still continue after 00:00. Sample is Show 3 below.

To create other days, just copy the second-day range (A7:AC11) and paste it into A13, A19, A25, A31, and A37, then clear the Start Time and Lenght values.

Finally, I built this structure on the assumption that all Shows will be the same/existing and located in the same order in the day ranges.

Please note that I made some changes to the start times and durations in the sample below to test it and demonstrate the "next day" issue.

1216668.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Wed9/14/2022Start timeLength (hrs)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
2Show 13:005-1-1-111111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
3Show 21:004-11111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
4Show 322:005-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-111
5Show 44:003-1-1-1-1111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
6
7Thu9/15/2022Start timeLength (hrs)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
8Show 114:0014-1-1-1-1-1-1-1-1-1-1-1-1-1-11111111111
9Show 21:004-11111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
10Show 35:005111-1-111111-1-1-1-1-1-1-1-1-1-1-1-1-1-1
11Show 43:003-1-1-1111-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
Sheet2
Cell Formulas
RangeFormula
A1,A7A1=TEXT(B1,"ddd")
F2:AC5F2=((B1+F1:AC1)>=(B1+C2:C5))*((B1+F1:AC1)<((B1+C2:C5)+E2:E5/24))*2-1
B7B7=B1+1
F8:AC11F8=(((B7+F7:AC7)<((B1+C2:C5)+E2:E5/24)) + ((B7+F7:AC7)>=(B7+C8:C11))*((B7+F7:AC7)<((B7+C8:C11)+E8:E11/24)))*2-1
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F8:AC11Cell Value=1textNO
F2:AC5Cell Value=1textNO
 
Upvote 0
Solution
eraust said:
Haha, it did take me a while considering I actually adapted it from an example I found. I'm still very much a beginner.

Thanks so much for your solution - I am 100% unfamiliar with VBA, so I watched a couple of quick beginner tutorials today and tried to have a play around with what you wrote. I have lots of learning to do and you've given me a great place to start so thank you!

I'll leave this open for a bit on the off chance anyone can think of a way to do this with functions. Thanks again!
Click to expand...
I use to write a lot of complex functions and was too intimidated by VBA to try and learn it. Once I finally buckled down and decided to learn it, I almost never write functions anymore. Looking back over the code, I see a few flaws, but I am glad I could get you started. Let me know if you have any questions.
 
Upvote 0
smozgur said:
Welcome to the MrExcel Message Board!

Do I understand it correctly? If Show 3 starts at 22:00 and lasts 5 hours, it is also supposed to be marked in the next-day range, right?

Please take a look at the following sample to see if it helps. It is using two different Array formulas only (you need to select the weekly formula range (i.e. F2:AC5 for the first day) then enter the formula into the top-left cell in this range (F2 for the first day), then hit Ctrl + Shift + Enter). I see that you are using Excel 2016, but there are no Dynamic arrays in that version. So, we need to use CSE Array formulas.

Please also note, that the formula is slightly different starting from the second day, because it also looks for the Shows that start on the previous day but still continue after 00:00. Sample is Show 3 below.

To create other days, just copy the second-day range (A7:AC11) and paste it into A13, A19, A25, A31, and A37, then clear the Start Time and Lenght values.

Finally, I built this structure on the assumption that all Shows will be the same/existing and located in the same order in the day ranges.

Please note that I made some changes to the start times and durations in the sample below to test it and demonstrate the "next day" issue.
Click to expand...

Hi there. Sorry for the late reply.

Wow - thank you so much for this. I've had a quick play around with it and it definitely looks like it works. Again, completely new to arrays so you've given me a massive head start to work up from. I still need to look closer at why your formula actually works so I understand what's going on!

Thanks again, I really appreciate it.
 
Upvote 0
smozgur said:
Welcome to the MrExcel Message Board!

Do I understand it correctly? If Show 3 starts at 22:00 and lasts 5 hours, it is also supposed to be marked in the next-day range, right?

Please take a look at the following sample to see if it helps. It is using two different Array formulas only (you need to select the weekly formula range (i.e. F2:AC5 for the first day) then enter the formula into the top-left cell in this range (F2 for the first day), then hit Ctrl + Shift + Enter). I see that you are using Excel 2016, but there are no Dynamic arrays in that version. So, we need to use CSE Array formulas.

Please also note, that the formula is slightly different starting from the second day, because it also looks for the Shows that start on the previous day but still continue after 00:00. Sample is Show 3 below.

To create other days, just copy the second-day range (A7:AC11) and paste it into A13, A19, A25, A31, and A37, then clear the Start Time and Lenght values.

Finally, I built this structure on the assumption that all Shows will be the same/existing and located in the same order in the day ranges.

Please note that I made some changes to the start times and durations in the sample below to test it and demonstrate the "next day" issue.
Click to expand...

Hi again! Wondering if you'd be able to help me - I've been trying to use what you helped me with to figure out a different way of displaying this but I'm struggling. I'd like to move away from relying on conditional formatting so I was trying to make it so that rather than the cells of each hour coming up in a different color, they just display the name of the show.

So, for example, if Show 1 starts at 14:00 and finishes at 17:00, cells T2, U2 and V2 would display whichever name is in cell B2.

Would you be able to help?

Thanks again in advance.
 
Upvote 0
I think the following should help.

Instead of returning -1 and 1, we can use 0 and 1 this time (false and true as boolean), and also use IF function to decide displaying the corresponding show's name. Same instructions to enter the formulas (Wed and Thu formulas are different, so Thursday area should be copied for the days starting from Friday).

Book16
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Wed9/14/22Start timeLength (hrs)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
2Show 114:003              Show 1Show 1Show 1       
3Show 21:004 Show 2Show 2Show 2Show 2                   
4Show 322:005                      Show 3Show 3
5Show 44:003    Show 4Show 4Show 4                 
6
7Thu9/15/22Start timeLength (hrs)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
8Show 114:0014              Show 1Show 1Show 1Show 1Show 1Show 1Show 1Show 1Show 1Show 1
9Show 21:004 Show 2Show 2Show 2Show 2                   
10Show 35:005Show 3Show 3Show 3  Show 3Show 3Show 3Show 3Show 3              
11Show 43:003   Show 4Show 4Show 4                  
Sheet1
Cell Formulas
RangeFormula
A1,A7A1=TEXT(B1,"ddd")
F2:AC5F2=IF(((B1+F1:AC1)>=(B1+C2:C5))*((B1+F1:AC1)<((B1+C2:C5)+E2:E5/24)),B2:B5,"")
B7B7=B1+1
F8:AC11F8=IF((((B7+F7:AC7)<((B1+C2:C5)+E2:E5/24)) + ((B7+F7:AC7)>=(B7+C8:C11))*((B7+F7:AC7)<((B7+C8:C11)+E8:E11/24))),B8:B11,"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 1
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
315
Mouse7681
M
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
564
SaraWitch
SaraWitch
I
  • Question Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
465
ItalianPlatinum
I
S
  • Locked
  • Question Question
Formula Lenght
Replies
1
Views
573
Peter_SSs
Peter_SSs
HappyChappy
  • Question Question
Hours over basic
Replies
4
Views
393
Fluff
Fluff

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top