Index/Match with variable or dynamic sheet

[jgopal]

Hi everyone,

In our current spreadsheet, I am trying to make a dashboard view which will update figures from 12 sheets (January, February, March, etc.) which are months of the year.

All month sheets are the same and have a list of report names (dashboard sheet to will have the same report names), with a target send time (10:00, 11:00.... 15:00) of the reports. Then there are calendar days from 1-28/30/31 depending on the month. The team leader updates the time the report is sent as per the time on the email in the respective 'date' column's cell corresponding to the 'report' row. All this is typical data entry.

Now the challenge I am facing is with getting the time from the month sheet as mentioned below. Obviously, if it is one sheet (e.g. March), I am able to do an index-match and get it but was not able to get the right syntax when the month changes as in the first case.

1. The below works well as long as its only March.
Also, if I copy-paste for the remaining reports, the range B3:AF3 changes as required to B4:AF4 and so on for all the reports.
=INDEX(March!$B3:$AF3,MATCH(DASHBOARD!$B$1,March!$B$2:$AF$2,0))
B1=TODAY()
Range B3:AF3: Time Input cells from 1st to 31st March for Report 1. This will be updated by the team leader as mentioned above.
Range B2:AF2: Locked Cells with dates from 01 to 31.

2. After a lot of search on using the INDIRECT function for dynamic sheetname, I found the below solution.
=INDEX(INDIRECT($B$2 & "!$B3:$AF3"), MATCH($B$1,INDIRECT($B$2 & "!$B$2:$AF$2"),))
B2=TEXT((B1),"mmmm")
Range B3:AF3: Time Input cells from 1st to 31st March for Report 1. This will be updated by the team leader as mentioned above.
Range B2:AF2: Locked Cells with dates from 01 to 31.

However in this solution, when I copy-paste to the cells below, it does not update the range B3:AF3. It copies the same formula as is.

Hope the above information is clear and that someone is able to help in fixing this issue for me.

Thanks
Best Regards
J

 

[Domenic]

Try . . .

=INDEX(INDIRECT($B$2&"!"&CELL("address",$B3)&":"&CELL("address",$AF3)),MATCH($B$1,INDIRECT($B$2&"!$B$2:$AF$2"),))

Actually, it's always a good idea to wrap your sheet names within single quotes, in case of spaces contained within the name . . .

=INDEX(INDIRECT("'"&$B$2 &"'!"&CELL("address",$B3)&":"&CELL("address",$AF3)),MATCH($B$1,INDIRECT("'"&$B$2&"'!$B$2:$AF$2"),))

Hope this helps!

 

[jgopal]

Try . . .

=INDEX(INDIRECT($B$2&"!"&CELL("address",$B3)&":"&CELL("address",$AF3)),MATCH($B$1,INDIRECT($B$2&"!$B$2:$AF$2"),))

Actually, it's always a good idea to wrap your sheet names within single quotes, in case of spaces contained within the name . . .

=INDEX(INDIRECT("'"&$B$2 &"'!"&CELL("address",$B3)&":"&CELL("address",$AF3)),MATCH($B$1,INDIRECT("'"&$B$2&"'!$B$2:$AF$2"),))

Hope this helps!

Wow Domenic, that was fast.
Yes, It worked perfectly.
Appreciate if you could please explain the syntax as I would like to understand what is happening rather than just pasting your code.

Thank you very much
Regards
J

 

[Domenic]

The Cell function can return a variety of information for the specified cell (see the help file for a list of information you're able to obtain). So, for example, with this . . .
CELL("address",$B3)

...the address of the reference is returned, which in this case returns $B$3. When it's copied down to the next cell, the function becomes . . .

CELL("address",$B4)

...since the row reference is a relative reference. And so this time it returns $B$4, and so on. So we simply construct a string, which we pass to the INDIRECT function to return the desired reference. So we first construct our string like this...

$B$2&"!"&CELL("address",$B3)&":"&CELL("address",$AF3)

So, let's say that B2 contains the sheet name "Sheet2", it returns the following string...

Sheet2!$B$3:$AF$3

Then this string is passed to the INDIRECT function, which returns a reference to that range.

Hope this helps!

 

[jgopal]

Hi Domenic,
My apologies for not replying earlier to thank you for the above.
I understood your above explanation and the logic behind it. I think the more I use it, I may get the hang of it.
I am going to post a few more new requirements I have on the same project that I am working on, hope you will be able to assist with that too.
Thanks again
Regards
J

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!

 

[jgopal]

Hi Domenic,

I posted another requirement yesterday wherein the requirement is to capture 6 days input values from the month sheet to generate a weekly report against the above which is for only the current day on the daily dashboard worksheet. So I am not sure how the syntax would be or even if there is a way to do that using Index / Match function.

I didn't get any replies to the post, so thought of giving a shout out to you if you know how if it is possible or not.
Appreciate if you can check the post please for details

Thanks
Regards
J

 

[jgopal]

Hi Domenic,

I applied your solution differently and it works brilliantly however I have a new challenge now. Hope you can help.

The weekly report is for the week's data analysis. So if the date range is March 28, 2022 - April 2, 2022, the lookup month for match and index is only the data in March and it will not search for the data in April. Is there a further tweak that can be done in your code that can search both months? I hope the xl2bb extract explains the situation.

I will update this in my other post as it is still open and without any answer yet.

Regards
J

Report Tracker2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1											INSERT START DATE	INSERT END DATE	WEEK START IN	WEEK ENDS IN
2
3	CODE	TYPE & NAME OF REPORTS	TARGET TIME	Mon 04 Apr	Tue 05 Apr	Wed 06 Apr	Thu 07 Apr	Fri 08 Apr	Sat 09 Apr		Mon 04 Apr	Sat 09 Apr	April	April
4	D1-1	REPORT 1	11:30	13:00	11:00	12:00	11:30	13:00	11:30
5	D1-2	REPORT 2	11:30	12:00	11:45	11:59	11:59	12:15	11:30
Weekly Report

Cell Formulas
Range	Formula
D3	D3	=K3
E3:I3	E3	=D3+1
D4:D5	D4	=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($D$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($D$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
E4:E5	E4	=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($E$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($E$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
F4:F5	F4	=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($F$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($F$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
G4:G5	G4	=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($G$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($G$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
H4:H5	H4	=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($H$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($H$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
I4:I5	I4	=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($I$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($I$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
L3	L3	=K3+5
M3:N3	M3	=TEXT((K3),"mmmm")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:I11	Cell Value	contains "NOT SENT"	text	YES
D5:I11	Cell Value	<=C5	text	YES
D5:I11	Cell Value	between C5+TIME(0,1,0) and C5+TIME(0,30,0)	text	YES
D5:I11	Cell Value	>C5+TIME(0,30,0)	text	YES
D4:I4	Cell Value	contains "NOT SENT"	text	YES
D4:I4	Cell Value	<=C4	text	YES
D4:I4	Cell Value	between C4+TIME(0,1,0) and C4+TIME(0,30,0)	text	YES
D4:I4	Cell Value	>C4+TIME(0,30,0)	text	YES

 

[Fluff]

Duplicate to: Index/Match values for a working week

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.