dynamic chain between files

[KlausW]

Hi
I have a file where I collect the employees' service and there is a chain that updates between files that the employees enter their service in and a collection file so that I can see the employees' service throughout the year. From April to March.
But since new employees arrive once in a while and I make new files they have to enter their service in, so the chain has to be dynamic. I can create a general chain between Excel files.
But when I change the employee's name in the Name sheet from cell D3 down to D21, here are all the employees' names, the chain should also be changed.

Some who can help.

Any help will be appreciated

Best regards

Klaus W

 

[awoohaw]

Hi Klaus. what do you mean by "chain"? It would be helpful if you provided some sample data to work with. Mr. Excel has a tool called xl2bb add in that allows users to post mini worksheets into a post. The link is below. If you can't do that, then please post a table ... and please label the column and rows and post in the text any necessary formulas you have.

Thanks in advance.

 

[KlausW]

Hi awoohaw

I've never used this tool, so I hope that's what you mean.

Tjenesteliste Donald Duck.xlsm
	D
April

Tjenesteliste opsamling.xlsm
	F
22
April to March

 

[KlausW]

s

 

[Joe4]

You need to first select the whole range you want to copy to the board.

 

[KlausW]

Like this

Tjenesteliste opsamling.xlsm
	A	B	C		E	F	G	H	I
4
5			April 2024
6
7					2. Påskedag
8					01-04-2024	02-04-2024	03-04-2024	04-04-2024	05-04-2024
9					Mandag	Tirsdag	Onsdag	Torsdag	Fredag
11		Donald Duck	Planlagt TJ.		s	T	T
12			Faktisk Tjeneste
13			Fra
14			Til
15			Planlagt TJ.
16			Faktisk Tjeneste
17			Fra
18			Til
19			Planlagt TJ.
20			Faktisk Tjeneste
21			Fra
22			Til
23			Planlagt TJ.
24			Faktisk Tjeneste
25			Fra
26			Til
April to March

Cell Formulas
Range	Formula
C5	C5	=PROPER(TEXT(C2,"mmmm")) & " " & YEAR(C2)
E7:I7	E7	=HelligdagsNavn(E8,0,0)
E8	E8	=C2
F8:I8	F8	=E8+1
E9:I9	E9	=PROPER(TEXT(E8,"dddd"))
E11:I11	E11	='D:\[Tjenesteliste Donald Duck.xlsm]April'!E$11
B11	B11	=Name!D3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E12:AI14	Cell Value	="SY"	text	NO
E12:AI14	Cell Value	contains "H"	text	NO
E12:AI14	Cell Value	contains "F"	text	NO
E11:AI14	Cell Value	="pd"	text	NO
E12:AI14	Cell Value	contains "T"	text	NO
E12:AI14	Cell Value	contains "S"	text	NO
E12:AI14	Cell Value	contains "V"	text	NO
E9:AU10,AK10:NE10	Expression	=OG(UGEDAG(E$8;2)>5;MÅNED(E$8)=MÅNED($C$2))	text	NO
A9:XED10	Expression	=C$3	text	YES

Tjenesteliste Donald Duck.xlsm
	A	B	C	D	E	F	G	H	I
4					TJENESTELISTE FOR
5			April 2024
6
9					Mandag	Tirsdag	Onsdag	Torsdag	Fredag
10					1	2	3	4	5
11		Donald Duck	Planlagt TJ.	1	s	T	T
12			Faktisk Tjeneste
13			Fra
14			Til
15
April

Cell Formulas
Range	Formula
C5	C5	=PROPER(TEXT(C2,"mmmm")) & " " & YEAR(C2)
E9:I9	F9	=PROPER(TEXT(F8,"dddd"))
E10:I10	F10	=DAY(F8)
F11:I11	F11	='D:\[Tjenesteliste For Christian Bøving.xlsm]April'!D$35
B11	B11	=Navn!D3
D11:D12	D11	=COUNTIF($E11:$AH11,"s")
D13:D14	D13	=COUNTIF($E11:$AH11,"f")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E12:AH14	Cell Value	="SY"	text	NO
E12:AH14	Cell Value	contains "H"	text	NO
E12:AH14	Cell Value	contains "F"	text	NO
E11:AH14	Cell Value	="pd"	text	NO
E12:AH14	Cell Value	contains "T"	text	NO
E12:AH14	Cell Value	contains "S"	text	NO
E12:AH14	Cell Value	contains "V"	text	NO
E9:AH10	Expression	=OG(UGEDAG(E$8;2)>5;MÅNED(E$8)=MÅNED($C$2))	text	NO
A9:AF10,AI9:WRF10	Expression	=C$3	text	YES