How to populate a specific cell based on text in another cell

Question

Hi,

    I am trying to populate a calendar with specific text based on the text in another cell

For example, if G43 says May, i want to auto populate M43 with a specific word (in this example it is REMOTE)

Another example is if G48 says February then i want to populate J48 

Essentially, i am trying to create a visual Calendar / Planner

Answer 1

Thanks!

Change the formula in G5 on the Project Planner sheet to

='Full Details'!J2&""

Then fill sown to G55. This will remove those strange Jan-00 values.

Enter the following formula in I5:

=IF(LEFT($G5,3)=I$4,"REMOTE","")

Copy I5.

Select I5:T55.

Click the lower half of the Paste button and select Formulas.

See the modified version at https://www.dropbox.com/s/247adxepcefzrgi/JASON%20AUDIT%20PLANNER%20GRAPH%202021%20for%20help.xlsx?dl=1

Answer 2

What does row 4 contain? Text values such as Jan, Feb, etc., or dates formatted as mmm?

What does column G contain? Text values such as October, Jan-00 etc., or dates with various formats?

Answer 3

Hi, thanks for your reply.

Currently, Row 4 is just simply text values and the month typed in the cell

Column G is currently formatted as mmm-yy (see below).

I should add the text in Column G is automatically populated from cells in another worksheet, i.e. Csee below. I hope this helps and appreciate your kind support

Answer 4

Hi Jay

Please, try the following

1- Make sure the values you enter in column G (Month) are real Date values i.e 18/05/2021 formatted as "mmmm" if you only want to display eg.  May

2-Enter in cell I43 or probably I5 the formula

=IF($I43="","",IF(MATCH(MONTH($I43),$I$3:$T$3,0)=I$3,"REMOTE",""))

and copy/drag across the table

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 5

According to the newly given details

If values in column G are the month names as text values, "January", "May", etc...

Try the formula from row I4

=IF($I4="","",IF(MATCH(MONTH(DATEVALUE("01/"&G4&2021)),$I$3:$T$3,0)=I$3,"REMOTE",""))