Array Formula and Text

[Steeviee]

Hello all,

I have the following array formula:

={MAX(IF(PO!$AJ$13:$AJ$10000=$T410,IF(PO!$BO$13:$BO$10000=$U$3,PO!$BQ$13:$BQ$10000)))}

which works great.

What I'd like to do is check if any of the entries in sheet PO, column BQ (that match the two criteria) are in text format, and if so do some work on that.

Basically, if a due date is this year, the week number (e.g. we are now in week 48) is used and so I can work out the latest (or MAX) number using the above. However, if the due date is next year, it is entered as text such as 02/12 for week 2. Such as:

=IF(AND(ISTEXT(BQ13),RIGHT(BQ13,2)=12[COLOR=blue]),...MAX of these fields...,MAX of the numbers[/COLOR])

but as part of the array, similar to above.

I'm not even sure if this is possible, but I'm sure you guys will let me know either way - I look forward to your help. Much appreciated in advance.

 

[Andrew Poulsom]

Is it?

=RIGHT(MAX(IF(PO!$AJ$13:$AJ$10000=$T410,IF(PO!$BO$13:$BO$10000=$U$3,IF(ISTEXT(PO!$BQ$13:$BQ$10000),(RIGHT(PO!$BQ$13:$BQ$10000,2)&LEFT(PO!$BQ$13:$BQ$10000,2))+0,("11"&PO!$BQ$13:$BQ$10000)+0)))),2)

 

[Steeviee]

So fast! thank you Andrew - I'm going to try this right now.

You're a star, as always.

 

[Steeviee]

Unfortunately, I get zero for everything! thanks for the attempt.

 

[Steeviee]

Taking my cue from your suggestion, I have added a column to the sheet PO, creating a new serial number.

=IF(ISTEXT(BQ13),VALUE(RIGHT(BQ13,2)&LEFT(BQ13,2)),VALUE(RIGHT(YEAR(NOW()),2)&TEXT(BQ13,"00")))

I can then find the MAX of that and convert back into the correct format. Not nearly as elegant...