I am working on a spreadsheet where I have 5 important columns. Column B is the "Completed" category. Columns C, D, E & F will all contain values (mainly "Y" or "N") when their associated tasks are completed.
What I would like is a formula for B that will search the C, D, E & F columns in that same row for any values, and if all 4 cells on that row have any non-blank value, change the text in that row of column B to "Y" - if all other cells of that row are blank, the text in column B for that row should instead display "N".
Thank you very much for your time and interest! I've been trying to piece this one together on my own, but am having no luck, and need to get back to doing the maintenance this spreadsheet is being used to track - what I'm really looking to do here is eliminate superfluous mouse-clicks, since I have enough of those to do as it is.
Cheers,
C.J.
Related:
Change text in cell if other cells have any values
I got some help from a co-worker that got me going in the right direction, but the big final trick was realizing the order of execution was important, because with the {"Y","N"} section in front, it would mark column B with a "Y" if any of the four cells had a "Y" or a "N" in them.
By reversing the order so that the ="" section went first, it prioritizes those such that if ANY of the four cells are blank (regardless of the others), it defaults to "N" in column B, and only at such time that all four conditions are complete in the last section (all four cells having a "Y" or "N" value) will it change B to "Y".
Ambucias
Posts47356Registration dateMonday February 1, 2010StatusModeratorLast seenFebruary 15, 202311,169
>
C.J. Leffel
Jul 14, 2016 at 06:10 PM
Hello
From this forum to google you and you co-worker will help thousands. Very generous on your part. I would send you a cheque but I am as poor as a church mouse,
C.J. Leffel
>
Ambucias
Posts47356Registration dateMonday February 1, 2010StatusModeratorLast seenFebruary 15, 2023 Jul 15, 2016 at 01:13 PM
Truly, my pleasure to help. I googled quite a lot for a solution, but nothing ever quite matched what I was looking to do... thankfully we have a few Excel wizards where I work and I was able to get a few minutes of time from one of them - I can but only hope this helps someone else :)
Jul 14, 2016 at 05:46 PM
Cheers!
Jul 14, 2016 at 05:58 PM
I got some help from a co-worker that got me going in the right direction, but the big final trick was realizing the order of execution was important, because with the {"Y","N"} section in front, it would mark column B with a "Y" if any of the four cells had a "Y" or a "N" in them.
By reversing the order so that the ="" section went first, it prioritizes those such that if ANY of the four cells are blank (regardless of the others), it defaults to "N" in column B, and only at such time that all four conditions are complete in the last section (all four cells having a "Y" or "N" value) will it change B to "Y".
Jul 14, 2016 at 06:10 PM
From this forum to google you and you co-worker will help thousands. Very generous on your part. I would send you a cheque but I am as poor as a church mouse,
Jul 15, 2016 at 01:13 PM