Problem "copying" values

[Flash0220]

Hello,


I'm having a little problem (?) copying values to a different tab on the same worksheet.

On the tab "Jaar planner" (= Year planner) the following information is entered.

Jaarplanner Andre.xls
	A	B	C	D	E	F	G	H	I
7		Di	Wo	Do	Vr	Ma	Di	Wo	Do
8		01-01-02	02-01-02	03-01-02	04-01-02	07-01-02	08-01-02	09-01-02	10-01-02
9	Joe
10	Jack
11	William
12	Averell
13	Bill
14	John
15	Edward
16	Pete
Jaar planner

In cells B8:IV8 all day's of 2002 are entered (Jan 1st to Dec 31st).

Cells A9:A53 contain various employee names.

Cells B9:IV53 contain various predefined "Work code's" like "Sales" and "Accounting".

The code's are predefined in A100:A49, and the code's are entered using "Validation".

The following information is entered on the tab "Totalen" (=Totals)

Jaarplanner Andre.xls
	A	B	C	D	E
6		01-01-02	02-01-02	03-01-02	04-01-02
7	Sales	0	0	0	0
8	Accounting	0	0	0	0
9	Omschr.3	0	0	0	0
10	Omschr.4	0	0	0	0
11	Omschr.5	0	0	0	0
12	Omschr.6	0	0	0	0
13	Omschr.7	0	0	0	0
14	Omschr.8	0	0	0	0
Totalen

Cells A7:A56 contains the different (also predefined) work code's.

Cells B6:IV6 contains the date's Jan 1st to Dec 31st.

Cells B7:IV56 contains different calculated values.
For example in cell B7 the number of employee's are calculated, who are doing "Sales" work. (Using the COUNT.IF formula's).
The values needed to do this are on the tab "Jaar planner".

The tab "Week totaal" (=Week total) contains the following information.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=13><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Jaarplanner Andre.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl97 : OS = Windows NT 4</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=13><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb285793><INPUT onclick='window.clipboardData.setData("Text",document.formFb506491.sltNb910359.value);' type=button value="Copy Formula" name=btCb963142></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=13><TABLE border=0><TBODY><TR><FORM name=formFb506491><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb506491.txbFb548867.value = document.formFb506491.sltNb910359.value" name=sltNb910359><OPTION value==WEEKDAG(B9;2)>B7<OPTION value==WEEKDAG(C9;2) selected>C7<OPTION value==WEEKDAG(D9;2)>D7<OPTION value==WEEKDAG(E9;2)>E7<OPTION value==WEEKDAG(F9;2)>F7<OPTION value==WEEKDAG(G9;2)>G7<OPTION value==WEEKDAG(H9;2)>H7<OPTION value==WEEKDAG(I9;2)>I7<OPTION value==WEEKDAG(J9;2)>J7<OPTION value==WEEKDAG(K9;2)>K7<OPTION value==WEEKDAG(L9;2)>L7<OPTION value==VERT.ZOEKEN(B7;Variabelen!$A$7:$B$13;2;ONWAAR)>B8<OPTION value==VERT.ZOEKEN(C7;Variabelen!$A$7:$B$13;2;ONWAAR)>C8<OPTION value==VERT.ZOEKEN(D7;Variabelen!$A$7:$B$13;2;ONWAAR)>D8<OPTION value==VERT.ZOEKEN(E7;Variabelen!$A$7:$B$13;2;ONWAAR)>E8<OPTION value==VERT.ZOEKEN(F7;Variabelen!$A$7:$B$13;2;ONWAAR)>F8<OPTION value==VERT.ZOEKEN(G7;Variabelen!$A$7:$B$13;2;ONWAAR)>G8<OPTION value==VERT.ZOEKEN(H7;Variabelen!$A$7:$B$13;2;ONWAAR)>H8<OPTION value==VERT.ZOEKEN(I7;Variabelen!$A$7:$B$13;2;ONWAAR)>I8<OPTION value==VERT.ZOEKEN(J7;Variabelen!$A$7:$B$13;2;ONWAAR)>J8<OPTION value==VERT.ZOEKEN(K7;Variabelen!$A$7:$B$13;2;ONWAAR)>K8<OPTION value==VERT.ZOEKEN(L7;Variabelen!$A$7:$B$13;2;ONWAAR)>L8<OPTION value==VANDAAG()-5>B9<OPTION value==VANDAAG()-4>C9<OPTION value==VANDAAG()-3>D9<OPTION value==VANDAAG()-2>E9<OPTION value==VANDAAG()-1>F9<OPTION value==VANDAAG()>G9<OPTION value==VANDAAG()+1>H9<OPTION value==VANDAAG()+2>I9<OPTION value==VANDAAG()+3>J9<OPTION value==VANDAAG()+4>K9<OPTION value==VANDAAG()+5>L9<OPTION value="='Jaar planner'!B100">A10<OPTION value="='Jaar planner'!B101">A11<OPTION value="='Jaar planner'!B102">A12<OPTION value="='Jaar planner'!B103">A13<OPTION value="='Jaar planner'!B104">A14<OPTION value="='Jaar planner'!B105">A15<OPTION value="='Jaar planner'!B106">A16<OPTION value="='Jaar planner'!B107">A17</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value==WEEKDAG(C9;2) name=txbFb548867></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>F</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>G</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>H</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>I</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>J</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>K</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>L</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000;

 

[Flash0220]

Hello,


Oops, apparenty the last part of my question did not appear in my previous message's.

Here's the rest and last part of my message.




The tab "Week totaal" (=Week total) contains the following information.

Jaarplanner Andre.xls
	A	B	C	D	E	F	G	H	I	J	K	L
7		5	6	7	1	2	3	4	5	6	7	1
8		Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma
9		4-10-2002	5-10-2002	6-10-2002	7-10-2002	8-10-2002	9-10-2002	10-10-2002	11-10-2002	12-10-2002	13-10-2002	14-10-2002
10	Sales
11	Accounting
12	Omschr.3
13	Omschr.4
14	Omschr.5
15	Omschr.6
16	Omschr.7
17	Omschr.8
Week Totaal

Cells B9:L9 contains different date's.

Cell G9 contains taday's date, (TODAY()).
Cell F9 contains yesterday's date (TODAY()-1), and cell H9 contains tomorrow's date (TODAY()+1)
There will be 5 days calculated in the past, 5 days in the future and the current date.

Cells A10:A59 contain the same "work code's" as used on the tab "Totals" (for example "Sales" and "Accounting").

Cells B10:L59 must contain different values.
For example in the "G" column (Today's date) all values have to be copied from the corresponding column (Today's date) in the tab "Totalen".
The same thing has to be done for the date's in the past and in the future.
Since the current date in column "G" changes every day, I need a formula wich searches for the corresponding value (Today's date) in the tab "Totalen" and "copies" them to the "G" column on the tab "Week totalen".
And of course the same thing has to be done for the 5 day's in the past and the 5 day's in the future.

Is there a way to do this using formula's only ?
I'm not that good in programming in VBA.

I would appreciate if anyone could help me.


Best regards,
Frits Jager