Problem "copying" values

Flash0220

Board Regular
Joined
May 2, 2002
Messages
104
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
ABCDEFGHI
7DiWoDoVrMaDiWoDo
801-01-0202-01-0203-01-0204-01-0207-01-0208-01-0209-01-0210-01-02
9Joe
10Jack
11William
12Averell
13Bill
14John
15Edward
16Pete
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)

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;
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Flash0220

Board Regular
Joined
May 2, 2002
Messages
104
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.

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
 

Forum statistics

Threads
1,143,613
Messages
5,719,713
Members
422,242
Latest member
hishamkhatri

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
Top