Home

Change text in one cell based on which cell in a row is completed.

%3CLINGO-SUB%20id%3D%22lingo-sub-779321%22%20slang%3D%22en-US%22%3EChange%20text%20in%20one%20cell%20based%20on%20which%20cell%20in%20a%20row%20is%20completed.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779321%22%20slang%3D%22en-US%22%3E%3CP%3EOK%2C%20So%20I%20am%20trying%20to%20create%20a%20tracker%20for%20curriculum%20proposals.%26nbsp%3B%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20734px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124934iF6C817C6926DCF8A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22table.png%22%20title%3D%22table.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20want%20the%20%22Current%20Status%22%20cell%20in%20the%20row%20to%20auto-populate%20with%20the%20column%20header%20text%20based%20on%20the%20one%20with%20the%20most%20recent%20date%2C%20and%20I%20also%20want%20the%20first%204%20cells'%20color%20to%20change%20to%20correspond%20with%20the%20status.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20when%20I%20enter%20the%20date%20for%20received%2C%20I%20want%20the%20Current%20Status%20to%20be%20%22received%22%20and%20the%20first%20four%20to%20be%20blue.%26nbsp%3B%20Then%2C%20when%20I%20add%20the%20date%20for%20the%20Agenda%2C%20I%20want%20it%20to%20change%20to%20%22On%20Agenda%2C%22%20and%20the%20color%20to%20be%20yellow%2C%20and%20so%20forth.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20probably%20just%20add%20a%20dropdown%20and%20do%20conditional%20formatting%20based%20on%20text%2C%20but%20I%20would%20like%20it%20to%20auto-populate.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-779321%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779417%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20text%20in%20one%20cell%20based%20on%20which%20cell%20in%20a%20row%20is%20completed.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384401%22%20target%3D%22_blank%22%3E%40juleem%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20this%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20584px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124949iDE859CF464E82D91%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eto%20show%20the%20status%20for%20the%20latest%20date%20you%20may%20use%20in%20D3%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24E%241%3A%24L%241%2CMATCH(LARGE(%24E3%3A%24L3%2C1)%2C%24E3%3A%24L3%2C0))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E(drag%20it%20down).%20To%20pickup%20color%20it%20could%20be%20set%20of%20conditional%20formatting%20rules%20on%20column%20D%20with%20formulas%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DMATCH(%24D3%2C%24E%241%3A%24L%241%2C0)%3D6%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20you%20map%20status%20numbers%20on%20colors.%20Please%20check%20attached.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
juleem
Occasional Visitor

OK, So I am trying to create a tracker for curriculum proposals.  table.png

I want the "Current Status" cell in the row to auto-populate with the column header text based on the one with the most recent date, and I also want the first 4 cells' color to change to correspond with the status.  

 

So, when I enter the date for received, I want the Current Status to be "received" and the first four to be blue.  Then, when I add the date for the Agenda, I want it to change to "On Agenda," and the color to be yellow, and so forth.

 

I could probably just add a dropdown and do conditional formatting based on text, but I would like it to auto-populate.  

1 Reply

@juleem 

With this sample

image.png

to show the status for the latest date you may use in D3

=IFERROR(INDEX($E$1:$L$1,MATCH(LARGE($E3:$L3,1),$E3:$L3,0)),"")

(drag it down). To pickup color it could be set of conditional formatting rules on column D with formulas like

=MATCH($D3,$E$1:$L$1,0)=6

if you map status numbers on colors. Please check attached.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies