Michael D. Setzer II wrote: > Was interested if it could be modified for multi-column searches. > Did a same test using H2:I5 and used this formula. > > = SMALL((IF(ISNUMBER($H$2:$I$5), "ALL > GOOD",COLUMN($H$2:$I$5)*1000+ROW($H$2:$I$5))), 1) > > That returns the number 8004 and 2nd one returns 9005. > Then used this formula to convert to a cell address. > =CHAR(INT(L2/1000)+64)&MOD(L2,1000) > > That only works for first 26 columns, and upto 999 rows, but could > be modified.
Hi Michael, I've got another way of finding the address of the first non-numeric value in a multi-column search. = ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))), MIN(IF(ISNUMBER(OFFSET($G$4:$H$7, MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), "All good", COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1))))) Please note that it should be entered as an array formula by pressing CTRL+SHIFT+ENTER instead of just plain ENTER (curly braces will appear around the formula if it is done correctly). The advantage of this formula is that it should work for any contiguous range, not limited to 999 rows or 26 columns. There are also disadvantages to this formula. The first obvious disadvantage is debugging/understanding the formula (if anyone wants an explanation of how it works, feel free to ask). The second disadvantage is that I can't think of a logical way to extend it to find the 2nd or 3rd non-numeric value. Your (Michael's) formula can easily be modified to search for the 2nd, 3rd, 4th etc. non-numeric cell. The formula also references the range in question quite a few times, which makes it a pain to change the reference to another range (in this case I would suggest a "find & replace" for $G$4:$H$7) The above formula returns "Err:502" if all the values are non-numeric. To get a more user friendly result, use the following formula: = IF(MIN(ISNUMBER(G4:H7)), "All values are numeric", ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))), MIN(IF(ISNUMBER(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), "All good", COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)))))) Once again, remember to enter it as an array formula (by pressing CTRL+SHIFT+ENTER instead of just ENTER). Regards Stephan -- Unsubscribe instructions: E-mail to [email protected] In case of problems unsubscribing, write to [email protected] Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
