Consulting

Results 1 to 20 of 20

Thread: Solved: Data Validation Error

  1. #1

    Solved: Data Validation Error

    Okay I'm really confused by this. I had a spreadsheet set up which would give a dropdown list on one cell based on the values in another. I did this with conditional data validation using this formula:

    =IF(D1="Building",Building,IF(D1="Mechanical",Mechanical,IF(D1="Electrical" ,Electrical, "Please enter Description")))

    Not I wanted to change this because I needed more options in the initial cell so I changed the formula to this:

    =IF(D1="Building",Building,IF(D1="Electrical",Building,IF(D1="Heating and Ventilation",Building,IF(D1="Air Conditioning",Building,IF(D1="Window Blinds",Building,"Please enter Description")))))

    Now when I try and enter this it comes up with an error saying "The List Source must be a delimited list, or a reference to a single row or column".

    What's even more strange is that I had a back up of the spreadsheet before I changed it and I deleted the formula in there and reentered it as exactly the same and got this error.

    Does anyone have any ideas?

  2. #2
    VBAX Tutor CCkfm2000's Avatar
    Joined
    May 2005
    Posts
    209
    Location
    should your code be

    =IF(D1="Building",Building,IF(D1="Electrical","Building",IF(D1="Heating and Ventilation","Building",IF(D1="Air Conditioning","Building",IF(D1="Window Blinds","Building","Please enter Description")))))
    Quote Originally Posted by icthus123
    Okay I'm really confused by this. I had a spreadsheet set up which would give a dropdown list on one cell based on the values in another. I did this with conditional data validation using this formula:

    =IF(D1="Building",Building,IF(D1="Mechanical",Mechanical,IF(D1="Electrical" ,Electrical, "Please enter Description")))

    Not I wanted to change this because I needed more options in the initial cell so I changed the formula to this:

    =IF(D1="Building",Building,IF(D1="Electrical",Building,IF(D1="Heating and Ventilation",Building,IF(D1="Air Conditioning",Building,IF(D1="Window Blinds",Building,"Please enter Description")))))

    Now when I try and enter this it comes up with an error saying "The List Source must be a delimited list, or a reference to a single row or column".

    What's even more strange is that I had a back up of the spreadsheet before I changed it and I deleted the formula in there and reentered it as exactly the same and got this error.

    Does anyone have any ideas?

  3. #3
    sorry, the code I'm trying to put in is

    =IF(D1="Building",Building,IF(D1="Electrical",Electrical,IF(D1="Heating and Ventilation",HandV,IF(D1="Air Conditioning",AirCon,IF(D1="Window Blinds",WindB,"Please enter Description")))))

    Building, Electrical are defined ranges.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Have you chosen a type of List in the DV, not Custom?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Quote Originally Posted by xld
    Have you chosen a type of List in the DV, not Custom?
    I'm not sure what you mean?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Try this formula

    =IF(OR(D1="Building",D1="Electrical",D1="Heating and Ventilation",D1="Air Conditioning",D1="Window Blinds"),Building,"Please enter Description")
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Quote Originally Posted by xld
    Try this formula

    =IF(OR(D1="Building",D1="Electrical",D1="Heating and Ventilation",D1="Air Conditioning",D1="Window Blinds"),Building,"Please enter Description")
    But that wouldn't include my lists HandV, Electrical, AirCon and WindB, would it?

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Nor did yours, did it?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    This works for me

    =IF(D1="Building",Building,IF(D1="Electrical",Electrical,IF(D1="Heating and Ventilation",HandV,IF(D1="Air Conditioning",AirCon,IF(D1="Window Blinds",Blinds,"Please enter Description")))))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Quote Originally Posted by xld
    Nor did yours, did it?
    Sorry when I first put it up it was wrong, I did repost it but you probably didn't see it. It's this:

    =IF(D1="Building",Building,IF(D1="Electrical",Electrical,IF(D1="Heating and Ventilation",HandV,IF(D1="Air Conditioning",AirCon,IF(D1="Window Blinds",WindB,"Please enter Description")))))

  11. #11
    Quote Originally Posted by xld
    Nor did yours, did it?
    Sorry, when I first put it up it was wrong. I did post the correction but you probably didn't see it. The formula is:

    =IF(D1="Building",Building,IF(D1="Electrical",Electrical,IF(D1="Heating and Ventilation",HandV,IF(D1="Air Conditioning",AirCon,IF(D1="Window Blinds",WindB,"Please enter Description")))))

  12. #12
    Quote Originally Posted by xld
    This works for me

    =IF(D1="Building",Building,IF(D1="Electrical",Electrical,IF(D1="Heating and Ventilation",HandV,IF(D1="Air Conditioning",AirCon,IF(D1="Window Blinds",Blinds,"Please enter Description")))))
    I know, I don't know why, but it's not working for me! Any ideas?

  13. #13
    could it be something to do with my settings?

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Maybe, but I can't think what. Did you check the DV type as I suggested?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Quote Originally Posted by xld
    Maybe, but I can't think what. Did you check the DV type as I suggested?
    Yes, it's definitely is set as List!

  16. #16
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Post your workbook and see if that works or fails for us.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  17. #17
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Do not post your workbook as that would make it entirely too easy to see what the problem might be and would shorten the number of posts required to solve your problem.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  18. #18
    Quote Originally Posted by xld
    Post your workbook and see if that works or fails for us.
    I can't it's too big! Even when it's zipped with all the unnecessary stuff taken out! There must be some way to get it smaller?

  19. #19
    It's working now! I didn't really change anything I just put $D4 instead of just D4 and it worked! Does anyone know why that was?

    =IF($D4="Building",Building,IF($D4="Electrical",Electrical,IF($D4="Air Conditioning",AirCon,IF($D4="Heating and Ventilation",HandV,IF($D4="Window Blinds",WindB,"Please Enter Description")))))

  20. #20
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I didn't use the $, so it can't be anything to do with that.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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