simple reference changing weekly?

[WadeWatts]

Hi,

I am trying to pull a week data through from one worksheet to another based on current work week.

example of what im trying to do.
range C5:Q8 is manual input,
range C15:G18 is auto pulled through based on this week number.

this week is week 42, so today() falls within the week, so range C15:G18 shows the data from the input range,
next week when i opened the sheet, today() will be in week 43, i want the weeks data to be pulled through

initially i wrote =if(and(b5=b15,$c$13=weeknum(today())),c5)
seemed to work to pull through but i cant figure out the date to move


any ideas?

thanks

 

[awoohaw]

The formula can probably be made more efficient, but this seems to work. but, I'm not sure you want a count of absences or just to see another "L".
This is assuming weeknumbers start on Sunday (Option 1 for 2nd argument of weeknumber), I used a hard coded date for the formula instead of TODAY() so I could change it as needed.

Mr excel questions 67.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3		Week Nbr	42	42	42	42	42	43	43	43	43	43	44	44	44	44	44
4			2023-10-16	2023-10-17	2023-10-18	2023-10-19	2023-10-20	2023-10-23	2023-10-24	2023-10-25	2023-10-26	2023-10-27	2023-10-30	2023-10-31	2023-11-01	2023-11-02	2023-11-03
5		dave								L		L
6		tony	L						L				L
7		matt		L		L						L
8		pablo						L
9
10
11	2023-10-23	Target Date
12	43	today()
13		week nbr
14		This week	Mon	Tues	Wed	Thu	Fri
15		dave	0	0	1	0	1
16		tony	0	1	0	0	0
17		matt	0	0	0	0	1
18		pablo	1	0	0	0	0
19
WadeWatts

Cell Formulas
Range	Formula
H3:Q3	H3	=C3+1
D4:Q4	D4	=WORKDAY.INTL(C4,1,1)
A12	A12	=WEEKNUM($A$11,1)
C15:G18	C15	=--(DROP(FILTER(HSTACK($B$5:$B$8,DROP(FILTER($C$3:$Q$8,$C$3:$Q$3=$A$12),2)),$B15=$B$15:$B$18),,1)="L")
Dynamic array formulas.

 

[WadeWatts]

thanks for the reply.
I am looking for whatever is in the cell to be returned, including blanks, the L in the example is a reference to a location that team member is going to be, so could be a multitude of different letters.
and i was hoping to use the Today() rather than hard coded so it always pulls the relevant week through.

this example is just a small snippet of what im trying to do, the real thing has a large team and a 12 month look ahead, people put in leave etc on this, so im looking at being able to open it any day and see the returned week

thanks again,

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		Week Nbr	42	42	42	42	42	43	43	43	43	43	44	44	44	44	44
4			16/10/2023	17/10/2023	18/10/2023	19/10/2023	20/10/2023	23/10/2023	24/10/2023	25/10/2023	26/10/2023	27/10/2023	30/10/2023	31/10/2023	01/11/2023	02/11/2023	03/11/2023
5		dave								L		L
6		tony	L						L				L
7		matt		L		L						L
8		pablo						L
9
10
11
12
13		week nbr	42
14		This week	Mon	Tues	Wed	Thu	Fri
15		dave
16		tony	L
17		matt		L		L
18		pablo
Sheet5

Cell Formulas
Range	Formula
C13	C13	=WEEKNUM(TODAY())
C15:G18	C15	=FILTER(FILTER($C$5:$Q$8&"",$B$5:$B$8=B15),$C$3:$Q$3=$C$13)
Dynamic array formulas.

 

[kvsrinivasamurthy]

In C13

=WEEKNUM(TODAY())

IN C15

=LET(a,INDEX($C$5:$Q$8,MATCH($B15:$B18,$B$5:$B$8,0),MATCH($C$13,$C$3:$Q$3,0)+COLUMN($C15:$G15)-COLUMN($C$15)),IF(a="","",a))

 

[WadeWatts]

How about

thanks for the input, appreciate it, on the version of windows im building this on, Filter isnt available to me, only filterxml, and when i tried to use the substitute your formula filter with filterxml, it just returns !VALUE#,

 

[WadeWatts]

In C13

thanks for the input, same as the other potential, not able to see if this will work as the LET function isnt on the version i have on my work laptop, i think LET may be too new to be honest haha

 

[Fluff]

Your profile shows you are using 365, if you need a formula that will work in older version you need to let us know.
What version does this need to work in?

 

[WadeWatts]

Your profile shows you are using 365, if you need a formula that will work in older version you need to let us know.
What version does this need to work in?

apologies, youre right, my MrExcel account is set up on my "Work" laptop, which has 365, I forgot to mention the issue I am working on is on a clients machine, which is pretty locked down,
the instance of excel i am currently working with is office professional plus 2016

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		Week Nbr	42	42	42	42	42	43	43	43	43	43	44	44	44	44	44
4			16/10/2023	17/10/2023	18/10/2023	19/10/2023	20/10/2023	23/10/2023	24/10/2023	25/10/2023	26/10/2023	27/10/2023	30/10/2023	31/10/2023	01/11/2023	02/11/2023	03/11/2023
5		dave								L		L
6		tony	L						L				L
7		matt		L		L						L
8		pablo						L
9
10
11
12
13		week nbr	42
14		This week	Mon	Tues	Wed	Thu	Fri
15		dave	0	0	0	0	0
16		tony	L	0	0	0	0
17		matt	0	L	0	L	0
18		pablo	0	0	0	0	0
Sheet5

Cell Formulas
Range	Formula
C13	C13	=WEEKNUM(TODAY())
C15:G18	C15	=INDEX(INDEX($C$5:$Q$8,MATCH($B15,$B$5:$B$8,0),0),MATCH($C$13,$C$3:$Q$3,0)+COLUMNS($C15:C15)-1)