I cannot for the life of me figure this out...in a nutshell if the column is changed to Non-Business it gets highlighted grey and it's contents need to be moved back to the last business day before it...so if it's a Friday it needs to move back 1 day to Thursday, a Monday would need to go back 3 days to the Friday,
if you look at the highlighted green cell below, i used this formula:
=IF(N$3="Non-Business",OFFSET(M$3,$E18,1),IF(IFERROR(VLOOKUP(CONCATENATE($H18," ",$I18," ",M$4),'Input Sheet'!$B$3:$E$19,4,0),0)=M$4,1,IF(IFERROR(VLOOKUP(CONCATENATE($H18," ",$I18," ",M$5),'Input Sheet'!$B$3:$E$19,4,0),0)=M$5,1,0)))
But i think it's over complicated and I cant replicate it over the the other columns. I wasn't sure if it would be better to use an IF statement and use some vba code to move it via colour instead? The main problem is I have no idea how to do that either. Any help will be really appreciated.