Sprinter99
New Member
- Joined
- Mar 11, 2021
- Messages
- 5
- Office Version
- 2016
- Platform
- Windows
Hi,
I have a formula that i am struggling with, i have attached a picture of the spreadsheet i am working on the Blue shaded cells in row 4, are the dates when an application will complete testing (1 seperate app per column).
The Blue cells in column "K" is the desired date when a user wants to be migrated, M6:AD25 is a range that will be "blank" or contain the word "Required", each row can contain a number of blanks if the user does not require the applicaiton indicated in the column, or "Required" if they do.
Clearly i cannot migrate a user if any of their required apps have not completed testing, so in column L i would like a simple "Good" if all required apps have a ready date that is earlier than the planned migration date (Conditional formatting is used to show the app as Green if its good, e.g. Cell N7 has an app requirement, and the app ready date is earlier than the user cutover, all is good), if an app will not be ready in time (Cell M7 shows that the user in row 7 requires a migration date that is one day before the app is ready), this turns the cell red.
Now the fun part, how do I create a formula or array formula that searchs all apps for each user (per row), and compares each one with the applicaiton ready data on a per app basis where an app is required, and if "any" return a date that is greater than the migration date, a suitable word "Bad will do" is inserted in the relevent line in column L to show that the user is flagged as having an app problem, if app ready dates are smaller than the migration date then a simple good would be useful in column L.
Hope i have explained it correctly, thanks in advance, its driving me mad.
I have a formula that i am struggling with, i have attached a picture of the spreadsheet i am working on the Blue shaded cells in row 4, are the dates when an application will complete testing (1 seperate app per column).
The Blue cells in column "K" is the desired date when a user wants to be migrated, M6:AD25 is a range that will be "blank" or contain the word "Required", each row can contain a number of blanks if the user does not require the applicaiton indicated in the column, or "Required" if they do.
Clearly i cannot migrate a user if any of their required apps have not completed testing, so in column L i would like a simple "Good" if all required apps have a ready date that is earlier than the planned migration date (Conditional formatting is used to show the app as Green if its good, e.g. Cell N7 has an app requirement, and the app ready date is earlier than the user cutover, all is good), if an app will not be ready in time (Cell M7 shows that the user in row 7 requires a migration date that is one day before the app is ready), this turns the cell red.
Now the fun part, how do I create a formula or array formula that searchs all apps for each user (per row), and compares each one with the applicaiton ready data on a per app basis where an app is required, and if "any" return a date that is greater than the migration date, a suitable word "Bad will do" is inserted in the relevent line in column L to show that the user is flagged as having an app problem, if app ready dates are smaller than the migration date then a simple good would be useful in column L.
Hope i have explained it correctly, thanks in advance, its driving me mad.