Solved: Data Validation Error

Printable View

07-04-2007, 06:43 AM
icthus123

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?
07-04-2007, 06:51 AM
CCkfm2000

should your code be

Quote:

=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?
07-04-2007, 06:54 AM
icthus123

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.
07-04-2007, 06:55 AM
Bob Phillips

Have you chosen a type of List in the DV, not Custom?
07-04-2007, 06:57 AM
icthus123

Quote:

Originally Posted by xld

Have you chosen a type of List in the DV, not Custom?

I'm not sure what you mean?
07-04-2007, 06:59 AM
Bob Phillips

Try this formula

=IF(OR(D1="Building",D1="Electrical",D1="Heating and Ventilation",D1="Air Conditioning",D1="Window Blinds"),Building,"Please enter Description")
07-04-2007, 07:03 AM
icthus123

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? :dunno
07-04-2007, 07:05 AM
Bob Phillips

Nor did yours, did it?
07-04-2007, 07:10 AM
Bob Phillips

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")))))
07-04-2007, 07:12 AM
icthus123

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")))))
07-04-2007, 07:13 AM
icthus123

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")))))
07-04-2007, 07:16 AM
icthus123

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! :dunno Any ideas?
07-04-2007, 07:41 AM
icthus123

could it be something to do with my settings?
07-04-2007, 07:45 AM
Bob Phillips

Maybe, but I can't think what. Did you check the DV type as I suggested?
07-04-2007, 07:48 AM
icthus123

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!
07-04-2007, 07:50 AM
Bob Phillips

Post your workbook and see if that works or fails for us.
07-04-2007, 07:51 AM
lucas

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.
07-04-2007, 08:27 AM
icthus123

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?
07-04-2007, 08:43 AM
icthus123

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")))))
07-04-2007, 08:47 AM
Bob Phillips

I didn't use the $, so it can't be anything to do with that.