Automatically Updating Worksheets within a Workbook

[scharm]

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

 

[pbornemeier]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4				AB	CB1 Del					CB2 Del			Pro	L
5	Reg	Name	Priority	K-O/Brnstm	Int	WT	CB1	AB	CBrf	Int	WT	CB2	Timeline	P-L	CL
6	P	Fo	A	31-Dec-10	14-Jan-11	18-Jan-11	19-Jan-11	19-Jan-11	20-Jan-11	18-Feb-11	22-Feb-11	10-Feb-11	26-Feb-11	28-Jan-11	29-May-11
7	P	Ex	B	31-Dec-10	14-Jan-11	18-Jan-11	19-Jan-11	19-Jan-11	20-Jan-11	18-Feb-11	22-Feb-11	10-Feb-11	26-Feb-11	28-Jan-11	14-Mar-11
8	P	Es-12	A	7-Oct-11	21-Oct-11	25-Oct-11	19-Oct-11	19-Oct-11	20-Oct-11	9-Dec-11	13-Dec-11	21-Dec-11	16-Dec-11	5-Mar-12	Q1 2012
9	P/C	R	B	13-May-11	27-May-11	2-Jun-11	18-May-11	18-May-11	19-May-11	22-Jul-11	26-Jul-11	20-Jul-11	29-Jul-11	3-Oct-11	6-Nov-11
10	P/C	PB	TBD	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
11
12	F	PB	A	NA	NA	NA	NA	NA	NA	11-Mar	17-Mar	20-Mar-11	23-Mar	NA	13-Jun-11
13	F	Ex	A	11-Feb-11	25-Feb-11	3-Mar-11	8-Mar-11	8-Mar-11	9-Mar-11	1-Apr-11	7-Apr-11	12-Apr-11	15-Apr-11	5-Jul-11	19-Sep-11
14	F	Fi	C	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
15	F	Fo	A	18-May-11	1-Jun-11	5-Jun-11	7-Jun-11	7-Jun-11	8-Jun-11	6-Jul-11	10-Jul-11	12-Jul-11	15-Jul-11	5-Sep-11	7-Nov-11
16	F	R	A	27-May-11	10-Jun-11	16-Jun-11	7-Jun-11	7-Jun-11	8-Jun-11	5-Aug-11	11-Aug-11	9-Aug-11	19-Aug-11	7-Nov-11	2-Jan-12
17	F	Es-Ku	C	21-Oct-11	4-Nov-11	8-Nov-11	8-Nov-11	8-Nov-11	9-Nov-11	9-Dec-11	13-Dec-11	13-Dec-11	16-Dec-11	NA	Q1 2012
18	F	Fu	TBD	13-Apr-12	27-Apr-12	3-May-12	Q4 2011	Q4 2011	NA	1-Jun-12	7-Jun-12	Q1 2012	15-Jun-12	6-Aug-12	Q1 2012
19
20	SK	Fu	A	NA	NA	NA	20-Jan-11	20-Jan-11	21-Jan-11	NA	18-Mar-11	17-Feb-11	1-Mar-11	27-Mar-11	8-May-11
21	SK	Ex	B	21-Jan-11	4-Feb-11	8-Feb-11	17-Feb-11	17-Feb-11	18-Feb-11	4-Mar-11	8-Mar-11	10-Mar-11	18-Mar-11	10-Apr-11	6-Jun-11
22	SK	Fo	A	14-Feb-11	28-Feb-11	3-Mar-11	10-Mar-11	10-Mar-11	11-Mar-11	1-Apr-11	7-Apr-11	21-Apr-11	15-Apr-11	3-Jul-11	4-Sep-11
23	SK	Es-Ku	A	21-Oct-11	4-Nov-11	8-Nov-11	17-Nov-11	17-Nov-11	18-Nov-11	9-Dec-11	13-Dec-11	15-Dec-11	16-Dec-11	NA	5-Mar-12
24	SK	Fu-Mo	TBD	13-Apr-12	27-Apr-12	3-May-12	TBD	TBD	NA	1-Jun-12	7-Jun-12	TBD	15-Jun-12	6-Aug-12	TBD
25	SK	PB	TBD	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
26
27	M	R	A	8-Jul-11	22-Jul-11	28-Jul-11	18-Aug-11	18-Aug-11	19-Aug-11	26-Aug-11	1-Sep-11	15-Sep-11	9-Sep-11	31-Oct-11	2-Jan-12
28	M	PB	TBD	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
29
30	SSA	Fo	B	18-May-11	1-Jun-11	5-Jun-11	17-Jun-11	17-Jun-11	20-Jun-11	6-Jul-11	10-Jul-11	15-Jul-11	15-Jul-11	3-Oct-11	7-Nov-11
31	SSA	R	A	13-May-11	27-May-11	2-Jun-11	17-Jun-11	17-Jun-11	20-Jun-11	6-Jul-11	10-Jul-11	15-Jul-11	15-Jul-11	2-Oct-11	1-Jan-12
32	SSA	Es-Ku	B	21-Oct-11	4-Nov-11	8-Nov-11	18-Nov-11	18-Nov-11	21-Nov-11	9-Dec-11	13-Dec-11	16-Dec-11	16-Dec-11	NA	5-Mar-12
33	SSA	Fi	B	NA	NA	NA	8-Apr-11	8-Apr-11	11-Apr-11	NA	NA	22-Apr-11	NA	5-Jun-11	3-Jul-11
34	SSA	Ex	C	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	5-Apr-11
35	SSA	PB	TBD	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	5-Apr-11
Overall

Excel Workbook
	A	B	C	D	E	F	G
1	March 2011
2	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3			1	2	3	4	5
4
5
6
7
8
9	6	7	8	9	10	11	12
10
11
12
13
14
15
16	13	14	15	16	17	18	19
17
18
19
20
21
22
23	20	21	22	23	24	25	26
24
25
26
27
28
29
30	27	28	29	30	31
31
32
33
34
35
36
March

Excel Workbook
	A	B	C	D	E	F	G
1	April 2011
2	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3						1	2
4
5
6
7
8	3	4	5	6	7	8	9
9
10
11
12
13
14	10	11	12	13	14	15	16
15
16
17
18
19
20	17	18	19	20	21	22	23
21
22
23
24
25
26	24	25	26	27	28	29	30
27
28
29
30
31
April

 

[pbornemeier]

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.