Max and VLookUp together?

RogerPenna

New Member
Joined
Mar 27, 2017
Messages
20
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
DATERISK NAME# ANALYSISDAYS NEXT ANALYSISDATE NEXT ANALYSIS
1/1/18Low Budget1601/3/18
1/1/18High Taxes1601/3/18
1/1/18Project Delays1901/4/18
1/3/18Low Budget2601/6/18
1/3/18High Taxes2601/6/18
current date: 11/07/18

<tbody>
</tbody>

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.
 
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 :ROFLMAO:) :LOL:
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Re: Max and VLookUp together? Or is there another way?

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

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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top