Automatically Updating Worksheets within a Workbook

scharm

New Member
Joined
Mar 31, 2011
Messages
2
I have a primary spreadsheet with various text and date information in the range of A6 to 035, which I need to automatically update the respective calendar within the workbook. i.e. march, april, may, june, etc.

I thought we could use some VBA code. [I am a beginner with VBA]

I know that it is possible within Access. Is this possible within Excel?

Thank you for your input.
scharm
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
Welcome to the Board!
When the data from the primary is changed, the corresponding worksheet data could also be changed, possibly with formulas or VBA, but more information is needed. Please provide the primary worksheet ranges for the data that will update a particular month.

Use Excel Jeanie (see link in my sig) to display the appropriate source and destination portions of your workbook.
 

scharm

New Member
Joined
Mar 31, 2011
Messages
2
Thank you for your response.
How it should work: Basically the user inputs the information into the primary worksheet (Overall), when the user hits the enter key, I would like for the information to automatically be updated in the corresponding calendar. Example: The date changed in row 6 column f to April 12th, I would like the information from A6, B6, C6, F5 and F6 to be placed in the April calendar on the 12th day.

Can this be done in Excel?


Excel Workbook
ABCDEFGHIJKLMNO
4ABCB1 DelCB2 DelProL
5RegNamePriorityK-O/BrnstmIntWTCB1ABCBrfIntWTCB2TimelineP-LCL
6PFoA31-Dec-1014-Jan-1118-Jan-1119-Jan-1119-Jan-1120-Jan-1118-Feb-1122-Feb-1110-Feb-1126-Feb-1128-Jan-1129-May-11
7PExB31-Dec-1014-Jan-1118-Jan-1119-Jan-1119-Jan-1120-Jan-1118-Feb-1122-Feb-1110-Feb-1126-Feb-1128-Jan-1114-Mar-11
8PEs-12A7-Oct-1121-Oct-1125-Oct-1119-Oct-1119-Oct-1120-Oct-119-Dec-1113-Dec-1121-Dec-1116-Dec-115-Mar-12Q1 2012
9P/CRB13-May-1127-May-112-Jun-1118-May-1118-May-1119-May-1122-Jul-1126-Jul-1120-Jul-1129-Jul-113-Oct-116-Nov-11
10P/CPBTBDNANANANANANANANANANANANA
11
12FPBANANANANANANA11-Mar17-Mar20-Mar-1123-MarNA13-Jun-11
13FExA11-Feb-1125-Feb-113-Mar-118-Mar-118-Mar-119-Mar-111-Apr-117-Apr-1112-Apr-1115-Apr-115-Jul-1119-Sep-11
14FFiCNANANANANANANANANANANANA
15FFoA18-May-111-Jun-115-Jun-117-Jun-117-Jun-118-Jun-116-Jul-1110-Jul-1112-Jul-1115-Jul-115-Sep-117-Nov-11
16FRA27-May-1110-Jun-1116-Jun-117-Jun-117-Jun-118-Jun-115-Aug-1111-Aug-119-Aug-1119-Aug-117-Nov-112-Jan-12
17FEs-KuC21-Oct-114-Nov-118-Nov-118-Nov-118-Nov-119-Nov-119-Dec-1113-Dec-1113-Dec-1116-Dec-11NAQ1 2012
18FFuTBD13-Apr-1227-Apr-123-May-12Q4 2011Q4 2011NA1-Jun-127-Jun-12Q1 201215-Jun-126-Aug-12Q1 2012
19
20SKFuANANANA20-Jan-1120-Jan-1121-Jan-11NA18-Mar-1117-Feb-111-Mar-1127-Mar-118-May-11
21SKExB21-Jan-114-Feb-118-Feb-1117-Feb-1117-Feb-1118-Feb-114-Mar-118-Mar-1110-Mar-1118-Mar-1110-Apr-116-Jun-11
22SKFoA14-Feb-1128-Feb-113-Mar-1110-Mar-1110-Mar-1111-Mar-111-Apr-117-Apr-1121-Apr-1115-Apr-113-Jul-114-Sep-11
23SKEs-KuA21-Oct-114-Nov-118-Nov-1117-Nov-1117-Nov-1118-Nov-119-Dec-1113-Dec-1115-Dec-1116-Dec-11NA5-Mar-12
24SKFu-MoTBD13-Apr-1227-Apr-123-May-12TBDTBDNA1-Jun-127-Jun-12TBD15-Jun-126-Aug-12TBD
25SKPBTBDNANANANANANANANANANANANA
26
27MRA8-Jul-1122-Jul-1128-Jul-1118-Aug-1118-Aug-1119-Aug-1126-Aug-111-Sep-1115-Sep-119-Sep-1131-Oct-112-Jan-12
28MPBTBDNANANANANANANANANANANANA
29
30SSAFoB18-May-111-Jun-115-Jun-1117-Jun-1117-Jun-1120-Jun-116-Jul-1110-Jul-1115-Jul-1115-Jul-113-Oct-117-Nov-11
31SSARA13-May-1127-May-112-Jun-1117-Jun-1117-Jun-1120-Jun-116-Jul-1110-Jul-1115-Jul-1115-Jul-112-Oct-111-Jan-12
32SSAEs-KuB21-Oct-114-Nov-118-Nov-1118-Nov-1118-Nov-1121-Nov-119-Dec-1113-Dec-1116-Dec-1116-Dec-11NA5-Mar-12
33SSAFiBNANANA8-Apr-118-Apr-1111-Apr-11NANA22-Apr-11NA5-Jun-113-Jul-11
34SSAExCNANANANANANANANANANANA5-Apr-11
35SSAPBTBDNANANANANANANANANANANA5-Apr-11
Overall
Excel Workbook
ABCDEFG
1March 2011
2SundayMondayTuesdayWednesdayThursdayFridaySaturday
312345
4
5
6
7
8
96789101112
10
11
12
13
14
15
1613141516171819
17
18
19
20
21
22
2320212223242526
24
25
26
27
28
29
302728293031
31
32
33
34
35
36
March
Excel Workbook
ABCDEFG
1April 2011
2SundayMondayTuesdayWednesdayThursdayFridaySaturday
312
4
5
6
7
83456789
9
10
11
12
13
1410111213141516
15
16
17
18
19
2017181920212223
21
22
23
24
25
2624252627282930
27
28
29
30
31
April
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
The layout of your calendars would make the code more complex. Would this calendar work for you? It can easily be used for any month/year. Copy this layout to multiple worksheets then change the value in A2. The border immediately above each date should be cleared out - Excel Jeanie put in a border that was not present in my worksheets.

Month

<table style="font-family: Arial,Arial; font-size: 12pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="width: 30px;"><col style="width: 145px;"><col style="width: 145px;"><col style="width: 145px;"><col style="width: 145px;"><col style="width: 145px;"><col style="width: 133px;"><col style="width: 145px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="text-align: center;">First Day</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: center;">04/01/2011</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 27px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td colspan="7" style="font-weight: bold; font-size: 14pt; text-align: center;">April 2011</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="font-size: 16pt; text-align: center;">Sunday</td><td style="font-size: 16pt; text-align: center;">Monday</td><td style="font-size: 16pt; text-align: center;">Tuesday</td><td style="font-size: 16pt; text-align: center;">Wednesday</td><td style="font-size: 16pt; text-align: center;">Thursday</td><td style="font-size: 16pt; text-align: center;">Friday</td><td style="font-size: 16pt; text-align: center;">Saturday</td></tr><tr style="height: 78px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td><td style="font-weight: bold; font-size: 16pt; text-align: right;">1</td><td style="font-weight: bold; font-size: 16pt; text-align: right;">2</td></tr><tr style="height: 78px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="font-weight: bold; font-size: 8pt;">
</td><td style="font-weight: bold; font-size: 8pt;">
</td><td style="font-weight: bold; font-size: 8pt;">
</td><td style="font-weight: bold; font-size: 8pt;">
</td><td style="font-weight: bold; font-size: 8pt;">
</td><td style="font-weight: bold; font-size: 8pt;">
</td><td style="font-weight: bold; font-size: 8pt;">
</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="font-size: 16pt; text-align: right;">3</td><td style="font-size: 16pt; text-align: right;">4</td><td style="font-size: 16pt; text-align: right;">5</td><td style="font-size: 16pt; text-align: right;">6</td><td style="font-size: 16pt; text-align: right;">7</td><td style="font-size: 16pt; text-align: right;">8</td><td style="font-size: 16pt; text-align: right;">9</td></tr><tr style="height: 78px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="font-size: 16pt; text-align: right;">10</td><td style="font-size: 16pt; text-align: right;">11</td><td style="font-size: 16pt; text-align: right;">12</td><td style="font-size: 16pt; text-align: right;">13</td><td style="font-size: 16pt; text-align: right;">14</td><td style="font-size: 16pt; text-align: right;">15</td><td style="font-size: 16pt; text-align: right;">16</td></tr><tr style="height: 78px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="font-size: 16pt; text-align: right;">17</td><td style="font-size: 16pt; text-align: right;">18</td><td style="font-size: 16pt; text-align: right;">19</td><td style="font-size: 16pt; text-align: right;">20</td><td style="font-size: 16pt; text-align: right;">21</td><td style="font-size: 16pt; text-align: right;">22</td><td style="font-size: 16pt; text-align: right;">23</td></tr><tr style="height: 78px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="font-size: 16pt; text-align: right;">24</td><td style="font-size: 16pt; text-align: right;">25</td><td style="font-size: 16pt; text-align: right;">26</td><td style="font-size: 16pt; text-align: right;">27</td><td style="font-size: 16pt; text-align: right;">28</td><td style="font-size: 16pt; text-align: right;">29</td><td style="font-size: 16pt; text-align: right;">30</td></tr><tr style="height: 78px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td><td style="font-size: 8pt;">
</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td><td style="font-size: 16pt;">
</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>A3</td><td>=DATE(YEAR($A$2),MONTH($A$2),1)</td></tr><tr><td>A6</td><td>=IF(WEEKDAY(A3,1)=1,A3,"")</td></tr><tr><td>B6</td><td>=IF(LEN(A6)>0,A6+1,(IF(WEEKDAY(A3,1)=2,A3,"")))</td></tr><tr><td>C6</td><td>=IF(LEN(B6)>0,B6+1,(IF(WEEKDAY(A3,1)=3,A3,"")))</td></tr><tr><td>D6</td><td>=IF(LEN(C6)>0,C6+1,(IF(WEEKDAY(A3,1)=4,A3,"")))</td></tr><tr><td>E6</td><td>=IF(LEN(D6)>0,D6+1,(IF(WEEKDAY(A3,1)=5,A2,"")))</td></tr><tr><td>F6</td><td>=IF(LEN(E6)>0,E6+1,(IF(WEEKDAY(A3,1)=6,A3,"")))</td></tr><tr><td>G6</td><td>=IF(LEN(F6)>0,F6+1,(IF(WEEKDAY(A3,1)=7,A3,"")))</td></tr><tr><td>A8</td><td>=G6+1</td></tr><tr><td>B8</td><td>=A8+1</td></tr><tr><td>C8</td><td>=B8+1</td></tr><tr><td>D8</td><td>=C8+1</td></tr><tr><td>E8</td><td>=D8+1</td></tr><tr><td>F8</td><td>=E8+1</td></tr><tr><td>G8</td><td>=F8+1</td></tr><tr><td>A10</td><td>=G8+1</td></tr><tr><td>B10</td><td>=A10+1</td></tr><tr><td>C10</td><td>=B10+1</td></tr><tr><td>D10</td><td>=C10+1</td></tr><tr><td>E10</td><td>=D10+1</td></tr><tr><td>F10</td><td>=E10+1</td></tr><tr><td>G10</td><td>=F10+1</td></tr><tr><td>A12</td><td>=G10+1</td></tr><tr><td>B12</td><td>=A12+1</td></tr><tr><td>C12</td><td>=B12+1</td></tr><tr><td>D12</td><td>=C12+1</td></tr><tr><td>E12</td><td>=D12+1</td></tr><tr><td>F12</td><td>=E12+1</td></tr><tr><td>G12</td><td>=F12+1</td></tr><tr><td>A14</td><td>=IF(G12="","",IF(MONTH(G12+1)=MONTH(A10),G12+1,""))</td></tr><tr><td>B14</td><td>=IF(A14="","",IF(MONTH(A14+1)=MONTH(B10),A14+1,""))</td></tr><tr><td>C14</td><td>=IF(B14="","",IF(MONTH(B14+1)=MONTH(C10),B14+1,""))</td></tr><tr><td>D14</td><td>=IF(C14="","",IF(MONTH(C14+1)=MONTH(D10),C14+1,""))</td></tr><tr><td>E14</td><td>=IF(D14="","",IF(MONTH(D14+1)=MONTH(E10),D14+1,""))</td></tr><tr><td>F14</td><td>=IF(E14="","",IF(MONTH(E14+1)=MONTH(F10),E14+1,""))</td></tr><tr><td>G14</td><td>=IF(F14="","",IF(MONTH(F14+1)=MONTH(G10),F14+1,""))</td></tr><tr><td>A16</td><td>=IF(G14="","",IF(MONTH(G14+1)=MONTH(A12),G14+1,""))</td></tr><tr><td>B16</td><td>=IF(A16="","",IF(MONTH(A16+1)=MONTH(B12),A16+1,""))</td></tr><tr><td>C16</td><td>=IF(B16="","",IF(MONTH(B16+1)=MONTH(C12),B16+1,""))</td></tr><tr><td>D16</td><td>=IF(C16="","",IF(MONTH(C16+1)=MONTH(D12),C16+1,""))</td></tr><tr><td>E16</td><td>=IF(D16="","",IF(MONTH(D16+1)=MONTH(E12),D16+1,""))</td></tr><tr><td>F16</td><td>=IF(E16="","",IF(MONTH(E16+1)=MONTH(F12),E16+1,""))</td></tr><tr><td>G16</td><td>=IF(F16="","",IF(MONTH(F16+1)=MONTH(G12),F16+1,""))</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web - Excel Jeanie Html 4

I presume that if F6 was changed to Apr 12, that you would want the data that was formerly in Jan 18 to be removed.

Is there a maximum number of events that could occur on a single date?

W5 header occurs in the CB1 Del and CB2 Del groups. Should CBx be copied as well? What should be copied if K2 is changed?

If any of the dates (col D-O) on the primary worksheet are changed what are the corresponding values that should be copied to the appropriate date block

What should happen if a value in column A, B, or C is changed? Should all dates in the changed row be updated?

Does the yellow background colors convey any information that should be copied to the date block? How about the red character values?

Does the order of the entries in each date block matter?

I believe this would be possible in Excel - I also believe it would be slow (1-2 min to update) and somewhat complex to build.

I would recommend that when changes that are made in the primary page a indicator would display that recalculation was needed. Once all changes for a particular session are made the code could be invoked by pushing a command button, (or automatically prior to printing or saving).

The code will examine each of the cells in D6:O35 and copy the date and the appropriate other cell values to a new worksheet that will contain a list of all the current events.

Sort that worksheet by date.

For like dates, concatenate all rows into a single cell (with hard line breaks) set the font size to 10 (or smaller as required to show all entries).

Clear the current calendars (optionally, clear only the cells that have new data, leave old data in cells with no new data).

Copy the concatenated values to the appropriate cells.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,416
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top