Getting SPILL reference when using IF to return from a range

*Updated to meet guidelines *

I am creating a project plan and want the phase status (highlighted in yellow on attached) to populate based on the worst case status from that phases’ tasks.

For example, F5 is the phase status and where the formula is, F6 to F12 are the status for each task, there are 6 different statuses available (from a drop down on my main document).

I thought I’d cracked it with the following but I just get a SPILL reference:

=IFERROR(IF(F6:F13="In progress and off track","Off track",IF(F6:F13="In progress and at risk","At risk",IF(F6:F13="In progress and on track","On track",IF(F6:F13="Future date","Future",IF(F6:F13="Future date and at risk","At risk"))))),"Completed")

... any ideas would be massively appreciated!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional.)



Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Please add some expected results ( manually). it is not clear whet you are trying to do
(IF does not work as you described)
What is the " worst case scenario"?

Rather than trying to say:

Formula:

Please Login or Register  to view this content.

say

Formula:

Please Login or Register  to view this content.

If any of the cells in F6:F13 contain the text, the COUNTIF will return TRUE

Hi - thanks so much for coming back, I am not 100% clear on your suggestion (apologies!).

I have tried the following and am obviously not inputting it correctly (try as I might!!):

=iferror(IF(COUNTIF(f6:F13,"In progress and off track","Off Track",""),countif(F6:F13,"In progress and on track","On Track",""),"Completed"))

I have also tried:

=IF(COUNTIF(F6:F13,"In progress and off track"),"Off track","") - this does work, but I can't figure out how to add all the additional items (such as show at risk, on track or completed).

Here you go ...

Formula:

Please Login or Register  to view this content.

Note: the formula may not be in the right priority order but it is based on the fomula in Post #1

You may have to resequence to get the worst case scenario first, second worst case second, and so on.

Guessing that's not quite the same as Post #8

You're welcome. Thanks for the rep.

Also note: in this instance, you don't need IFERROR

Formula:

Please Login or Register  to view this content.

And,lastly, forgot to thank you for changing the thread title. So, thank you . Makes everyone happy, especially the Moderators.

To be fair, I think your request was reasonably clear. However, the forum is intended to be a resource for all and, as such, there are rules that govern proper (searchable) thread titles, use of Code Tags, and so on.

You fixed the thread title very quickly which is commendable ... and allows us to move on with working on a solution.

And you will almost certainly get a quicker answer if you post a sample file, ideally a cut down version of the real thing with sensitive data removed or replaced ... and showing expected results (mocked up).