Hello,
sorry for this bad title, but the "challenging thing" is hard to explain in a few words.
Challenging thing:
In the attached file, sheet 1, you will see rows from 1 to infinity.
I am using it to monitor the statuses of documents (each document is on a row).
The statuses always follow a specific route, first stage is RFQ, second is Quote issued - Direct, third is Quote issued - Local, etc...
For each row, I want to be able to only change the status every time each stage is hit and I'd like the date on which the specific status has been changed to be set in the right cell.
Here's an example.
Row 1:
On 13 December: Status is set to RFQ; date in cell C2 should automatically show as 13 december
On 15 December: status is set to Quote issued - Direct; date in Cell D2 should automaticall show as 15 December while the one in C2 should remain the same
And so on and so forth.
I'm struggling with keeping the initial date not to change with a formula.
Maybe a Macro? (although formula is preferable)
Average of 2 columns:
In sheet 2 of the same file, I'd like to monitor what's happening in sheet 1.
You will see in row 1 that I'm trying to calculate the average duration it takes to switch statuses from RFQ to Quote issued - Direct. Basically average of D2-C2, D3-C3, etc e
So, I need a formula which would help calculate that in the simplest way.
Bear in mind that when you fill Sheet 1, if, let's say you are at row 6 and you only got to the RFQ stage of this row, then D6-C6 will be negative which will confuse the result.
The formula should take into consideration the possibility of a negative result in one or many rows which should not be taken into account when calculating the average.
Can you help please?