Max and VLookUp together?

[RogerPenna]

I have a Risk Analysis spreadsheet.

User registers a Risk, the number of the analysis, analysis date, risk probability, risk impact, days for next analysis. For the sake of clarity, lets not even consider the risk probability and risk impact.

So you have like
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]RISK NAME[/TD]
[TD]# ANALYSIS[/TD]
[TD]DAYS NEXT ANALYSIS[/TD]
[TD]DATE NEXT ANALYSIS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/18[/TD]
[TD]Low Budget[/TD]
[TD]1[/TD]
[TD]60[/TD]
[TD]1/3/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/18[/TD]
[TD]High Taxes[/TD]
[TD]1[/TD]
[TD]60[/TD]
[TD]1/3/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/18[/TD]
[TD]Project Delays[/TD]
[TD]1[/TD]
[TD]90[/TD]
[TD]1/4/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3/18[/TD]
[TD]Low Budget[/TD]
[TD]2[/TD]
[TD]60[/TD]
[TD]1/6/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3/18[/TD]
[TD]High Taxes[/TD]
[TD]2[/TD]
[TD]60[/TD]
[TD]1/6/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]current date: 11/07/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Ok, notice I registered 3 risks and made an analysis number 1 of them. Two of the risks I decided to make a new analysis in 60 days. [DATE NEXT ANALYSIS] is the sum of [DATE]+[DAYS NEXT ANALYSIS]

So, on march, I re-analysied two of the risks. The 3rd risk, I should analyse only on April.

There are some formatting rules in place which make the DATE NEXT ANALYSIS get red, as the date approaches. HOWEVER, if a new analysis of that risk was made, I need to change the conditional formatting (to blue for example), so users seeing a big list of risks won´t get confused on which risks were already re-analysed and which ones are really pending a new analysis.



OK, enough explanations about the table. Formulas. I decided to have a hidden column that will tell the conditional formatting of [DATE NEXT ANALYSIS] if it should be blue or not.

My problem is with the formula for that hidden column.


It should check [RISK NAME], where any row with a Risk name Equal to the current row, but with a higher #Analysis , will mark as TRUE. Meaning that a new analysis of that risk was done and therefore the Data Warning should be blue, or set to zero, whatever.


Right now, all DATE NEXT ANALYSIS are RED, because current date is july and all rows have past dates.

However, I need the first two rows to be BLUE (because those two risks already had a 2nd analysis, so only NEXT DATE for analysis number 2 should be red.
Third row however must still be red, as the third risk did not get a 2nd analysis yet, so the DATE NEXT ANALYSIS of the first analysis is the one that is delayed.

 

[RogerPenna]

Re: Max and VLookUp together? Or is there another way?

No need, "Thanks" is enough.

You are welcome.

M.

Valeu pela ajuda tchê!

Thanks for the help pal!

Also, special thanks to VDS1, as I used his array solution.


(ps, just now I noticed we have a Neymar emoticon )

 

[Marcelo Branco]

Re: Max and VLookUp together? Or is there another way?

(ps, just now I noticed we have a Neymar emoticon )

Sorry, i don't like.
During the World Cup i was seeing such things about Neymar as an attempt to undermine and destabilize him. Now, as something cruel and disgusting.

M.