PDA

View Full Version : Solved: Data Validation Error



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

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

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?

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

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

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

I'm not sure what you mean?

xld
07-04-2007, 06:59 AM
Try this formula

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

icthus123
07-04-2007, 07:03 AM
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

xld
07-04-2007, 07:05 AM
Nor did yours, did it?

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

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

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

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

icthus123
07-04-2007, 07:41 AM
could it be something to do with my settings?

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

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

Yes, it's definitely is set as List!

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

lucas
07-04-2007, 07:51 AM
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.

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

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

xld
07-04-2007, 08:47 AM
I didn't use the $, so it can't be anything to do with that.