Data Type Error

[specialk802]

I am currently trying to use an OFFSET formula to declare a range for use in a Drop Down List. The formula works fine in a cell or range of cells however, when I try to use it in the Data Validation source it throws an error claiming something is wrong in the formula. After some investigation I found that it is considering the output from the match in the formula to be of the wrong data type despite showing a 2 and the data type when tested separately in a cell also resulted in a number. Any help to resolve this issue would be appreciated. The formula is below;

=OFFSET(Library!E2,MATCH(Reference!E4,ComponentLibrary[Connector],0),0,,7)

The error I receive when checking the formula;

Error In Value: A value used in the formula is of the wrong data type.

It then shows the formula reaching the error in the last step of the evaluation as;

=OFFSET(E2,2,0,,7) Note the 2 is in Italics

Not sure why it is evaluating it as a different data type. Any suggestions?

 

[JoeMo]

When you use a formula for data validation, the formula must evaluate to True of False. Unless your offset formula returns a cell that has True or False as its value, the formula doesn't meet this test. Perhaps the reference to data type points to the return value not having a Boolean data type.

 

[specialk802]

Joe,

Why is it then you can select a range for data validation? That is not a Boolean data type or is it? Additionally I have seen dozens of examples using very similar equations of =OFFSET(MATCH) Structure such as the one in the link and not have any issues.

http://www.contextures.com/xlDataVal13.html

 

[JoeMo]

Joe,

Why is it then you can select a range for data validation? That is not a Boolean data type or is it? Additionally I have seen dozens of examples using very similar equations of =OFFSET(MATCH) Structure such as the one in the link and not have any issues.

http://www.contextures.com/xlDataVal13.html

The Boolean restriction applies if you are using Custom and entering a formula in the Data Validation dialog box. If you are using a List and entering a formula that returns a list of values from your sheet, that's a different case. Can you expand on exactly what you are doing - List or Custom? If list, is it in a column or a row?

 

[specialk802]

Joe,

Sorry about that, thought I pointed it out in the original post. I am using it in a List data validation. The list is coming from a row. all the examples I have seen are from columns but I don't fully see why pulling a list from a row shouldn't work.

 

[JoeMo]

Joe,

Sorry about that, thought I pointed it out in the original post. I am using it in a List data validation. The list is coming from a row. all the examples I have seen are from columns but I don't fully see why pulling a list from a row shouldn't work.

I agree, but it's hard to know, just looking at your formula, what the issue might be.