Get value from another google sheet

Question

1. The formula should be placed in C7: ={"SCHEDULE";ARRAYFORMULA()}
2. Need a formula that will search the name of the user from INDIRECT(J6) and find the Time from another sheet (1) depending on the Sched sheet name in cell B3 and (2) based on what day is selected in INDIRECT(J5)
3. If the user has no schedule yet (blank on the other sheet, it should return "NONE YET".
4. Basically, we'll get the user's schedule from the other sheet...

main sheet: https://docs.google.com/spreadsheets/d/1c3HdMq4PA50pYr88JqPoG51jvru8ipp8ebe4z5DczTQ/edit?usp=sharing

other sheet that has the schedules: https://docs.google.com/spreadsheets/d/1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo/edit?usp=sharing

Answer 1

use:

={"SCHEDULE"; ARRAYFORMULA(IF(INDIRECT(J6)="",,IFNA(VLOOKUP(INDIRECT(J6), 
 IMPORTRANGE("1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo", "User Schedules!B4:Z"), MATCH(INDIRECT(J5), 
 IMPORTRANGE("1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo", "User Schedules!2:2"), 0)-1, 0), "NONE YET")))}

---

update:

={"SCHEDULE"; ARRAYFORMULA(IF(INDIRECT(J6)="",,IFNA(VLOOKUP(INDIRECT(J6), 
 IMPORTRANGE("1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo", B3&"!B4:1000"), MATCH(INDIRECT(J5), 
 IMPORTRANGE("1LM7yIJJ_w6mftXJhQgonLy67w6CUooU04WgdwG9k3xo", B3&"!2:2"), 0)-1, 0), "NONE YET")))}