Data Validation formulas works but not for all cells

[PaulM100]

Hi All,

I have the following issue. I have 3 column with data validation from 3 different sheets. My problem is that sometimes this works and sometimes they don't. I also have some formulas between them. Please see attached.

Thank you in advance,
Paul

[dflak]

See if either of these methods work for you.
http://www.utteraccess.com/wiki/Casc..._%28Non-VBA%29
http://www.utteraccess.com/wiki/Casc...ists_%28VBA%29

[JohnTopley]

Try

=OFFSET(City!$B$1,MATCH($B1,City!$J$1:$J$100,0)-1,0,COUNTIF(City!$J$1:$J$100,$B1))

[PaulM100]

Hi both,

First of all, thank you.

@dflak, I cannot change my file to pivot tables and VBA is above my current Excel skills

@John, your formula work, but so do mine, in my main file, column C. The one that is problematic is the one found in column E

To be more precise, I have 28 selections in column A, a little bit more in column C, and each one found in column C(the one for City), has one or multiple choices in Column E that stands for region.
Out of this 28 found in column A, 3 of them have building, 4 values, but my formula does not work for those.

In the file attached in my previous post, for AUS, CAN and CHIN the formula works in column E, but not for ROM, MAR, SOU and I cannot comprehend why,

I also have the error **The Source curently evaluates to an error**.

Do you have any ideas why?
Thanks

[JohnTopley]

The problem is trailing blanks on data.

[PaulM100]

It was so obvious, but not for me. Thank you John, you've saved me.