Good morning all
Apologies this may sound an easy question, but I am going round in circles due to "The expression cannot be used in a calculated cell"
I have a calculated cell that works as follows, apologies column names arent my choice as it needs to be built so the data output table can be linked to other sources that are already built
I can do this in the query, but it would mean re running all the data each week which could be done but as team movements take please it wouldnt align people properly hence why I would like to do this in the main table once the records are appended each week to give a live update
This works but with a static week number. Is there anyway to change this so it can say look at say week minus 1 or another week number.
I have put a couple of examples above but am going round in circles currently.
Thanks in advance
Gavin
Apologies this may sound an easy question, but I am going round in circles due to "The expression cannot be used in a calculated cell"
I have a calculated cell that works as follows, apologies column names arent my choice as it needs to be built so the data output table can be linked to other sources that are already built
I can do this in the query, but it would mean re running all the data each week which could be done but as team movements take please it wouldnt align people properly hence why I would like to do this in the main table once the records are appended each week to give a live update
Code:
works - IIf([WeekNo]=47,5,0)
tried - IIf([WeekNo]=DateAdd("w",-5,Now()),5,0)
tried - IIf([Week]=DatePart("ww",now()),5,0)
This works but with a static week number. Is there anyway to change this so it can say look at say week minus 1 or another week number.
I have put a couple of examples above but am going round in circles currently.
Thanks in advance
Gavin