Results 1 to 6 of 6

Thread: Trick a cell??

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I want to be able to put a formula in a cell along the lines of:

    =IF(A1="XYZ","N/A","") - so that N/A shows up when user input is not required.

    But, I need the user input to be as text, where user input is required.

    Are there any "tricks" to achieve this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    A cell can't be used for both a formula AND user input. It's either one or the other.

    You could use the Worksheet_Change event in the worksheet module to fill the cell with N/A if "XYZ" is entered in A1. This will only work if the value is entered by the user. If "XYZ" is the result of a formula, you'd need to use the Worksheet_Calculate event.
    Disadvantage of using these events is that the code disables undo.

  3. #3
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts
    [quote name='HansV' post='775639' date='17-May-2009 16:14']A cell can't be used for both a formula AND user input. It's either one or the other.

    You could use the Worksheet_Change event in the worksheet module to fill the cell with N/A if "XYZ" is entered in A1. This will only work if the value is entered by the user. If "XYZ" is the result of a formula, you'd need to use the Worksheet_Calculate event.
    Disadvantage of using these events is that the code disables undo.[/quote]

    How about Data Validation?
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='t8ntlikly' post='775680' date='18-May-2009 14:19']How about Data Validation?[/quote]
    I don't see how that would help here, but I might be mistaken...

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='775628' date='17-May-2009 18:25']I want to be able to put a formula in a cell along the lines of:

    =IF(A1="XYZ","N/A","") - so that N/A shows up when user input is not required.

    But, I need the user input to be as text, where user input is required.

    Are there any "tricks" to achieve this?[/quote]

    Have you considered using conditional formatting to fill the cell with an obvious colour when an input is required?
    Regards
    Don

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='775739' date='18-May-2009 18:39']Have you considered using conditional formatting to fill the cell with an obvious colour when an input is required?[/quote]

    OOh, a bit like my trafic light spreadsheets, green 4go, ref 4stop...... (my colleagues love them) GRRRRRR....

Posting Permissions

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