Index/Match with variable or dynamic sheet

Status
Not open for further replies.

jgopal

New Member
Joined
Dec 27, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,523
Office Version
  1. 365
Platform
  1. Windows
Try . . .

Excel Formula:
=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 . . .

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

Hope this helps!
 
Solution

jgopal

New Member
Joined
Dec 27, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Try . . .

Excel Formula:
=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 . . .

Excel Formula:
=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

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,523
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 27, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,523
Office Version
  1. 365
Platform
  1. Windows
You're very welcome, I'm glad I could help.

Cheers!
 

jgopal

New Member
Joined
Dec 27, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Dec 27, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
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
ABCDEFGHIJKLMN
1INSERT START DATEINSERT END DATEWEEK START INWEEK ENDS IN
2
3CODETYPE & NAME OF REPORTSTARGET TIMEMon 04 AprTue 05 AprWed 06 AprThu 07 AprFri 08 AprSat 09 AprMon 04 AprSat 09 AprAprilApril
4D1-1REPORT 111:3013:0011:0012:0011:3013:0011:30
5D1-2REPORT 211:3012:0011:4511:5911:5912:1511:30
Weekly Report
Cell Formulas
RangeFormula
D3D3=K3
E3:I3E3=D3+1
D4:D5D4=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:E5E4=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:F5F4=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:G5G4=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:H5H4=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:I5I4=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"),)))
L3L3=K3+5
M3:N3M3=TEXT((K3),"mmmm")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D5:I11Cell Valuecontains "NOT SENT"textYES
D5:I11Cell Value<=C5textYES
D5:I11Cell Valuebetween C5+TIME(0,1,0) and C5+TIME(0,30,0)textYES
D5:I11Cell Value>C5+TIME(0,30,0)textYES
D4:I4Cell Valuecontains "NOT SENT"textYES
D4:I4Cell Value<=C4textYES
D4:I4Cell Valuebetween C4+TIME(0,1,0) and C4+TIME(0,30,0)textYES
D4:I4Cell Value>C4+TIME(0,30,0)textYES
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,705
Office Version
  1. 365
Platform
  1. Windows
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.
 
Status
Not open for further replies.

Forum statistics

Threads
1,176,098
Messages
5,901,381
Members
434,887
Latest member
zoath

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