Excel Help

Discussion in 'All other topics' started by emdadhul, Aug 4, 2010.

  1. emdadhul

    emdadhul Member

    Joined:
    Oct 20, 2007
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    16
    Hi I need help with Excel.

    Basically what I want to do is use an IF statement or otherwise to set up excel to allow only one value to be inserted into one of two collums not both. If values are typed into both fields excel shouldn't accept it and give an error message.

    Please can someone help me with the Excel Jargon

    Many Thanks
     
  2. Ripper

    Ripper Active member

    Joined:
    Feb 20, 2006
    Messages:
    4,697
    Likes Received:
    13
    Trophy Points:
    68
    You need to use validation rules for that, based on an IF statement.

    In both of your fields, set a validation rule as follows:

    [​IMG]

    That is the validation rule that would go in cell A2, and vice versa for cell B2. You can set a custom error message in the other tabs.

    IF(B2="*",0,0)

    IF( - function
    B2="*" - B2 equals anything
    ,0,0) - display nothing (you can change these values to display things within the cell. the first value is what would be displayed if B2 was empty, and the second value is what would be displayed if B2 contained a value)

    Hope that helps.

    Edit: Based on the above, it would return this..

    [​IMG]
     
    Last edited: Aug 4, 2010
  3. emdadhul

    emdadhul Member

    Joined:
    Oct 20, 2007
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    16
    Would I be able to copy the Formula into both cells.
     
  4. emdadhul

    emdadhul Member

    Joined:
    Oct 20, 2007
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    16
    Would I be able to ask you for a favour, can you kindly make me a screen capture video of the above
     
  5. Ripper

    Ripper Active member

    Joined:
    Feb 20, 2006
    Messages:
    4,697
    Likes Received:
    13
    Trophy Points:
    68
    I don't have any video capture software installed anymore, but if you want I'll make you a spreadsheet with the validation rules set and you can have a look for yourself.
     
  6. emdadhul

    emdadhul Member

    Joined:
    Oct 20, 2007
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    16
    Thanks :)

    Very Helpful
     
  7. Ripper

    Ripper Active member

    Joined:
    Feb 20, 2006
    Messages:
    4,697
    Likes Received:
    13
    Trophy Points:
    68
    What I had in mind didn't work in practise, I'll mess around with it some more (I'm not an Excel expert by any means, far from it). You may have to use Visual Basic to achieve your result, in which case I wouldn't be able to help you - Googling it provides quite a few answers. Sorry :-(
     
  8. emdadhul

    emdadhul Member

    Joined:
    Oct 20, 2007
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    16
    I've never heard of Visual Basic lol......Thanks 4 trying please do let me know if you have any joy with EXCEL.

    Many Thanks
     
  9. Ripper

    Ripper Active member

    Joined:
    Feb 20, 2006
    Messages:
    4,697
    Likes Received:
    13
    Trophy Points:
    68
    Visual Basic is a type of code that you can use within Excel to perform more advanced functions. You'd be better off posting on a specialised help forum I think, perhaps this one:

    http://www.excelforum.com/

    Unless someone who is good with VB happens upon this thread!
     
  10. emdadhul

    emdadhul Member

    Joined:
    Oct 20, 2007
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    16
    Thanks will try!!!!
     

Share This Page