How to Disable / Enable specific columns based on the input from a cell

  • Hi All,


    Attached is the excel sheet which is desiged by me to log my team members daily activity and also use the same for Shift Handover. Colums A to M is used for Daily Tracking.


    I am trying to acheive something which I am not familiar of. Trust me I am not good at coding.


    Query: I want the columns O to AE be in Disabled (Uneditable) and color coded in "Grey" so that it is understood that it is not editable.


    For Ex: In A10 I have an entry and select N10 - Handover Required as "YES" only then the columns O10 to AE10 should be editable. And other columns should be in locked mode for editing.


    In the same way if any of the colums under N is selected with Yes, only the same Row should be enabled for editing. Also, I want to restrict the date format to DD-Mmm-YY whereever applicable.

    Please help me friends :)

  • Re: How to Disable / Enable specific columns based on the input from a cell


    Hi Smallman,


    Thanks for the reply, but unfortunately I am not able to attach the file as it throws out an error - 500 [IOErrorEvent type= "ioError" bubbles=false
    cencelable=false eventPhase=2 text= "Error #2038"]


    Really not sure what is this error all about :(


    Also, would you please let me know the formula on how to consolidate the number of hours logged into the sheet. As I am unable to attach, mentioned below is what I am trying for:


    I am trying to Sum up the number of hours logged by Praveen Kagitala.


    [TABLE="width: 337"]

    [tr]


    [td]

    Date

    [/td]


    [td]

    Logged By

    [/td]


    [td]

    Hours

    [/td]


    [td]

    Minutes

    [/td]


    [/tr]


    [tr]


    [td]

    01-Jan-14

    [/td]


    [td]

    Praveen Kagitala

    [/td]


    [td]

    1

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    02-Jan-14

    [/td]


    [td]

    A

    [/td]


    [td]

    0

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [td]

    03-Jan-14

    [/td]


    [td]

    Praveen Kagitala

    [/td]


    [td]

    2

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [td]

    04-Jan-14

    [/td]


    [td]

    Praveen Kagitala

    [/td]


    [td]

    6

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    05-Jan-14

    [/td]


    [td]

    Praveen Kagitala

    [/td]


    [td]

    5

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    06-Jan-14

    [/td]


    [td]

    Praveen Kagitala

    [/td]


    [td]

    1

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    07-Jan-14

    [/td]


    [td]

    Praveen Kagitala

    [/td]


    [td]

    8

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    08-Jan-14

    [/td]


    [td]

    Praveen Kagitala

    [/td]


    [td]

    3

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    09-Jan-14

    [/td]


    [td]

    Praveen Kagitala

    [/td]


    [td]

    7

    [/td]


    [td]

    25

    [/td]


    [/tr]


    [tr]


    [td]

    10-Jan-14

    [/td]


    [td]

    B

    [/td]


    [td]

    4

    [/td]


    [td]

    35

    [/td]


    [/tr]


    [tr]


    [td]

    11-Jan-14

    [/td]


    [td]

    A

    [/td]


    [td]

    2

    [/td]


    [td]

    15

    [/td]


    [/tr]


    [tr]


    [td]

    12-Jan-14

    [/td]


    [td]

    C

    [/td]


    [td]

    5

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [td]

    12-Jan-14

    [/td]


    [td]

    B

    [/td]


    [td]

    8

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [td]

    12-Jan-14

    [/td]


    [td]

    A

    [/td]


    [td]

    1

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [td]

    12-Jan-14

    [/td]


    [td]

    C

    [/td]


    [td]

    2

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    Hrs worked

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Praveen Kagtiala

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    B

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    C

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: How to Disable / Enable specific columns based on the input from a cell


    With respect to adding the hours you could use Sumproduct


    =SUMPRODUCT(--($B$2:$B$16=$B19)*($C$2:$C$16))+(SUMPRODUCT(--($B$2:$B$16=$B19)*($D$2:$D$16))/60)


    Have a look at the attached workbook


    Try and place a link for a sample workbook - you could use Dropbox or Mediafire - I am not sure about the first post part of your question - with a sample workbook it would be easier to see how your data is set out


    PS: The sum of hours and minutes is in decimal e.g. 28.75 means 28 and 3/4 hours = 28hours 45 mins

  • Re: How to Disable / Enable specific columns based on the input from a cell


    Hi Smuzoen,


    It works perfect :) Awesome dude and thanks for the quick reply. I appreciate you and this forum users who are willing to help and definetly :tabasco:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!