Home

Default Drop-Down Based on Cell Value

%3CLINGO-SUB%20id%3D%22lingo-sub-737242%22%20slang%3D%22en-US%22%3EDefault%20Drop-Down%20Based%20on%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737242%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20evening.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20wondering%20if%20there's%20a%20way%20to%20auto-populate%20an%20item%20in%20a%20drop-down%20menu%20based%20on%20the%20value%20entered%20in%20a%20different%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20trying%20to%20accomplish%3A%20Cell%20E7%20is%20a%20drop-down%20containing%20only%20the%20selection%20%22QA%20Due%22.%20When%20this%20value%20is%20entered%20in%20E7%2C%20I'd%20like%26nbsp%3BM7%20to%20auto-populate%20the%20word%20%22Pending%22%2C%20but%20ALSO%20contain%20a%20drop-down%20box%20which%20can%20be%20changed%20to%20%22Complete%22.%20When%20M7%20is%20changed%20to%20%22Complete%22%2C%20S7%20then%20auto-populates%20the%20word%20%22No%22%2C%20but%20is%20also%20a%20drop-down%20that%20can%20be%20changed%20to%20say%20%22Yes%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20figured%20out%20one%20way%20to%20do%20this%20by%20applying%20Data%20Validation%20List%20to%20cells%20M7%20and%20S7%26nbsp%3Bwith%20their%20available%26nbsp%3Boptions%20(%22Pending%22%26nbsp%3Bor%20%22Complete%22%2C%20and%20%22Yes%22%20or%20%22No%22%2C%20respectively)%2C%20then%20turning%20off%20the%20error%20alert%20and%20applying%20an%20IF%26nbsp%3Bformula.%20For%20example%2C%20to%20M7%20I%20would%20apply%20this%20formula%3A%26nbsp%3B%3CSTRONG%3E%3DIF(E7%3D%22QA%20Due%22%2C%22Pending%22%2C%22%22)%3C%2FSTRONG%3E%26nbsp%3Bafter%20which%20I%20re-apply%20the%20error%20alert.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%2C%20but%20leaves%20the%20formulas%20exposed%2C%20meaning%20if%20the%20user%20accidentally%20clicks%20the%20cell%20and%20hits%20%22delete%22%20the%20formula%20no%20longer%26nbsp%3Bexists%20to%20auto-populate%20the%20response%20in%20M7%20when%20%22QA%20Due%22%20is%20selected%20in%20E7.%20Or%2C%20if%20the%20user%20selects%20a%20drop-down%20item%20from%20M7%20before%20selecting%20the%26nbsp%3Bdrop-down%20item%20in%26nbsp%3BE7%2C%20the%20formula%20no%20longer%20exists.%26nbsp%3BIf%20I%20protect%20the%20cell%20and%20worksheet%2C%20the%20drop-down%20no%20longer%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20for%20this%20to%20work%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20record%2C%20I%20have%20never%20used%20VBA%20or%20macros%20so%20I'm%20hoping%20to%20avoid%20those%20if%20at%20all%20possible.%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%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122117i05B42E5E40C6D668%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22QA%20Tracker%20Snapshot.jpg%22%20title%3D%22QA%20Tracker%20Snapshot.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-737242%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-749186%22%20slang%3D%22en-US%22%3ERe%3A%20Default%20Drop-Down%20Based%20on%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-749186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370816%22%20target%3D%22_blank%22%3E%40MDBeckmanEHP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20looks%20like%20you%20have%20figured%20out%20the%20strengths%20and%20weaknesses%20of%20data%20validation%20dropdowns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20add%20an%20empty%20string%20%22%22%20to%20your%20data%20validation%20source%2C%20you%20won't%20need%20to%20turn%20error%20alert%20off%20when%20you%20apply%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20need%20to%20reapply%20the%20formula%20after%20the%20user%20has%20deleted%20it%20by%20making%20a%20data%20validation%20dropdown%20choice%2C%20then%20VBA%20is%20the%20way%20to%20go.%20I%20suggest%20adding%20a%20button%20to%20the%20worksheet%20to%20call%20the%20re-initialization%20macro.%20VBA%20is%20also%20the%20way%20to%20go%20if%20you%20want%20to%20prevent%20the%20user%20from%20making%20a%20selection%20in%20M7%20or%20S7%20until%20after%20they%20have%20made%20a%20selection%20in%20the%20preceding%20dropdown.%20This%20could%20be%20done%20by%20a%20Worksheet_Change%20event%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20realize%20that%20you%20don't%20know%20how%20to%20write%20the%20required%20VBA%20code%2C%20but%20it%20is%20pretty%20easy.%20If%20you%20post%20your%20workbook%2C%20I'd%20be%20glad%20to%20show%20you%20how%20it's%20done.%3C%2FP%3E%3C%2FLINGO-BODY%3E
MDBeckmanEHP
Frequent Visitor

Good evening.

 

I'm wondering if there's a way to auto-populate an item in a drop-down menu based on the value entered in a different cell.

 

What I'm trying to accomplish: Cell E7 is a drop-down containing only the selection "QA Due". When this value is entered in E7, I'd like M7 to auto-populate the word "Pending", but ALSO contain a drop-down box which can be changed to "Complete". When M7 is changed to "Complete", S7 then auto-populates the word "No", but is also a drop-down that can be changed to say "Yes".

 

I have figured out one way to do this by applying Data Validation List to cells M7 and S7 with their available options ("Pending" or "Complete", and "Yes" or "No", respectively), then turning off the error alert and applying an IF formula. For example, to M7 I would apply this formula: =IF(E7="QA Due","Pending","") after which I re-apply the error alert.

 

This works, but leaves the formulas exposed, meaning if the user accidentally clicks the cell and hits "delete" the formula no longer exists to auto-populate the response in M7 when "QA Due" is selected in E7. Or, if the user selects a drop-down item from M7 before selecting the drop-down item in E7, the formula no longer exists. If I protect the cell and worksheet, the drop-down no longer works.

 

Is there any way for this to work?

 

For the record, I have never used VBA or macros so I'm hoping to avoid those if at all possible.

 

QA Tracker Snapshot.jpg

 

Thanks.

1 Reply

@MDBeckmanEHP 

It looks like you have figured out the strengths and weaknesses of data validation dropdowns.

 

If you add an empty string "" to your data validation source, you won't need to turn error alert off when you apply the formula.

 

If you need to reapply the formula after the user has deleted it by making a data validation dropdown choice, then VBA is the way to go. I suggest adding a button to the worksheet to call the re-initialization macro. VBA is also the way to go if you want to prevent the user from making a selection in M7 or S7 until after they have made a selection in the preceding dropdown. This could be done by a Worksheet_Change event macro.

 

I realize that you don't know how to write the required VBA code, but it is pretty easy. If you post your workbook, I'd be glad to show you how it's done.

Related Conversations