2
  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

player0
  • 124,011
  • 12
  • 67
  • 124
KB John
  • 89
  • 5

1 Answers1

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")))}

enter image description here

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")))}

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Hi player0, thanks so much for your answer. The original file doesn't end up in column Z. It increments as the day progresses so it never ends. Would it be possible to get the last column and last row instead for data flexibility? I updated the schedules table as the users aren't in the same table all the time (sometimes they're on a separate table). Also, if I change B3 to User Schedules v2 or 3, nothing happens... – KB John Aug 18 '20 at 08:09
  • Thanks again. Is it possible to get the last column instead of 1000 only? The schedules sheet may have countless of columns as the day the progresses and when will it end is out of my control. Also, when I change B3 in Main sheet to **User Schedules v2** it says all users have **"NONE YET"** the range of the row isn't fixed by the way...So user 1 could be in another row number. Basically the whole sheet is the table where the formula should look for **"user 1"** and get the corresponding schedule... – KB John Aug 18 '20 at 17:06
  • 1
    1000 stands for 1000 rows and an unlimited number of columns - as you requested – player0 Aug 18 '20 at 18:06
  • 1
    v2 and v3 wont work because dates there are not on row 2 as in the first sheet – player0 Aug 18 '20 at 18:09
  • Hi Player, how about when the schedule is blank, can it say "Schedule unavailable"? – KB John Aug 27 '20 at 14:45
  • Hi @player0 I hope you can also help me here: https://stackoverflow.com/questions/63672481/get-count-array-formula-from-another-sheet/63676973#63676973 – KB John Sep 01 '20 at 08:09