Dynamic (row) selection in index formula based on date selection

O

Ofnieee

New Member
Joined
Sep 22, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi!

First of all im super new here, so thanks in advance for any help! Normally i would google my self out and figure out a solution but this seemed to conditional in order to find a solution myself.

I've created a roster for my company, and want to create a small overview card of who is working today and on which project. In order to be dynamic, im creating a date picker which should be the base of the variable.

As you can see attached, from G8 till wherever(im creating new months every month;)) the working date is listed. In C8 till E13 i've created a small overview card with the people working on that day. However the formula is now static on Row R as you can see in the formula here.

=FILTER(P9:P50;C9=R9:R50)

I want to make it dynamic, where it by default sets todays day and searches for the hit in the roster, then changes the ranges dynamiccaly based on that day. (1-10 is row R9:R50, 2-10 is S9:S50 etc etc)

See dummy file attached.

Thanks in advance!
 

Attachments

  • Schermafbeelding 2024-09-22 145703.png
    Schermafbeelding 2024-09-22 145703.png
    95.4 KB · Views: 135

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi and welcome to MrExcel

I propose the following to you. You can put the date to filter in cell C7, if you leave it blank, then the formula considers Today's date.

Put the formula in cell C10 and copy it to the right.
The formula assumes that the dates are in cells J9 through AX9.
Excel Formula: 
=FILTER($H$10:$H$50,INDEX($J$10:$AX$50,0,MATCH($C$7,$J$9:$AX$9,0))=IF(C$9="",TODAY(),C$9))

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
 
Upvote 0
Another option, assuming your dates in row 9 are valid Excel dates.
Book1
ABCDEFOPQRSTUVWXYZAAAB
8
923-09-202422-0923-0924-0925-0926-0927-0928-0929-0930-091-10
10VFZODCPEmployee 1ODODODOD
11Employee 2Employee 1Employee 5Employee 2VFZVFZVFZVFZ
12Employee 4Employee 3Employee 8Employee 3ODODOD
13Employee 6Employee 4VFZVFZVFZ
14Employee 5VFZCPCPVFZVFZ
15Employee 6ODODODOD
16Employee 7VFZVFZ
17Employee 8CPCP
18Employee 9
19Employee 10
Sheet1
Cell Formulas
RangeFormula
C9C9=TODAY()
C11:C12,E11:E12,D11:D13C11=LET(ffilt,DROP(FILTER($S$9:$AB$19,$S$9:$AB$9=$C$9),1), DROP(FILTER(HSTACK($P$10:$P$19,ffilt),ffilt=C$10),,-1))
Dynamic array formulas.
 
Upvote 0
DanteAmor said:
Hi and welcome to MrExcel

I propose the following to you. You can put the date to filter in cell C7, if you leave it blank, then the formula considers Today's date.

Put the formula in cell C10 and copy it to the right.
The formula assumes that the dates are in cells J9 through AX9.
Excel Formula: 
=FILTER($H$10:$H$50,INDEX($J$10:$AX$50,0,MATCH($C$7,$J$9:$AX$9,0))=IF(C$9="",TODAY(),C$9))

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
Click to expand...
Hey Dante,

Thanks in advance for your help and input. When i copy your code and set the values to the correct cells, it looks like this:
=FILTER($P$9:$P$50, INDEX($R$9:$ZZ$50, 0, MATCH($C$6, $R$8:$AX$8, 0)) = IF(C$6 = "", TODAY(), C$6))

Basics:
Date = C6
Formula should be in c10(unchanged)
Range of date cells is R9 till the end of the sheet (it should automatically expand when i add new months in the roster)

My original static but functioning formula(hard selected Colum R):
=FILTER(P9:P50;D9=R9:R50)

The output it gives is CALCULATE?

Thanks!
 

Attachments

  • Schermafbeelding 2024-06-05 095628.png
    Schermafbeelding 2024-06-05 095628.png
    52.9 KB · Views: 123
Upvote 0
myall_blues said:
Another option, assuming your dates in row 9 are valid Excel dates.
Book1
ABCDEFOPQRSTUVWXYZAAAB
8
923-09-202422-0923-0924-0925-0926-0927-0928-0929-0930-091-10
10VFZODCPEmployee 1ODODODOD
11Employee 2Employee 1Employee 5Employee 2VFZVFZVFZVFZ
12Employee 4Employee 3Employee 8Employee 3ODODOD
13Employee 6Employee 4VFZVFZVFZ
14Employee 5VFZCPCPVFZVFZ
15Employee 6ODODODOD
16Employee 7VFZVFZ
17Employee 8CPCP
18Employee 9
19Employee 10
Sheet1
Cell Formulas
RangeFormula
C9C9=TODAY()
C11:C12,E11:E12,D11:D13C11=LET(ffilt,DROP(FILTER($S$9:$AB$19,$S$9:$AB$9=$C$9),1), DROP(FILTER(HSTACK($P$10:$P$19,ffilt),ffilt=C$10),,-1))
Dynamic array formulas.
Click to expand...

If i copy your work, i get this reply. I've never worked with "LET" so im directly stuck :D
1727101926973.png
 
Upvote 0
This is the corrected formula, put it in C10 and copy to the right:
=FILTER($P$9:$P$50,INDEX($R$9:$ZZ$50,0,MATCH(IF($C$6="",TODAY(),$C$6),$R$8:$ZZ$8,0))=C$9)​

I explain to you how the formula is made up, so that you can make changes to it if you require it.

Range of cells to be filtered
FILTER($P$9:$P$50​

This range is the dynamic one, so it goes from R9 to the last column with data (ZZ) and the last row with data (50)
$R$9:$ZZ$50​

In this cell you are going to put the date that you want to filter or if you leave it empty, then the formula considers today's date.
IF($C$6="",TODAY(),$C$6​

Row 8 is where all the dates are found.
$R$8:$ZZ$8​

Row 9 is where the key is, starting at C9
C$9​

And an image to see where the data is:
1727109511530.png
Note: when you post an image it must show the row numbers and column letters. Or better yet, if you can put in a sample of data using the XL2BB tool.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
 

Attachments

  • 1727109388004.png
    1727109388004.png
    38.2 KB · Views: 119
Upvote 0
Solution
Ofnieee said:
If i copy your work, i get this reply. I've never worked with "LET" so im directly stuck :D
Click to expand...
I can't explain that one - it works fine for me - unless it is some regional setting thing.

All I can suggest is try changing all occurrences of ffilt to something else. e.g.
Excel Formula: 
=LET(_ff,DROP(FILTER($S$9:$AB$19,$S$9:$AB$9=$C$9),1),DROP(FILTER(HSTACK($P$10:$P$19,_ff),_ff=E$10),,-1))
 
Upvote 0
DanteAmor said:
This is the corrected formula, put it in C10 and copy to the right:
=FILTER($P$9:$P$50,INDEX($R$9:$ZZ$50,0,MATCH(IF($C$6="",TODAY(),$C$6),$R$8:$ZZ$8,0))=C$9)​

I explain to you how the formula is made up, so that you can make changes to it if you require it.

Range of cells to be filtered
FILTER($P$9:$P$50​

This range is the dynamic one, so it goes from R9 to the last column with data (ZZ) and the last row with data (50)
$R$9:$ZZ$50​

In this cell you are going to put the date that you want to filter or if you leave it empty, then the formula considers today's date.
IF($C$6="",TODAY(),$C$6​

Row 8 is where all the dates are found.
$R$8:$ZZ$8​

Row 9 is where the key is, starting at C9
C$9​

And an image to see where the data is:
Note: when you post an image it must show the row numbers and column letters. Or better yet, if you can put in a sample of data using the XL2BB tool.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
Click to expand...
Yea i figured, i was a bit confused on why it wouldnt allow me to post it as a reply instead of in a new thread but i figured that out. I got it working, it broke on regional settings at first but when i changed that to mine(EU uses ; instead of , ) it works. Great man much appreciated!
 
Upvote 1
Last question, promise!

I got everything fully up and running. However, the 3 counta fields(the big highlighted ones on the left) include the index formula if it gives no results as you can see below. I tried other formula's but i didnt get it fully functional.

Roostersalesmates.xlsx
ABCDEFGHIJKLMNOPQRS
1DIT IS VERBORGEN MET EEN REDENDatum:25-09
2Dagplanning voor 25-09MaDiWODOVRIJ
3
41619282120
5
601-0101-0101-01MedewerkerProject 1Project 2Type23-0924-0925-0926-0927-09
7VFZODCPx1OdidoZZP Tier 1VFZVFZVFZVFZ
8   x2CleanprofsOdidoZZP Tier 1VFZVFZVFZVFZVFZ
9x3OdidoZZP Tier 1VFZVFZVFZVFZ
10x4ZiggoZZP Tier 1ZIEKVFZVFZVFZ
11VFZCPODx5ZiggoCleanprofsZZP Tier 2VFZVFZVFZVFZ
12x6OdidoZZP Tier 2VFZVFZ
13x7ZiggoZZP Tier 2VFZVFZVFZ
14x8OdidoZZP Tier 1VFZVFZ
151911x9ZiggoZZP Tier 1VFZVFZVFZVFZ
160x10CleanprofsZiggoZZP Tier 2VFZVFZVFZ
17x1  x11ZiggoZZP Tier 1VFZVFZVFZVFZ
18x2x12ZiggoZZP Tier 1ZIEKZIEKVFZ
19x3x13CleanprofsZZP Tier 1ZIEKVFZVFZ
20x4x14CleanprofsZiggoZZP Tier 1VFZVFZVFZ
21x5x15CleanprofsZiggoZZP Tier 1VFZVFZVFZVFZ
22x6x16ZZP Tier 1VFZVFZVFZVFZ
23x7x17ZZP Tier 1VFZVFZVFZ
24x9x18CleanprofsZZP Tier 1
25x11x19
26x13x20TR
27x14x21StudentZIEKVFZVFZVFZVFZ
Sheet1
Cell Formulas
RangeFormula
L1L1=TODAY()
E2E2= "Dagplanning voor " & TEXT($L$1,"DD-MM")
O4:S4O4=COUNTA(O7:O48)
A6:C6A6=COUNTA(A8:A48)
A8:C8A8=IFERROR(FILTER($K$7:$K$48, INDEX($V$7:$AAE$48, 0, MATCH(IF($L$1="", TODAY(), $L$1), $V$6:$AAE$6, 0)) = A$7), "")
E15,I15,G15E15=COUNTA(E17:E48)
E17:E35E17=IFERROR(FILTER($K$7:$K$48, INDEX($O$7:$AAE$48, 0, MATCH(IF($L$1="", TODAY(), $L$1), $O$6:$AAE$6, 0)) = $E$11), "")
G17G17=IFERROR(FILTER($K$7:$K$48, INDEX($O$7:$AAE$48, 0, MATCH(IF($L$1="", TODAY(), $L$1), $O$6:$AAE$6, 0)) = $G$11), "")
I17I17=IFERROR(FILTER($K$7:$K$48, INDEX($O$7:$AAE$48, 0, MATCH(IF($L$1="", TODAY(), $L$1), $O$6:$AAE$6, 0)) = $I$11), "")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:J48,O2:XFD48,AI49,AP49:AP58,AW49:AW58,BD49:BD58,BK49:BK58,BR49:BR58,BY49:BY58,CE49:XFD49,G49:J1048576,K2:N52Cell Value="NS"textNO
A2:J48,O2:XFD48,AI49,AP49:AP58,AW49:AW58,BD49:BD58,BK49:BK58,BR49:BR58,BY49:BY58,CE49:XFD49,G49:J1048576,K2:N52Cell Value="ZIEK"textNO
A2:J48,O2:XFD48,AI49,AP49:AP58,AW49:AW58,BD49:BD58,BK49:BK58,BR49:BR58,BY49:BY58,CE49:XFD49,G49:J1048576,K2:N52Cell Value="VAK"textNO
A2:J48,O2:XFD48,AI49,AP49:AP58,AW49:AW58,BD49:BD58,BK49:BK58,BR49:BR58,BY49:BY58,CE49:XFD49,G49:J1048576,K2:N52Cell Value="TR"textNO
A2:J48,O2:XFD48,AI49,AP49:AP58,AW49:AW58,BD49:BD58,BK49:BK58,BR49:BR58,BY49:BY58,CE49:XFD49,G49:J1048576,K2:N52Cell Value="CP"textNO
A2:J48,O2:XFD48,AI49,AP49:AP58,AW49:AW58,BD49:BD58,BK49:BK58,BR49:BR58,BY49:BY58,CE49:XFD49,G49:J1048576,K2:N52Cell Value="OD"textNO
A2:J48,O2:XFD48,AI49,AP49:AP58,AW49:AW58,BD49:BD58,BK49:BK58,BR49:BR58,BY49:BY58,CE49:XFD49,G49:J1048576,K2:N52Cell Value="VFZ"textNO
L1Cell Value="NS"textNO
L1Cell Value="ZIEK"textNO
L1Cell Value="VAK"textNO
L1Cell Value="TR"textNO
L1Cell Value="CP"textNO
L1Cell Value="OD"textNO
L1Cell Value="VFZ"textNO
G1:J1,G2:N9,A7:C7,K10:N12,X12:AG14,G13:N14,X16:AG19,AH17,J20,K15:N27,K59:N1048576,V52:W52,V50:W50,Q12:U14,Q16:U19,O20:AG21,O49:AG49,O50:P50,O52:P52,U4:U11,O7:T11,V7:AG11,K9:K48Cell Value="NS"textNO
G1:J1,G2:N9,A7:C7,K10:N12,X12:AG14,G13:N14,X16:AG19,AH17,J20,K15:N27,K59:N1048576,V52:W52,V50:W50,Q12:U14,Q16:U19,O20:AG21,O49:AG49,O50:P50,O52:P52,U4:U11,O7:T11,V7:AG11,K9:K48Cell Value="ZIEK"textNO
G1:J1,G2:N9,A7:C7,K10:N12,X12:AG14,G13:N14,X16:AG19,AH17,J20,K15:N27,K59:N1048576,V52:W52,V50:W50,Q12:U14,Q16:U19,O20:AG21,O49:AG49,O50:P50,O52:P52,U4:U11,O7:T11,V7:AG11,K9:K48Cell Value="VAK"textNO
G1:J1,G2:N9,A7:C7,K10:N12,X12:AG14,G13:N14,X16:AG19,AH17,J20,K15:N27,K59:N1048576,V52:W52,V50:W50,Q12:U14,Q16:U19,O20:AG21,O49:AG49,O50:P50,O52:P52,U4:U11,O7:T11,V7:AG11,K9:K48Cell Value="TR"textNO
G1:J1,G2:N9,A7:C7,K10:N12,X12:AG14,G13:N14,X16:AG19,AH17,J20,K15:N27,K59:N1048576,V52:W52,V50:W50,Q12:U14,Q16:U19,O20:AG21,O49:AG49,O50:P50,O52:P52,U4:U11,O7:T11,V7:AG11,K9:K48Cell Value="CP"textNO
G1:J1,G2:N9,A7:C7,K10:N12,X12:AG14,G13:N14,X16:AG19,AH17,J20,K15:N27,K59:N1048576,V52:W52,V50:W50,Q12:U14,Q16:U19,O20:AG21,O49:AG49,O50:P50,O52:P52,U4:U11,O7:T11,V7:AG11,K9:K48Cell Value="OD"textNO
G1:J1,G2:N9,A7:C7,K10:N12,X12:AG14,G13:N14,X16:AG19,AH17,J20,K15:N27,K59:N1048576,V52:W52,V50:W50,Q12:U14,Q16:U19,O20:AG21,O49:AG49,O50:P50,O52:P52,U4:U11,O7:T11,V7:AG11,K9:K48Cell Value="VFZ"textNO
D1:J1,A2:N3,B4:N4,A5:N7,D8:N8,A9:N9,K10:N12,G13:N13,A14:N14,K15:N17,E18:N27,L1,A10:D13,E15:G15,I15:J15,A15:D17,F16,J17,B18:D18,A19:D19,B20:D20,A1,V1:XFD21,N1:U1,O2:U21,A21:D27,A28:N1048576,O22:XFD1048576,K9:K48Dates OccurringtodaytextNO
BB1,CG1:XFD7,CF2:CF7,AC5:CE5,CE7,AA15:AG15,AC50,AJ50,AQ50,AX50,BE50,BL50,BS50,BZ50,AB50:AB58,CF50:XFD58,V52:W58,AA52:AA58,AC52:AC58,AG52:AG58,AI52:AJ58,AN52:AN58,AQ52:AQ58,AU52:AU58,AX52:AX58,BB52:BB58,BE52:BE58,BI52:BI58,BL52:BL58,BP52:BP58,BS52:BS58Cell Value="NS"textNO
BB1,CG1:XFD7,CF2:CF7,AC5:CE5,CE7,AA15:AG15,AC50,AJ50,AQ50,AX50,BE50,BL50,BS50,BZ50,AB50:AB58,CF50:XFD58,V52:W58,AA52:AA58,AC52:AC58,AG52:AG58,AI52:AJ58,AN52:AN58,AQ52:AQ58,AU52:AU58,AX52:AX58,BB52:BB58,BE52:BE58,BI52:BI58,BL52:BL58,BP52:BP58,BS52:BS58Cell Value="ZIEK"textNO
BB1,CG1:XFD7,CF2:CF7,AC5:CE5,CE7,AA15:AG15,AC50,AJ50,AQ50,AX50,BE50,BL50,BS50,BZ50,AB50:AB58,CF50:XFD58,V52:W58,AA52:AA58,AC52:AC58,AG52:AG58,AI52:AJ58,AN52:AN58,AQ52:AQ58,AU52:AU58,AX52:AX58,BB52:BB58,BE52:BE58,BI52:BI58,BL52:BL58,BP52:BP58,BS52:BS58Cell Value="VAK"textNO
BB1,CG1:XFD7,CF2:CF7,AC5:CE5,CE7,AA15:AG15,AC50,AJ50,AQ50,AX50,BE50,BL50,BS50,BZ50,AB50:AB58,CF50:XFD58,V52:W58,AA52:AA58,AC52:AC58,AG52:AG58,AI52:AJ58,AN52:AN58,AQ52:AQ58,AU52:AU58,AX52:AX58,BB52:BB58,BE52:BE58,BI52:BI58,BL52:BL58,BP52:BP58,BS52:BS58Cell Value="TR"textNO
BB1,CG1:XFD7,CF2:CF7,AC5:CE5,CE7,AA15:AG15,AC50,AJ50,AQ50,AX50,BE50,BL50,BS50,BZ50,AB50:AB58,CF50:XFD58,V52:W58,AA52:AA58,AC52:AC58,AG52:AG58,AI52:AJ58,AN52:AN58,AQ52:AQ58,AU52:AU58,AX52:AX58,BB52:BB58,BE52:BE58,BI52:BI58,BL52:BL58,BP52:BP58,BS52:BS58Cell Value="CP"textNO
BB1,CG1:XFD7,CF2:CF7,AC5:CE5,CE7,AA15:AG15,AC50,AJ50,AQ50,AX50,BE50,BL50,BS50,BZ50,AB50:AB58,CF50:XFD58,V52:W58,AA52:AA58,AC52:AC58,AG52:AG58,AI52:AJ58,AN52:AN58,AQ52:AQ58,AU52:AU58,AX52:AX58,BB52:BB58,BE52:BE58,BI52:BI58,BL52:BL58,BP52:BP58,BS52:BS58Cell Value="OD"textNO
BB1,CG1:XFD7,CF2:CF7,AC5:CE5,CE7,AA15:AG15,AC50,AJ50,AQ50,AX50,BE50,BL50,BS50,BZ50,AB50:AB58,CF50:XFD58,V52:W58,AA52:AA58,AC52:AC58,AG52:AG58,AI52:AJ58,AN52:AN58,AQ52:AQ58,AU52:AU58,AX52:AX58,BB52:BB58,BE52:BE58,BI52:BI58,BL52:BL58,BP52:BP58,BS52:BS58Cell Value="VFZ"textNO
K6:M6Cell Value="V"textNO
K6:M6Cell Value="s"textNO
K6:M6Cell Value="N"textNO
K6:M6Cell Value="T"textNO
K6:M6Cell Value="Z"textNO
K6:M6Cell Value="X"textNO
Cells with Data Validation
CellAllowCriteria
A7:C7List=Roosterinhoud_codes_lijst
E11:J11List=Roosterinhoud_codes_lijst
N7:N49List=Dienstverband_lijst
O7:V49List=Roosterinhoud_codes_lijst
 
Upvote 0
That corresponds to another topic.
Create a new thread.


🤗
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
709
Peter_SSs
Peter_SSs
V
  • Question Question
Need help with excel formulas
Replies
5
Views
239
victoriar
V
Burrgogi
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
936
Burrgogi
Burrgogi
D
  • Question Question
Tally that shifts cells daily.
Replies
3
Views
834
portews
P
L
  • Question Question
Use a Dynamic Range with Dates as a Criteria in FILTER Function
Replies
4
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,224,862
Messages
6,181,464
Members
453,044
Latest member
rgbenson1

Share this page

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
Back
Top