Difficult problem: Function that considers the contents of several cells

[stixmike]

I'm not sure if this is possible or not, and I will try to explain it as best I can:

I have a spreadsheet that lists 17 procedures in 17 rows (each procedure name starts on C4). Each procedure has a review date (which is in cells K4-Kx). The first two columns of the spreadsheet (A and B) are meant to list the procedures that are within 60 and 90 days of their review date. That is, the first two columns are "policy name" (A column) and "review date" (B column). The idea is if a policy's review date (which are the cells in column K) is less than 60 days away (the current date is listed in cell A1), it would appear in the first two columns of the spreadsheet. Further, if the date was less than 60 days away the text would appear yellow, and if the date was less than 30 days away, the text would appear red).

The point of all of this is that there will eventually be dozens of procedures, and the spreadsheet needs an easy way to tell the user that one of the procedures is up for review. That's why I wanted only the procedures within 60 and 30 days to appear in the first two columns.

The problem I have is that 1) I don't know if it's possible to have a function consider one cell to infinity, so that when more rows are added you don't have to change the formula, and 2) if it's even possible, if there are multiple procedures up for review, for excel to place the if/then statement result in consecutive rows (ie, A4, A5, A6, etc.).

I know this is probably too complicated, and might not even work. But if anyone knows I would really appreciate it. Thanks.

[tigeravatar]

stixmike,

Welcome to the forum!
Everything you asked for is doable, although it's generally not stuff for beginners.

Attached is an example workbook based on the criteria you described.
In cell A1 is this formula to get today's date:

Please Login or Register  to view this content.

There are two dynamic named ranges for the Procedure Name column (C) and the Review Date column (K).
This is the named range formula for the named range "ProcedureNames":

Please Login or Register  to view this content.

This is the named range formula for the named range "ReviewDates":

Please Login or Register  to view this content.

The formulas in columns A and B are array formulas. Note that array formulas must be confirmed with Ctrl+Shift+Enter and not just Enter. That's how they get the curly braces {} around the formula, don't try to put those in manually.

In cell A4 and copied down to A103:

Please Login or Register  to view this content.

In cell B4 and copied down to B103:

Please Login or Register  to view this content.

You'll notice those formulas use the named ranges we defined earlier. The formula is copied down 100 times (from row 4 to row 103) so it can return up to 100 results where the review date is < 90 days away. If you need/want more results, just copy the formulas down further.


Lastly is the Conditional Formatting.
The conditional formats have been applied to the range A:B (which means they applies to the full columns)
This is the conditional format formula for filling the cells red:

Please Login or Register  to view this content.

This is the conditional format formula for filling the cells yellow:

Please Login or Register  to view this content.

Here are some links to more information about dynamic named ranges, array formulas, and conditional formatting:

Dynamic Named Ranges:
http://support.microsoft.com/kb/830287
http://www.contextures.com/xlNames01.html
http://www.cpearson.com/excel/DefinedNames.aspx

Array Formulas:
http://office.microsoft.com/en-us/ex...001087290.aspx
http://www.cpearson.com/excel/ArrayFormulas.aspx

conditional formatting:
http://office.microsoft.com/en-us/ex...001111661.aspx
http://www.contextures.com/xlCondFormat01.html
http://www.contextures.com/xlCondFormat03.html
http://www.cpearson.com/excel/cformatting.htm

[tigeravatar]

As a side note, if you wanted the items to be sorted by date, just sort columns C:K by Review Date (column K) Oldest to Newest (ascending)

[SteveG]

Hi,

Attached is a workbook with some ideas using a macro and a days until due calculation and conditional formatting.

HTH

Steve


AdvFilter+CondFormatting.xlsm

[stixmike]

Tiger, that was absolutely perfect. I am in your debt. Thank you so much.

[tigeravatar]

You're very welcome

Hmm.. somehow the attachment got removed from my post, and it won't let me re-attach it there. Re-attaching it here for future reference

[stixmike]

Seriously, I can't believe you understood everything I said to a T. The worksheet you prepared looked idential to mine. And the calculations were perfect. I knew it involved an array, I just didn't know how to do it. This was so informative.

[stixmike]

I do have one question. Why did you add the extra parantheses after ReviewDates and Counta?

[tigeravatar]

stixmike, I'm not sure which formula you're referring to. Also, there shouldn't be any extra parentheses, each one is necessary to start/close a function call (like row(), indirect(), counta(), etc)

[stixmike]

I was referring to this code:

Please Login or Register  to view this content.

It's great, I was just trying to understand it. I'm about to go over those links you provided.

[tigeravatar]

Ah, the extra closing parentheses are for closing the prior function calls. Because the functions are nested, it takes several parentheses to close them all:

IF(_,IF(_,ROW(INDIRECT(_&COUNTA(_)))))

After that countA, the parentheses are closing the function calls for CountA, Indirect, Row, and the two If's.

After that, there is a comma, counta and then three more closing parentheses. They are closing function calls from earlier in the formula: Index, Small, and the second CountA.

[stixmike]

Thanks. I do have one more question regarding your formula. If someone who didn't know what they were doing edited this file (which will happen), and was trying to insert a new row so that the procedure name fields would be alphabetical, that would mess up the code. What do you recommend in that situation?

[tigeravatar]

No, it wouldn't mess up the code at all. That exact scenario is why I went the Row(Indirect(_&CountA())) route.

[stixmike]

Is there a way in Excel to organize by date, alpha order, etc... without screwing up the code?

[stixmike]

If I add a new row, though, I have to manually insert the formula into columns A and B and then re-edit the other formulas.

[tigeravatar]

However, if people want things to be sorted, I would recommend they add the new item to the bottom of columns C:K, then sort columns C:K as desired, so that the new item ends up in the correct spot.

[stixmike]

Well, when I click "sort, A-Z", it says, "this requires the cells to be identically sized).

[tigeravatar]

You could also protect the worksheet to prevent users from inserting new rows. Just make sure to unlock the cells in column C:K (select those columns, right-click and select "Format Cells", go to the Protection tab and uncheck "Lock Cells")

[tigeravatar]

Hehe, we keep posting at the same time.

For this:

Well, when I click "sort, A-Z", it says, "this requires the cells to be identically sized).

click on cell C3 (where it contains the header for column C), and then press Shift+Ctrl+Down and then Shift+Ctrl+Right. That should select all the data in columns C:K. Then go to the Data tab and click Sort (the big sort button, not 'A to Z' or 'Z to A'). Make sure the checkbox "My data has headers" is checked. In the Sort By, select the appropriate column header to sort by, and then click OK.

[stixmike]

Well, I need anyone to be able to edit and add new content to this worksheet. Another thing: If I keep the "review date" field blank in rows 8 - 18 the content in columns A and B disappears, but if I add content to an empty row below row 18 and leave the review date field blank, it doesn't make any A and B content disappear.

[stixmike]

Your sorting solution works, so that was a better idea. But I still have the review date problem (column K).

[tigeravatar]

I don't understand this question. Why would you have empty rows 8 - 18? And then start filling in data after a bunch of empty rows?

[tigeravatar]

Is this what you mean? There are some blank cells in the Review Date column. To account for that, I adjusted the ReviewDates named range formula, as well as the array formulas in columns A and B.

The new ReviewDates named range formula:

Please Login or Register  to view this content.

In cell A4 and copied down (this is an array formula):

Please Login or Register  to view this content.

In cell B4 and copied down (this is an array formula):

Please Login or Register  to view this content.

[stixmike]

I'm sorry, I'll clarify.

I didn't realize how easy it was to sort, so forget that part. My issue is that if you leave a cell in row K blank (the review date cell), the formula for columns A and B does not work. Some procedures do not have review dates, which is why some would be left blank.

[stixmike]

It's a weird error. For example, to account for that, I put the same date in both J and K fields on one row. Three procedures showed up in column A. When I deleted one cell in row K where the dates were the same, it for some reason removed an unrelated procedure from column A.

[stixmike]

oops, didn't see your previous post.

[stixmike]

By the way, how do I copy down the code?

[tigeravatar]

It should already be copied down, but to do so manually, you would select cell A4 and copy the cell (ctrl+c or right-click and select Copy, or a different method you prefer), then press Ctrl+Shift+Down and paste (ctrl+v or right-click and select Paste, or a different method you prefer).

[tigeravatar]

Note that the ctrl+shift+down method only works when there is already data/formulas in the below cells. If there are not already data/formulas in the below cells, do this instead:
copy cell A4, then press F5 and type in the cell you'd like to go to (in this case, A103) and press Enter. now press Ctrl+Shift+Up and then paste

[stixmike]

Thanks. I already finished the worksheet, so I just wanted to insert it in mine. Where does the new Review Dates named range formula go?

[tigeravatar]

To create, edit, or delete named ranges, go to the Formulas tab on the ribbon and click Name Manager. Select the name you'd like to edit, and then edit the formula in the Refers to section.

[stixmike]

Okay. Also, when I copy and copy down (the cells already have formulas in them) it says you cannot change part of an array.

[tigeravatar]

Don't try to do more than one column at a time when working with array formulas. Also, you may need to copy cell A4, and then press the down arrow so that cell A5 is selected (so that the copied cell isn't selected) and then press shift+ctrl+down and then paste.

[stixmike]

Ah, you were right. But when I try to copy down and press CTRL SHIFT ENTER, it doesn't add the { to the copied formulas.

[tigeravatar]

you don't need to ctrl+shift+enter when copy/pasting array formulas. Only the cell you're copying needed to have the ctrl+shift+enter. Once the original cell has been array-entered, the copy will pick that up, and the paste will paste them in as already array-entered

[stixmike]

If someone were to type text into a K cell, will that mess up the formula? For example, if they typed NA or As Needed.

[tigeravatar]

stixmike,

Yes, it currently will. Here's updated versions of the formulas to account for that.
Cell A4 and copied down (array formula):

Please Login or Register  to view this content.

Cell B4 and copied down (array formula):

Please Login or Register  to view this content.

[stixmike]

You're a genius. Thank you so much. Everything works perfectly.

[tigeravatar]

You're very welcome

[stixmike]

For my own knowledge, could you explain how this formula works? (What everything means it it)

Please Login or Register  to view this content.

[tigeravatar]

Alright, reading from left to right:
IFERROR(nested formula,"")
Iferror will return the specified value when the nested formula returns an error.


INDEX(ProcedureNames, row position)
Index loads an array into memory. In this case, the array being loaded is the dynamic named range ProcedureNames. the second argument is which row position from that array to return. The row position is obtained using the Small(If...


SMALL(array,COUNTA(A$3:A3))
Small gets the nth smallest number from an array. In this case, the array is being generated by the If statements (will cover those in a min). The n for the nth smallest number is being obtained by the CountA. CountA gets the number of populated cells in a range. In this case, it counts the number of populated cells from A$3 to A3. The first 3 has a dollar symbol, making it an absolute reference. So it counts the number of populated cells from the header row to the row just above the formula. This means that in row 4, the result is 1, so it gets the 1st smallest number. In row 5, the result is 2, so it gets the 2nd smallest number, etc.


IF(ISNUMBER(ReviewDates),IF(ReviewDates-$A$1<90,IF(ReviewDates>=$A$1,get value)))
This is the If statement chain. This is checking the dynamic named range ReviewDates against the criteria we need.
The first criteria is to make sure that not only is the cell not blank, but that it is populated with a number. IsNumber does this because Excel treats dates as numbers, and blank cells as non-numbers (they're treated as a 0 length string).
The second criteria is to make sure the date is less than 90 days away from today's date. Cell A1 contains today's date, and Excel treats each whole day as a whole number. So if the date - today's date is < 90, then the date is less than 90 days away.
The third criteria is to make sure that the date is on or after today. This is included because all dates prior to today's date would also be less than 90 days away. This ensures that only current and future dates are used.
Now that all conditions have been met, we need to get a value, which is the next (and last) part of the formula.


ROW(INDIRECT("1:"&ROWS(ReviewDates)))
Row (not Rows) gets the row number of the range reference. When using multiple rows in the range reference, it will generate an array from x (lowest row) to y (highest row) in a numeric, sequential order.
Indirect interprest a string as a range reference. In this case, the string is "1:"&Rows. This guarantees that the array generated by Row will always start at 1, regardless of if a row is inserted/deleted on the sheet.
Rows returns the total number of rows of a range reference. The range reference in this case is ]ReviewDates. So the array generated will be from 1 to the maximum number of rows in ReviewDates.


So, putting it all together, the If Statements generate an array of True/Falses. Anything that meets all the criteria is True, and the rest is False. This happens for each cell in ReviewDates. Then, the Row(Indirect(_&Rows())) returns the position of the all items that got a True value from the If statements. Next, Small chooses the appropriate position number depending on what cell the formula is in, so the cell in A4 gets the smallest row number, A5 gets the 2nd smallest row number, etc. That row number is fed to the Index function, where it returns the item in the array at the specified row number. This allows it to get all the items that met the criteria without duplicates. When it runs out of items that meet the criteria, Index will return an error. Iferror prevents that, and replaces it with a blank value instead ("").


Whew, that was a lot of typing. I hope that helps clear it up, and I certainly hope it didn't just make things more confusing.