Change drop down based on cell value

[nolandavis513]

Hello,

I have an excel worksheet with a column titled "Status" which contains a dropdown list with options of "In Progress", "On Hold", "Cancelled" and "Complete" (dropdown options located in an third column). I have another column titled "Ship Date" which is empty until the product is shipped.

When someone enters a valid ship date into the "Ship Date" column, I would like the dropdown in "Status" column to automatically change to "Complete" (as the product will also be completed it has a ship date). Is this possible and how would I go about doing this?

[BadlySpelledBuoy]

Possible? Yes.
You would need to use VBA to make the change happen though.

If that's not a problem, attach a sample workbook so we can see the layout and we can show you how.

To attach a sample workbook click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

BSB

[nolandavis513]

See attached workbook...column B is what i would expect it to look like but with automation to "Complete" instead of me selecting it every time.

[FDibbins]

there is nothing magical about a drop down (Data Validation). It is exactly the same as if you had typed the entry in manually, the only differences being that a DD can save you some typing, help avoid typos and help restrict entry to only a specified "list". Other than that, there is basically no difference. You would refer to it in formulas etc the exact same way as if you were referencing a manually entered cell.

So, having said that, you can see that what you want to do, would be the same as trying to have a formula AND data in the same cell.

[BadlySpelledBuoy]

For a VBA solution to this, right click the tab for Sheet1 and select 'View Code'.
Then paste the below code in the window that opens.
Go back to Sheet1 and enter a date in column A.

Please Login or Register  to view this content.

See attached.

Does that help at all?

BSB

[nolandavis513]

Yes. That works great! Thanks!

[BadlySpelledBuoy]

No problem. Happy to help

BSB