Add difference rows for data in sets
An as yet unanswered question How to compare multiple HLOOKUP cases from @L.Dutch - Reinstate Monica from a very long time ago on Stack Overflow asked whether it was possible to avoid use of a duplicate sheet in the scenario below:
In my sheet I have a formula using HLOOKUP to calculate a number based on the content of a cell. The content can be choosen among "OK","NOK","-".
Deliverable | CASE |Description | Value
Deliverable1 | OK | ******* | 3
Deliverable1 | NOK | ####### | 6
Deliverable1 | - | &&&&&&& | 10
Deliverable2 | OK | ******* | 4
Deliverable2 | NOK | ####### | 7
Deliverable2 | - | &&&&&&& | 9
I want then to calculate, for a given deliverable, the difference between the applied case and the case when the selection is put to "OK". I.e. if I have a -, I want to get 7.
To achieve this I have created a duplicate sheet where I force the content of the cell to be OK, and then I calculate the difference. The problem with this approach is that when using filtering or sorting the calculation is messed up.
Is this possible without a duplicate sheet?