Array formula to show two alerts?

[Margate]

Hello everyone,

I have an array formula (below) that will alert me if anything on my upcoming schedule list is due. The notification stays active until the date is changed back to one that is in the future (column N)

This works fine until there are two alerts due at the same time.

Because my formula is only displaying in one cell it only shows one alert. How can I display a second alert (if due at the same time) in a different cell. So if one alert is already showing but a second one is also due it needs to be displayed somewhere or I will not be notified of the second alert.

Here is the array formula that checks my list:

Please Login or Register  to view this content.

Below is an example image of my list:

Screen Shot 2016-11-22 at 21.26.42.jpg

Thank you for any help,

Margate

[JeteMc]

You might try this modification to your array entered formula which assumes that the second cell in which an alert will be shown is the cell below the first one:

Please Login or Register  to view this content.

After activating in the first cell, drag the formula down into the second cell.
In the event this doesn't work I'd suggest attaching a sample of your spreadsheet by clicking on GO ADVANCED and then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[Margate]

Hi JeteMc,

Thank you for your help, I tried the updated array that you suggested but it dit not work in this sheet so I have uploaded an attachment.

On the home page you will see on the top there are three red cells with the middle one showing alert 17. In the alert table below there are currently two
alerts due (Alert 12 & Alert 17). The problem is only one of them is displaying and I need to be alerted that both are due. I have tried a couple of ways
but my Excel knowledge is just not good enough for something this advanced.

Basically when there is an active alert in the middle cell I need to display the second alert in one of the other red cells along the top. There may even be
a better way of doing this other than array formulas but I am not sure.

Regards,

Margate

[Margate]

I have just had a thought about this that could provide a solution. It appears that the array is starting at the bottom the table, hence showing alert 17 before alert 12.

Would it be possible to have another array formula that starts searching from the top of the table instead of the bottom?

[JeteMc]

Since Home!J1 referenced O7 on the Formulas sheet and since that cell had the formula from post #1, I modified the formula in O7 as per the one in post #2 and drag copied it down to O9. I then changed the formulas across the top of the Home sheet to reference Formulas!O7, O8 and O9 respectively. I hope that I am understanding what you want correctly.
Note: as I was saving the file I got some compatibility warnings. Support office.com only shows the SMALL function as working with version 2007 and upwards. Unfortunately I don't have a copy of version 2003 with which to test the formula.
Let us know if you have any questions.

[Margate]

JeteMc, Thank you so much. It worked perfectly.

I will look into learning about the SMALL function as I think it is something I could benefit from understanding.

Regards,

Margate

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.