+ Reply to Thread
Results 1 to 11 of 11

Greying out cells after drop-down option in neighbouring cell is chosen

  1. #1
    Registered User
    Join Date
    06-19-2019
    Location
    Midlands, England
    MS-Off Ver
    365
    Posts
    29

    Greying out cells after drop-down option in neighbouring cell is chosen

    Hi everyone, complete newbie here. Hope you can help.

    My knowledge of Excel is OK, but pretty basic. Now my boss has asked me to find the answer to something even he can't figure out (and he's loads better than me at Excel)!

    spreadsheet exercise.jpg

    In the spreadsheet pictured, we have column L, which includes a drop-down menu in each cell.

    The customer selects "Accept", "Avoid", "Modify" or "Share". With the last three options, they then need to fill in the cells in cells M to S.

    But if the answer is "Accept", they don't.

    So my boss has asked me to find a way to "grey out" cells M to S if someone has chosen "Accept" in column L.

    This would make it clear to the customer that they don't need to fill in those cells.

    I wouldn't know where to start but, if someone could give me the magic formula, you'd be making me - and my boss - very happy.

    Thanks in advance,

    Andie

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    Select the data range in columns M:S (for instance M2:S100)
    The on the home tab > Conditional Format > New rule > Use a formula >
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    & select the format

  3. #3
    Registered User
    Join Date
    06-19-2019
    Location
    Midlands, England
    MS-Off Ver
    365
    Posts
    29

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    Hi Fluff, hmmm, that's not worked for some reason.

    I tried this in one row (row 14) and changed the $L2 to $L14 (also tried it with $L2) but nothing's happened.

    What am I doing wrong?

    Attachment 628915

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    The $L2 has to reflect the first row in the Applies to range, so if you selected M14:S14 then $L14 should work.

    Can you supply a sample workbook?

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    06-19-2019
    Location
    Midlands, England
    MS-Off Ver
    365
    Posts
    29

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    Thanks Fluff, I really appreciate your help! Here it is!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-19-2019
    Location
    Midlands, England
    MS-Off Ver
    365
    Posts
    29

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    PS Just leaving the office now. Back on Friday. Thanks in advance! xxx

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    You hadn't selected a format. Have a look here
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-19-2019
    Location
    Midlands, England
    MS-Off Ver
    365
    Posts
    29

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    Hi Fluff, thanks so much for this - that's exactly what we're after!

    Not really sure about "format". Where did you put the command text to tell it to do what it did?

    Andie xxx

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    It's all in conditional formatting, if you select manage rules, you can see it.

  10. #10
    Registered User
    Join Date
    06-19-2019
    Location
    Midlands, England
    MS-Off Ver
    365
    Posts
    29

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    Got it! Thanks again! xxx

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Greying out cells after drop-down option in neighbouring cell is chosen

    You're welcome & thanks fr the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need to assign value to a cell, based on pull down menu option chosen
    By vcastro458 in forum Excel General
    Replies: 14
    Last Post: 06-17-2017, 10:37 PM
  2. [SOLVED] Code to hide cells when option button chosen error
    By lindafinlay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2014, 11:50 PM
  3. [SOLVED] Formulas to update from different worksheets depending on drop down option chosen
    By Blinkers in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2013, 10:16 AM
  4. [SOLVED] Trying to tie a drop down list into creating a value in a neighbouring cell
    By amitzala in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-17-2013, 03:08 PM
  5. Replies: 22
    Last Post: 06-02-2012, 11:45 AM
  6. Greying out and disabling cells based on drop down selection
    By RichieSams2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2011, 07:40 PM
  7. Populate cell with not previously chosen option among 2 possible options...
    By Texas_Dawg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2010, 06:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1