Formula help SOS

[Wallace123]

Hello Excel community,

I could really use some help with formulas as part of a RAID log I am pulling together for a project (see below). 

I don't know if this is possible but I want the formulas to auto-populate when a new ID (row) is entered on the table. This is because on each new ID e.g. a risk ID I have a 'status' column that either says open or closed depending on the date entered in the Date Closed' column. 

If you look at the risk log tab, where the formulas are already dragged down, the status is 'open' even when there is no ID in the row, this is skewing  my RAID Log Dashboard saying there are 6 open risks when there would only be 1.

It could be that my formulas are wrong but let me know your thoughts!

Thanks :)

Excel spreadsheet 

[#2]

@Wallace123 

Please update  C5(in RAID Log Dashboard tab) formula to include a condition for Risk ID column.

=COUNTIFS('Risks Log'!$M:$M,"open", 'Risks Log'!$A:$A,"<>")

hope it helps!!

[#3]

@Wallace123 

If you tweak your formula in column M on Risk Log Sheet like this...

=IF(A10="","",IF(V10="","Open","Closed"))

You're existing formula on RAID Log Dashboard Sheet in C5 is correct and will return the correct output once you tweak the formula as suggested above.

You should also tweak the formula in L10 on Risk Log Sheet as below...

=IFERROR(VLOOKUP(W10,RiskLook,2,TRUE),"")

This will remove the #N/A error from Column L.

[#4]

Thanks so much for the help!

[#5]

Thanks for the help, really useful !

[#6]

@Wallace123 

You're welcome! Glad you found it helpful.

If any of the replies above resolved the issue, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.