Populating a cell based on conditions of other cells

[BlissC]

I have a small test spreadsheet similar to one I use at work where I'm testing out some things and trying in a number of columns to populate cells based on conditions of other cells to cut down on the amount of inputting needed. I have the basic formulas figured out, and it's all working correctly, except that for one condition it's not quite giving the result I need.

The spreadsheet holds data on surveys conducted on where repairs are needed to roads and footpaths. As part of the spreadsheet I have two columns - one that shows the date repair work was completed, and another that displays either "completed" or "repair outstanding" based on whether there's a date in the column that shows the date the repair was completed or not, which I've also got colour-coded using conditional formatting. This is the formula I'm using:

=IF(ISNUMBER(O4),"Completed","Repair outstanding")

This works fine, except where a survey's been completed but no repair work is required, where because there's no date in the column with the date completed, though no work is needed, it still shows "repair outstanding". There's a further column for the current status of the repair which shows the status of the repair request based on the severity of the repair needed - whether it's urgent and needs completing immediately so is shown as "pending" or not so severe so has been put on a work programme to be completed and is shown as "programmed". This column also shows "completed" for completed repairs, and "repair not required" for those not requiring repairs.

What I'm trying to achieve is changing the formula to check whether this third column shows "repair not required" and leave the cell blank if either no repair's required or there's no data in that cell. In other parts of the spreadsheet I'm successfully using nested IF statements to check the condition of two cells, but for some reason I can't get the same approach to work in this formula using ISNUMBER.

I have a similar problem with another column where I want the financial year to display (e.g. 2017/18), based on the date of the survey. This too is working fine, except where no date is entered, where it shows "1899/00", where I want the cell to remain blank if no date is entered in the survey date column. The formula I'm using is:

=IF(OR(MONTH(B14)<=3,AND(MONTH(B14)=3,DAY(B14)>=1)),YEAR(B14)-1,YEAR(B14))&"/"&TEXT(IF(OR(MONTH(B14)<=3,AND(MONTH(B14)=3,DAY(B14)<=31)),B14,DATE(YEAR(B14)+1,MONTH(B14),DAY(B14))),"yy")

Again, though I'm successfully using nested IF statements elsewhere in the spreadsheet, in this formula using Excel's date functions, I can't figure out where I need to put an additional IF statement in this formula to leave the cell empty if no survey date is entered.

If anyone can point me in the right direction for either of these formulas I'd be most grateful.

 

[BlissC]

Okay, so I've sort of solved this with a work-around.

I did find a formula that almost worked, but created a circular reference. The problem is that both Columns N and P are populated from the contents of other cells. When I looked at the spreadsheet again I also realised that both columns were effectively duplicating each other - N shows 'completed', 'pending', 'programmed' or 'repair not required' and does show an empty cell if there's no data in the row, while P shows 'completed' or 'repair outstanding'. With column N, if a repair's pending or programmed, it's not completed, so column P is actually redundant, except for the fact that column P determines if 'completed' is shown in column N, as it checks column O for a repair date. (I've probably totally confused anyone who's managed to get this far by now! )

As I really don't need to show both columns, and I've not been able to figure out how to get around the circular referencing, I've simply hidden column P. It's still there for column N's formula to work, but not visible. It's a bit of a cheat workaround, but it works!

With the second problem of "1899/00" showing in the financial year column (e.g. 2017/18) when no survey date is entered, again I've used a workaround to simply hide the "1899/00". As I'm using conditional formatting to shade the different financial years in different colours to make it easier to identify the year a particular repair was reported, I've simply added an additional rule to the conditional formatting to hide the "1899/00". The additional rule simply formats any cell containing the text "1899/00" to have a white background and white text. Again, a bit of a cheat of a workaround, but it does the trick and makes it look a bit tidier.

Neither solution is elegant in any way, but to quickly solve (hide!) the problems without having to take ages to figure out how to get the formulas to work or alter the spreadsheet to solve the circular referencing and duplication, it does the trick.