advanced filter three conditions does not contain, between and contain

This is probably best accomplished using helper columns.

I wasn't too sure how flexible you wanted the venues to be, so I put them in a table on sheet 2 and used ==ISNA(MATCH([@CITY],Table_Venue[Venue],0)) which yields TRUE if the city is not on this list.

Also, I am not quite sure what you mean by if a room capacity is blank that you want to accept it. I hope I interpreted it OK. I used a series of helper columns to get the formula in the rooms column. I substituted the pieces back until the formula is defined only in terms of the three rooms.

For the answer, filter on true in the Filter Column.

Hi,

You can also do this with an advanced filter using a criteria formula, but can you elaborate on what you mean by "include blanks"? Based on what you say the result should be, I think you mean to treat blanks as 0 values in the room capacity, but Dorking Hall would seem to meet your criteria too?

I have the same question: what do you mean by "not to show blanks on the rooms?" If a cell for the room size is blank what do I do with it? Does being blank mean that the room is OK and should be included for consideration or does being blank mean ignore this cell?

Here is your file using an advanced filter with a formula criteria range and a list of cities that you can adjust as required.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
http://www.excelguru.ca/forums/showt...en-and-contain


After 48 posts you should know this. You're lucky this forum is indulgent with your kind of posters