Drop down list change other multiple cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2770530%22%20slang%3D%22en-US%22%3EDrop%20down%20list%20change%20other%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2770530%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20question%20regarding%20if%20it's%20possible%26nbsp%3Bby%20changing%20a%20value%20in%20a%20drop%20down%20list%20-%20can%20it%20affect%20multiple%20other%20cells%20with%20commands%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20want%20everything%20to%20be%20depending%20on%20column%20D%20where%20I%20have%20a%20drop%20down%20list.%20If%20i%20change%20the%20value%20there%20i%20want%20the%20rest%20of%20the%20document%20to%20follow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20intstance%2C%20if%20I%20change%20the%20value%20in%20the%20drop%20down%20list%20from%20'1.%20Requested'%20to%20'2.%20Pre%20order'.%20Firstly%2C%20I%20want%20the%20cells%20in%20column%20I%20and%20J%20to%20change%20and%20be%20given%20todays%20date.%20Secondly%2C%20I%20want%20the%20color%20(in%20other%20drop%20down%20list)%20in%20column%20K%20change%20the%20status%20to%20Green%20(finished)%20and%20in%20column%20N%20to%20change%20to%20yellow%20(ongoing).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESame%20thing%20from%20when%20I%20change%20from%20'2.%20Pre%20Order'%20to%20'3.%20Field%20Work'%20then%20I%20want%20the%20cells%20in%20column%20O%2C%20P%2C%20Q%20to%20be%20given%20todays%20date%20and%20the%20colors%20to%20change%20from%26nbsp%3BGreen%20(finished)%20and%20in%20column%20N%20to%20change%20to%20yellow%20(ongoing)%20in%20column%20R.%20And%20so%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDoes%20this%20question%20make%20sense%3F%20Would%20my%20request%20be%20possible%20in%20any%20way%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20a%20bunch!%3CBR%20%2F%3E%3CBR%20%2F%3EI'll%20attach%20the%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22EliasEkman_1-1632234329730.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311791i87A45056C038D4F1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22EliasEkman_1-1632234329730.png%22%20alt%3D%22EliasEkman_1-1632234329730.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2770530%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2770888%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20down%20list%20change%20other%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2770888%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1162263%22%20target%3D%22_blank%22%3E%40EliasEkman%3C%2FA%3E%26nbsp%3Bwrote%26nbsp%3B%3CSPAN%3EDoes%20this%20question%20make%20sense%3F%20Would%20my%20request%20be%20possible%20in%20any%20way%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20general%2C%20%22Yes%22%20and%20%22Yes.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20do%20you%20realize%3C%2FP%3E%3CUL%3E%3CLI%3EYou%20had%20hidden%20column%20H%2C%20I%2C%20J%20and%20K%20in%20the%20posted%20spreadsheet%3F%3C%2FLI%3E%3CLI%3EColumn%20K%20is%20headed%20%22Project%20Manager%22%20so%20one%20wonders%20what's%20the%20connection%20between%20that%20and%20the%20%22%3CSPAN%3EGreen%20(finished)%22%20that%20you've%20requested%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EColumn%20N%20contains%20(one%20presumes)%20date%20a%20request%20has%20been%20answered%20and%20no%20colors%20at%20all%20currently%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EColumn%20P%20was%20hidden%2C%20doesn't%20contain%20dates%20to%20begin%20with.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3Eetc.%20I'm%20stopping%20listing%20discrepancies%20(and%20therefore%20sources%20of%20confusion)%20between%20the%20sheet%20I'm%20looking%20at%20and%20your%20requests%20concerning%20it.%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%3ESo%20what%20you've%20asked%20about%20is%20definitely%20do-able%2C%20but%20needs%20quite%20a%20bit%20of%20clarification%20before%20we%20try%20to%20accomplish%20it.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EYou%20clearly%20are%20fairly%20accomplished%20with%20Excel's%20features%2C%20given%20your%26nbsp%3Bsophisticated%20use%20of%20drop-down%20list%20and%20conditional%20formatting.%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EIf%20you'd%20like%20to%20just%20proceed%20on%20your%20own%2C%20what%20I%20think%20you%20need%20to%20have%20are%20some%20conditionals%20in%20those%20cells%20where%2C%20for%20example%2C%20you%20want%20the%20date%20to%20appear.%20%3C%2FSPAN%3E%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSPAN%3ESo%20the%20IF%20function%20would%20be%20helpful.%20%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EHOWEVER%2C%20if%20you%20want%20today's%20date%20to%20appear%20and%20then%20stay%20constant%20in%20subsequent%20days%2C%20then%20you'd%20need%20to%20use%20something%20other%20than%20the%20%3DTODAY()%20function%20(since%20that%20would%20just%20change%20every%20day%20to%20whatever%20the%20current%20date%20is).%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESo%20maybe%20another%20thing%20to%20clarify%3A%20what's%20the%20working%20procedure%20that%20will%20be%20used%20to%20make%20those%20entries%3F%20Who's%20doing%20it%3F%20How%20often%3F%20Is%20it%20practical%20to%20just%20manually%20enter%20the%20day's%20date%20in%20some%20other%20background%20cell%20at%20the%20start%20of%20each%20update%2C%20so%20it's%20not%20computed%20but%20only%20needs%20to%20be%20entered%20once%20each%20day....%20etc.%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EA%20full%20workflow%20needs%20to%20be%20articulated%2C%20with%20the%20updating%20of%20the%20spreadsheet%20as%20part%20of%20the%20bigger%20picture.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I have a question regarding if it's possible by changing a value in a drop down list - can it affect multiple other cells with commands?

I want everything to be depending on column D where I have a drop down list. If i change the value there i want the rest of the document to follow.

 

For intstance, if I change the value in the drop down list from '1. Requested' to '2. Pre order'. Firstly, I want the cells in column I and J to change and be given todays date. Secondly, I want the color (in other drop down list) in column K change the status to Green (finished) and in column N to change to yellow (ongoing).

 

Same thing from when I change from '2. Pre Order' to '3. Field Work' then I want the cells in column O, P, Q to be given todays date and the colors to change from Green (finished) and in column N to change to yellow (ongoing) in column R. And so on. 


Does this question make sense? Would my request be possible in any way?

Thanks a bunch!

I'll attach the file. 

 

EliasEkman_1-1632234329730.png

 

 

3 Replies

@EliasEkman wrote Does this question make sense? Would my request be possible in any way?

 

In general, "Yes" and "Yes."

 

But do you realize

  • You had hidden column H, I, J and K in the posted spreadsheet?
  • Column K is headed "Project Manager" so one wonders what's the connection between that and the "Green (finished)" that you've requested
  • Column N contains (one presumes) date a request has been answered and no colors at all currently
  • Column P was hidden, doesn't contain dates to begin with.
  • etc. I'm stopping listing discrepancies (and therefore sources of confusion) between the sheet I'm looking at and your requests concerning it.

So what you've asked about is definitely do-able, but needs quite a bit of clarification before we try to accomplish it.

 

You clearly are fairly accomplished with Excel's features, given your sophisticated use of drop-down list and conditional formatting.  If you'd like to just proceed on your own, what I think you need to have are some conditionals in those cells where, for example, you want the date to appear.

  • So the IF function would be helpful.
  • HOWEVER, if you want today's date to appear and then stay constant in subsequent days, then you'd need to use something other than the =TODAY() function (since that would just change every day to whatever the current date is). 

 

So maybe another thing to clarify: what's the working procedure that will be used to make those entries? Who's doing it? How often? Is it practical to just manually enter the day's date in some other background cell at the start of each update, so it's not computed but only needs to be entered once each day.... etc.  

 

A full workflow needs to be articulated, with the updating of the spreadsheet as part of the bigger picture.

 

Hello @mathetes !

 

Thank you for your reply. 

I'm sure that there were a few surprises that didn't make any sense for you when you started to dissect my document. It was just to give to you as an better understanding of the question rather than for you to find discrepancies in it. However, I'm thankful for your input and will make sure to clean my document further once I solve my initial problem.

This is my first time that I submit a question on this forum and therefore was unsure if people usually do actual modification in the documents rather than to explain for the sender how to do it them selves. 

The documents intent is for me and my co-workers to be able to go in, add a new project and follow its progress. Right now I use it most frequently out of anybody. About a few times a week. Now I have been manually changing the dates as a request is submitted, answered, tender given and so on. It has been very time consuming and I have fail to be consistent in this work. Therefor I wanted to facilitate this by just changing the the drop down list in column D and this would complete the affected cells with date and so on, as I described it in the question. 

Do we come any closer to a salvation? I basically look for a function that says when this cell changes in the drop down list, 'insert date in these cells' and 'change these drop down lists'. 

Thanks again for engaging in this topic to help me.

/Elias

@EliasEkman 

 

Closer perhaps. People here often make changes to spreadsheets that are posted.....and sometimes just point in the right direction.

 

From all that you've said, I'm thinking that you might benefit most from a macro or VBA routine and those are not my expertise at all. And, frankly, before you ask anybody else to help with that--there are people here who could do it--you really need to clean up those various discrepancies first so it's a clear description of what's needed, and a clean spreadsheet to work with.