Return row from other spreadsheet but allow override

[zefrogi]

I have a leaver tracker to record when people are on leave. People put an A when they are on a day off.

I'm making another separate spreadsheet to assign a task but it's important to know when people are going to be off so it doesn't get conflicted. I'd like to be able to bring through the A's that are recorded in the previous spreadsheet but also record when people will cover- C.

I can use this formula to copy the data:

=IF(ISBLANK(XLOOKUP(A7,'[Tracker - Annual Leave - 2023.xlsm]Tracker'!$A$7:$A$29,'[Tracker - Annual Leave - 2023.xlsm]Tracker'!$B$7:$NB$29)),"",XLOOKUP(A7,'[Tracker -  Annual Leave - 2023.xlsm]Tracker'!$A$7:$A$29,'[Tracker - Annual Leave - 2023.xlsm]Tracker'!$B$7:$NB$29))

A7 is the first name. A8 would be the second name. A7:A29 is the rows of name for all staff, this will not be the same on the new spreadsheet.
B7:NB29 is the A data range.

This does copy the A data but I can't override single days due to the formula being 'spilled'. The original column reports obstructing cells.

 

[zefrogi]

Actually just solved it.

=IF(XLOOKUP($A7,'[Tracker - Annual Leave - 2023.xlsm]Tracker'!$A$7:$A$29,'[Tracker - Annual Leave - 2023.xlsm]Tracker'!B$7:B$29)=0,"",XLOOKUP($A7,'[Tracker - Annual Leave - 2023.xlsm]Tracker'!$A$7:$A$29,'[Tracker - Annual Leave - 2023.xlsm]Tracker'!B$7:B$29))

Hope this helps someone.