I am currently trying to take an existing spreadsheet and replicate this as a SharePoint list.
The spreadsheet contains the following columns:
Received - This will be a number column with 0 decimal places values entered manually
Total In Queue - Same as above
Oldest Date - Date Column values manually added
Worked - This will be a calculated column
Report Category - Choice (Let's call them 1,2 and 3)
There are 3 categories for each item 1 for each mailbox being reported on. There will be one item added per day for each category.
Worked is what is causing difficulty. The Calculation will need to reference information that doesn't yet exist. The formula in excel is
In Terms of columns this would translate to
=SUM([Received],[Total in Queue]-[Next days Total in Queue])
Now as the required information for the calculation won't exist until the next day SharePoint will not allow for this formula to be used. I need to be able to replicate this and everything I have tried has either updated today's item for all 3 categories skewing the data or hasn't had any effect. Can anyone suggest an approach I could Take?