Link to home
Start Free TrialLog in
Avatar of Paul Cahoon
Paul Cahoon

asked on

Master schedule in excel from other worksheets

I'm looking for help building a formula referencing a range of cells on another worksheet and mirroring any merged cells. I have multiple schedules built in their own worksheet. Each row is a 5 minute range of time. So if a particular event is 30 minutes it would have 6 rows merged. Then there might be a 40 min block beneath it (8 cells merged). I want to mirror that in a separate worksheet so that if I change the number of merged cells or even the data in the cell, it gets reflected in the master schedule.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

My strengths are not with formulas but since you haven't gotten an answer from formula experts I assume that what you want to do isn't possible with formulas. If you are willing to accept a VBA solution then please attach a sample workbook.
Avatar of Paul Cahoon
Paul Cahoon

ASKER

I'm including a link to the sample in OneDrive: https://1drv.ms/x/s!AmpzUCTH0OuFv5dkYAzJCQbUliK2bA?e=UR1tHg

I hope that is an acceptable way to submit.

Just to clarify, what I'm looking for is that the Master Schedule be dynamically mirroring the other 2 schedules as they are adjusted.
When I open the One Drive file all I get is the Master Schedule. The normal way of uploading a workbook is to click the 'Attach File' link and then click the 'Upload File' button. Can you do that please?
Sample Workbook.xlsx

Sorry, I guess I was expecting a paperclip because I totally missed that button previously.
I the code I'm going to write, the names of the worksheets are important, so in your real workbook are 'Master Schedule', 'Schedule 1', 'Schedule 2', etc the actual names?

Also
Could you ever add a new schedule?
Could you ever delete a schedule?
On the 'Schedule n' sheets, will there ever be more than one week of data?
BTW are you familiar with Google Calendar? It may look daunting but it's actually very easy to use and my wife and I use it to keep track of both of our scheduled events (although there's a lot less of them these days).
In answer to your question about Google Calendar, yes I am very familiar with it and use it extensively. The reason I'm building this particular spreadsheet application is because I need to be able to build a precise printed calendar that integrates these various schedules. Unfortunately, Google Calendar does not give me the formatting flexibility I need.

As to your question about the schedule names, no those are not the correct names and yes it is possible that I would have to add other schedules. I do have a little bit of experience with Visual Basic and I'm hoping that I could modify the code as needed. I have been able to do this in the past when necessary. For now, if you could build it around that name but adding two more sheets to it that should give me the flexibility I need for now. The names of the sheets are not as critical as the data being pulled in so just call them schedule 1 through 4.

I was really hoping there was a way to do it with formulas but I appreciate your willingness to help in this manner.
Thanks. Are you familiar with the horizontal alignment 'Center Across Selection' formatting option? If not it looks exactly like merged cells but the columns are not merged. Merged cells are difficult to deal with so with your permission I'd like to change the Master Schedule's column formatting to be centered across selection.
No, I'm not familiar with it but after a quick Google search on it I don't know how I've never come across it before. It appears it is more for horizontal merges as opposed to Vertical merges like I'm trying to work with. I'm not against it either way as long as we are on the same page for what my end result is.
It only affects horizontal ranges so I'll change it. Note that if you ever wanted to change 'Monday' to something else via code, you'd refer to in this case 'B1'.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That looks great! I have picked up on one problem. If I edit Schedule 1 it updates the Master Schedule on the following day (change monday and on the Master schedule it updates on Tuesday). I don't understand the code well enough to solve it although I am assuming it's an indexing problem? (Not trying to tell you how to fix your own code, but just trying to learn.)

One other question/request if possible...Am I understanding correctly that this is basically a copy/paste process of the column to the Master Schedule? I understand why that would require activating that sheet for the paste process but is there any way to hide that process or at least reactivate the sheet being edited after the paste is complete?
Am I understanding correctly that this is basically a copy/paste process of the column to the Master Schedule?
Yes.

The attached workbook corrects the problem you reported and it also no longer selects the Master Schedule. The code is now simpler too. If you have any questions about the code let me know. I also made a few other changes:
1) The code needs to know if it is a schedule worksheet that's being changed. Previously it just looked  for the word "Schedule" in the worksheet's name, but since I don't know the real names of the schedules I changed it to look for "Monday" in B1.
2) I added code so that manual copy/paste actions will be ignored.
3) I added a border to each schedule's Wednesday column so as to preserve the Master Schedule's Wednesday border if a Wednesday data is changed. In the real workbook you should change Sundays' borders rather than Wednesdays'.
29191543a.xlsm
29191543a.xlsm

I made a few adjustments to the layout which seems to work fine. But notice on the far right of the master schedule how content seems to be showing up for some reason. I also had it show up down schedule as well (scheduled something for 8:15 Monday and it showed up on 8:15, 10:15, 12:15 etc) at one point but haven't been able to recreate that issue.

I don't mind manually deleting the content on the right if necessary but I wanted to at least mention it. I love the result so far, though.
You mentioned about the name of the schedules...am I able to change them at all? If I change them, I assume I would need to change the headings on the Master Schedule to match, right?
The code, starting with the 'a' version of the workbook,  no longer assume that "schedule" is in the name of the schedules so you can change the names as you like as long as the schedule names match those on the Master Schedule. The code does assume that the name of the master is "Master Schedule" but that is easily changed in the Set wsMaster = ThisWorkbook.Worksheets("Master Schedule") line.

I believe I found the cause of the problem and it was that the code that looked for "Monday" to determine if a schedule sheet had been changed also included the Master and it should not do that. To fix it I added code to check if the name of the changed sheet is Master Schedule" and if so ignore the change.

The only changes I made in the attached workbook were deleting the "far right" data and fixing the borders on all the sheets.
29191543b.xlsm
Edit: I updated my previous post and changed the workbook.
That seems to be working well. Is there a way to activate the code on ANY change, including formatting or copy/paste? If I copy/paste something, I then have to manually edit each cell to get it to transfer to the master. Same thing if I change formatting on a particular event.
Save your workbook and then change lines 11 and 12 from this
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim wsMaster As Worksheet
Dim wsSchedule As Worksheet
Dim rngMastDay As Range
Dim rngMastCols As Range
Dim lngLastRow As Long
Dim strColLetter As String
Dim lngLastColumn As Long
Dim lngCol As Long

' Exit if for example a manual copy is being done
If Target.Cells.Count > 1 Or Sh.Name = "Master Schedule" Then
    Exit Sub
End If
'... more code

Open in new window

to this and see what happens.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim wsMaster As Worksheet
Dim wsSchedule As Worksheet
Dim rngMastDay As Range
Dim rngMastCols As Range
Dim lngLastRow As Long
Dim strColLetter As String
Dim lngLastColumn As Long
Dim lngCol As Long

' Exit if the Master Schedule is being changed
If Sh.Name = "Master Schedule" Then
    Exit Sub
End If
'... more code

Open in new window

After making the above adjustment, things seem to be working exactly as I expected. I began adding real data to the schedules. Everything seemed to be working fine until I copied/pasted some borders that I didn't want copied. I corrected them in the schedules but it did not update in the master (even though the data was updating) after multiple changes of the data. I then removed the borders in the master schedule itself. I then got an error in the routine. I didn't note the line because I thought I would just restart the workbook and solve the issue. But, now even after a restart, the routine doesn't seem to be working at all. Any thoughts on what might be the issue?
29191543b.xlsm

Here is the file in case you need to look at the same one.
You left out a line here:

Change this
' Exit if the Master Schedule is being changed
If Sh.Name = "Master Schedule" Then
End If

Open in new window


to

' Exit if the Master Schedule is being changed
If Sh.Name = "Master Schedule" Then
    Exit Sub
End If

Open in new window

Otherwise changes to the Master Schedule will trigger the code that's supposed to copy a changed schedule to the Master yielding unexpected results.
Hmmm....that's interesting because it was working until I got to that point. I'll test this and get back to you.
Is it working now?
Working depends on how perfect you want it. I apologize for not getting back to you already. It still does not seem to pick up on formatting changes. I thought at one point we had that working properly but in the most recent version it's not. I have been able to accomplish quite a bit of planning using this tool with minimal concessions. If I need to make any formatting changes that I need reflected in the master schedule, I have to make a small text change somewhere in the column to get the formatting to copy over.

Another issue I've had is in the First Column of the schedules. For some reason when I try to use a time format it wants to translate it to a decimal time instead of hours and minutes. It works fine on the master schedule but not on the individual schedules. I finally ended up having to put them in as text so that I could get what I needed.

Let me know if you need me to send you my copy. I can make you a copy and strip out the private details.


It still does not seem to pick up on formatting changes.
The code I wrote is triggered when a change is made to the data and changing format doesn't fall under that definition. So if all you do is change the format then no change will be made to the Master, but if change format and you update any cell (even to itself) the Master will be show the new format. If you want to copy any changed format and you don't want to go to the trouble of changing some value to itself I can add a button that you could click to copy the format.
when I try to use a time format it wants to translate it to a decimal time instead of hours and minutes.
The blank cells below the last entry in the several Schedule... sheets were not formatted properly. To correct that I selected the first column in each of those sheets and right-clicked Format Cells->Custom and chose h:mm. That change is in the attached workbook,
29191543c.xlsm
I had already tried that. I'm not sure what's going on in my copy but take note of how it looks. It's displaying correctly in the formula bar but not in the actual cell. For some reason that sheet is converting it to a decimal.
User generated image
Here is a look at the formatting:
User generated imageI even tried the custom formatting and got the same results.
User generated image
Are those pictures from my 29191543c.xlsm workbook? if so that's very strange because everything is OK here.

Is the 08:00 cell OK? If so you could put the formula below in A4 and copy down. But before you do that, temporarily add an Exit Sub line as the first line in the ThisWorkbook->Workbook_SheetChange event.

=A3+TIME(0,5,0)
No, these are from the "b" version of your workbook. I built out a lot of detailed scheduling and formatting in that version and just haven't had time to transfer it over to the "c" version yet. I just really hoped to be able to fix the formatting on the one that I have.
I don't know which of the Schedule sheets you are showing, but even with the 'b' version of the workbook each Schedule sheet's cell A3 when formatted Custom->h:mm displays 8:15 when I do it.

There are a fewf things you could try:
  1. Delete column 'A' and then reinsert a new column 'A'
  2. Try formatting the cells in the new column
  3. If that doesn't work then close the workbook without saving it
  4. Then as shown in the picture select the workbook via File->Open
  5. Click the indicated 'Open' button
  6. Select 'Open and Repair'
  7. If that doesn't work then try rebooting
User generated image
I tried all of these steps and got the same result. I even tried from a 2nd computer and got the exact same results. I also tried copying the entire 1st column from one of the individual schedules (which display correctly) into the master schedule and it converted all of them to decimals.

I can live with it like it is and greatly appreciate your help. But, if you want to take another look at my file, I'll clean out some of the personal data and send you a copy of it. If not, let me know and I'll close this out and give you credit for the solution. Either way, I again thank you for your help.
Yes I'd like to take another look at your file.
Also in this comment of mine I suggested that I could add a button that would allow you to transfer formatting. I could still do that but I just realized that I can capture formatting if I add code to the schedule sheets that transfers that sheet's complete contents to the Master (rather than just the changed column as it does now)  when you leave the sheet. That would mean that if you just looked at schedule 1 for example and then went back to the master, schedule 1's contents would be transferred to the master. It would also mean however that if you looked at schedule 1 and then looked at schedule 2 that schedule 1's contents would still be added to the master. That's a bit of overhead but your worksheets aren't large so I don't think it will be noticeable.
I apologize for taking so long to get back to you on this. If you would prefer I go ahead and close this out, I'll glad credit you for a solution. I was able to get what I needed and am able to continue using this tool as needed.

That said, here is my version of the file if you would like to continue tweaking.

29191543b.xlsm
I only object based on the previous comment. If Martin would like to close out, I'll gladly close it out. My attention was diverted by a variety of matters and was not able to address this in a timely manner.
Since you hadn't replied I thought you had fixed the problem and just forgot to close the question. If it's not fixed then tell me what you've tried and I'll continue to work on it with you.
I'll close it out as is. Thanks for helping.
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018