Excel 2000 - VLOOKUP & Filter Criteria

[Rodopi]

Having problems with using Advanced Filter using VLOOKUP derived criteria. No data is returned from Filter process other than headings.
Criteria display returned from =VLOOKUP(G2,H4:I15,2,FALSE) is correct e.g. <31/2/2004 and entering this data in cell directly works fine.
I suspect that the displayed value is not being passed to the filter process.
Unable to give a screen dump here but can provide is necessary.

 

[DaleWatson123321]

Hi Rodopi,

I only observed your posting late in the day, due to having prior commitments. I hope this gets to you in time to help out.

Short answer first... I created a model based on your data and got it to work fine.

My model includes your exact situation - i.e. the use of the SAME formula =VLOOKUP(G2,H4:I15,2,FALSE), copied directly from "my" model - from the criteria.

One possible cause of your problem... The date you gave in your example... is NOT a VALID date - i.e. February does not have 31 days. I suspect you probably meant: <31/12/2004.

To get my formula to work, I actually had to switch the Month/Day - because my default is set to Month/Day. I suspect your default is Day/Month, but if NOT, then this could be your problem.

I hope the above helps. Please advise as to how you make out. Further help can be provided if needed.

Regards, Dale Watson

 

[Rodopi]

Hi Dale,

Many thanks for responding...yes I did mean 31/12/2004 (Typo).

I don't understand what is happening now...I removed the VLOOKUP and keyed the date criteria directly and retried...same result...only headings returned.
tried your mm/dd/yyyy format...same result.

I gues the only way you will be able to help is if I could send you my workbook...not sure if you want to go to that length.

Thanks anyway Dale.


Regards
Rodopi

 

[DaleWatson123321]

Rodopi,

I'm glad you received the posting (while you still need to resolve your problem).

In answer to your question, I'd be pleased to have a look at your file. In fact, it would make my task of resolving your problem MUCH easier. If you like, I could even add "some bells and whistles" in terms of full utilization of Excel's Advanced Filter.

At one time, I posted my email address with every posting. However, a "rule change" at Tek-Tips is that email addresses are no longer to be posted. The reason for this relates to problems caused by spammers, etc. for Tek-Tips management.

However, I've since viewed other postings by long-time Tek-Tips contributors where they created a "workaround" solution - by providing their email address in a manner that (apparently) prevents use by spammers. Based on this precedent, I feel comfortable in providing my email address below. You'll need to replace each "DOT" with a ".", and replace the "AT" with "@".

ndDOTwatsonATshawDOTca

Regards, Dale Watson

 

[Rodopi]

Dale,

Many thanks for your assistants.

Copied all data to another sheet and it now works ok.

Unable to explain this...but at least I now have a workable solution.

Thanks again

Rodopi

 

[DaleWatson123321]

Rodopi,

I'm pleased you were able to resolve your problem.

Regarding your comment "Unable to explain this..."
I can explain, as follows...

Excel has had a "known bug" for a considerable number of years. But let me be a little more specific when I say "known bug". It's been known to VERY FEW Excel users. I became aware of it because of having had considerable experience with Lotus 123 in the area of database functionality. Based on this, I knew what "should be possible" in Excel, and kept on experimenting whenever I encountered unexpected problems, until I found the solution.

The solution (in this specific type of situaton)... is to place the criteria on a SEPARATE sheet - i.e. NOT on the same sheet containing your database - the data to which the criteria refers via database formulas or via VBA code.

Problems will NOT ALWAYS surface when criteria is placed on the SAME sheet. However, problems can definitely surface, either initially or at some later stage of development of an application. Therefore, I HIGHLY recommend to ALL Excel users, to ALWAYS place your criteria on a SEPARATE sheet.

Microsoft likely still has not officially recognized this "known bug". It appears this is primarily because Microsoft has shown MUCH GREATER preference to Pivot Tables, and has essentially not provided comparable promotion of the "Advanced Filter" functionality of Excel. This is most unfortunate, because, as you have possibly discovered, the "Advanced Filter" functionality can be EXTREMELY powerful and useful.

I hope this explanation helps clarify for you why your moving your data worked.

On the "outside chance" that you might have moved ALL data (including your criteria) to another sheet, but they (data and criteria) are STILL on the SAME sheet... Copying the data to a new sheet might have caused the criteria to work, but this could be an "unstable" situation - i.e. where the solution could only be "temporary". To be "certain" to eliminate future problems, move your criteria to a SEPARATE sheet.

And for ALL future "Advanced Filter" tasks, you should develop a habit of ALWAYS creating a SEPARATE sheet for your criteria, and probably assign an obvious sheet name like "Criteria".

I hope this helps.

Regards, Dale Watson

 

[xlbo]

Dale - spot on for the email addys btw

Although we still don't like threads going off-line, as long as a solution is posted, there is no harm done...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DaleWatson123321]

Geoff,

Thanks very much for your clarification. It's much appreciated.

Regards, Dale