Is it possible to ask Excel to insert a word

[samaco]

I have the below table.
I need excel to type word OPEN when I insert the new date and higligh the the row from the date until the country with light red color.

11/01/2024 Jordan Motor Submitted Motor 1 OPEN Jordan
21/01/2024 Nigerian Motor Submitted Cycle 1 OPEN Nigeria
21/01/2024

I appreciate your help.

 

[SanjayGMusafir]

There are two parts to your query -
Check this and revert -

I need excel to type word OPEN when I insert the new date

for above - If you change your table to a structured table using 'Insert Tables' then you can put a default formula in columnE as below (assuming A1 is header row

=Ifs(A2<>"","Open",True,"")

and higligh the the row from the date until the country with light red color.

Select columns A:E and use formula below to conditional format

=$A1<>""

 

[samaco]

Thanks SanjayGMusafir
First forumla added the OPEN as requested. I forgot to mention that I need it to add SUMITTED as well please.

I amend the formula to =IFS(A5<>"","OPEN",TRUE,"SUBMITTED",TRUE,""") but it returns error. Please explain the forumla so I can understand how it works in case I need to update it in the future.

The second formula works as fine as well, but I need the row from A to F to be in red color please.
I forgot to mention that once the development team deploy the provided file I need to change the value from SUBMITTED to DEPLOYED. And once I amend it to DEPLOYED I need the color to be changed to Green if possible.

I am attaching a screenshot because I can not upload the excel file.

 

[SanjayGMusafir]

=IFS(A5<>"","OPEN",TRUE,"SUBMITTED",TRUE,""")

The format is incorrect. You are using IFS formula

It should be - Check this and revert -

=IFS(E2<>"OPEN","DEPLOYED",A2<>"","SUBMITTED,TRUE,"")

 

[SanjayGMusafir]

The second formula works as fine as well, but I need the row from A to F to be in red color please

Edit the conditional formatting for range A:F keep formula same

=$A1<>""

 

[SanjayGMusafir]

And once I amend it to DEPLOYED I need the color to be changed to Green if possible.

for above use Conditional formatting formula as below -

=$C2="DEPLOYED"

 

[samaco]

=IFS(E2<>"OPEN","DEPLOYED",A2<>"","SUBMITTED,TRUE,"")

If I click Yes the formula will be changed to the below

Please check the above.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I need Excel to insert a word
and here Excel auto add a word after typing the date
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[SanjayGMusafir]

Please note what @Fluff has just mentioned in #8. Thanks

About your query, there is small mistake I did while compiling formula. Check this and revert -

=IFS(E2<>"OPEN","DEPLOYED",A2<>"","SUBMITTED",TRUE,"")