Simplifying Formula by Predicting Worksheet Name

[Bounces]

Hi there. Been a while since I have been here with a problem.

I am looking to simplify a nasty 7-nested if that selects which worksheet to look at by just looking at a cell with the worksheet name in it instead. This is for a call center schedule, so there is a worksheet tab for each day of that production week.

Here's what I have right now:

=IFERROR(IF($B$2="Monday",INDEX('MON 08.16'!$C$19:$CT$59,MATCH('Schedule Reader'!A5,'MON 08.16'!$A$19:$A$59,0),MATCH('Schedule Reader'!E$1,'MON 08.16'!$C$5:$CT$5,1)-1),IF($B$2="Tuesday",INDEX('TUE 08.17'!$C$19:$CT$59,MATCH('Schedule Reader'!A5,'TUE 08.17'!$A$19:$A$59,0),MATCH('Schedule Reader'!E$1,'TUE 08.17'!$C$5:$CT$5,1)-1),IF($B$2="Wednesday",INDEX('WED 08.18'!$C$19:$CT$59,MATCH('Schedule Reader'!A5,'WED 08.18'!$A$19:$A$59,0),MATCH('Schedule Reader'!E$1,'WED 08.18'!$C$5:$CT$5,1)-1),IF($B$2="Thursday",INDEX('THU 08.19'!$C$19:$CT$59,MATCH('Schedule Reader'!A5,'THU 08.19'!$A$19:$A$59,0),MATCH('Schedule Reader'!E$1,'FRI 08.20'!$C$5:$CT$5,1)-1),IF($B$2="Friday",INDEX('FRI 08.20'!$C$19:$CT$59,MATCH('Schedule Reader'!A5,'FRI 08.20'!$A$19:$A$59,0),MATCH('Schedule Reader'!E$1,'FRI 08.20'!$C$5:$CT$5,1)-1),IF($B$2="Saturday",INDEX('SAT 08.21'!$C$19:$CT$59,MATCH('Schedule Reader'!A5,'SAT 08.21'!$A$19:$A$59,0),MATCH('Schedule Reader'!E$1,'SAT 08.21'!$C$5:$CT$5,1)-1),IF($B$2="Sunday",INDEX('SUN 08.22'!$C$19:$CT$59,MATCH('Schedule Reader'!A5,'SUN 08.22'!$A$19:$A$59,0),MATCH('Schedule Reader'!E$1,'SUN 08.22'!$C$5:$CT$5,1)-1),"Day of Week Error"))))))),"Off")

Its ugly and next week I will have to go and manually change each of the sheet names in the formula since they are hardcoded.

I have a cell J17 on the Schedule Reader tab that reports what the name of the Worksheet we need to reference is:

=UPPER(TEXT(B1,"ddd"))&" "&TEXT(B1,"MM")&"."&TEXT(B1,"dd")

For example: For today it returns MON 08.16, which is the name of the tab with Today's schedule on it.

So how do I eliminate the nested ifs and get just a single IFERROR(INDEX(MATCH()MATCH())) that correctly selects the tab to look at using the info in J17?

 

[Crystalyzer]

What version of excel are you using? Can you post a sample of your data using the XL2BB tool?

 

[Misca]

The INDIRECT function turns text into range references.

If you have your sheet name in A1 you can create the reference:

=INDIRECT("'"&A1&"'!C5")

 

[Bounces]

What version of excel are you using? Can you post a sample of your data using the XL2BB tool?

We are in office 365 so it is a .xlsx file I probably can, but since I am on a work PC they get all nervous about private add-ons.

I can post an image of what I am working with... blacked out the agent names to protect anonymity.

The formula I referenced is in all of the interval cells (B6:D45) It goes and looks at the agents expected activity for that interval and returns it to this sheet...meant to be used as a tool for Real Time Adherence Monitoring...are they where they are supposed to be when they are supposed to be there type of stuff.

 

[Bounces]

The INDIRECT function turns text into range references.

If you have your sheet name in A1 you can create the reference:

=INDIRECT("'"&A1&"'!C5")

I tried that...but the INDIRECT function did not seem to play nice with cell ranges and being nested inside INDEX and MATCH

=INDEX(INDIRECT(" ' "&$J17&"'!$C$19:$CT$59.... threw an #error.

 

[Bounces]

Respectfully I stand corrected. 10 points to MISCA, because indirect did work.

=INDEX(INDIRECT("'"& $C$6 &"'!$C$19:$CT$59"),MATCH(A12,INDIRECT("'"&$C$6&"'!$A$19:$A$59"),0),MATCH(E$1,INDIRECT("'"&$C$6&"'!$c$5:$ct$5"),1)-1)

Apparently I just had a syntax error in there somewhere the first time. Can't imagine why.

 

[Crystalyzer]

I think INDIRECT will get you there. I have a super simplified version of your workbook as follows. The yellow highlighted cell is a helper and can be number formatted with ;;; to make it disappear OR you can include that formula in your INDEX formulas (less desireable). Changing the date in cell B1 changes the value of Cell B2 and C2 and therefore updates your INDEX formula.

Book5
	A	B	C	D	E
1	Current Time	8/16/2021 0:00		Current Interval	3:00:00 PM
2	Day	Monday	'MON 08.16'!
3
4	Agent Name	Previous Interval	Current Interval	Next Interval
5	A	45
6	B
7	C
8	D
9	E
Schedule Reader

Cell Formulas
Range	Formula
B2	B2	=B1
C2	C2	="'"&UPPER(TEXT(B2,"ddd"))&" "&TEXT(MONTH(B1),"00")&"."&TEXT(DAY(B1),"00")&"'!"
B5	B5	=INDEX(INDIRECT($C$2&"$C$19:$CT$59"),MATCH(A5,INDIRECT($C$2&"$A$19:$A$59"),0),MATCH(E$1,INDIRECT($C$2&"$C$5:$g$5"),1))

Book5
	A	B	C	D	E	F	G
1
2
3
4
5			12:00:00 PM	1:00:00 PM	2:00:00 PM	3:00:00 PM	4:00:00 PM
6
7
8
9
10
11
12
13
14
15
16
17
18
19	A					45
20
21
22
23
24
25
26
27
28
29
30
31
32
MON 08.16

 

[Bounces]

It did end up getting me there...I think the issue was a syntax problem on my side.

Thank you for all the work on your end though...I appreciate it.

 

[Crystalyzer]

That's awesome! Glad to help!