Solved: This one may be WAY TOO DIFFICULT

Ok....here we go.

When using advanced filtering, I know you can search through text. And you can use wild-cards as well and all that.

My problem is I have text formatted like this:

Projected: 07-Dec-2004
0 Years 0 Months 7 Days

And I would like to be able to filter through cells like these for a range of dates :thinking: (try swallowing that).

I know normally (with dated formats in the cells) it could be done with something like:

Start Date | End Date
>=1/1/2005 | <=2/1/2005

but this is definitely not the case here.

And I was ALMOST done, too :mkay


Any ideas?

Joseph, can you put up a sample?

Okay....let me try to be a little more specific.

I'm using advanced filtering.

Normally if you want to search between dates you would do something as such:
Data:
Start Date | End Date
1/25/2005 | 2/24/2005
4/21/2004 | 6/23/2005
3/31/2003 | 5/25/2005

Advanced Filtering (criteria):
Start Date | End Date
>=12/1/2004 | <=6/1/2005

And this would search from December 1, 2004 up and through June 1, 2005. This works fine for my two main sheets.

But I have another sheet which looks like:
http://www.freeimagehosting.net/uploads/9a99cef78b.gif

And I would like to be able to filter this text through the same dates....which is insanely difficult if it were to be done the way I'm thinking it will be done http://vbaexpress.com/forum/images/smilies/sad2.gif (I won't even mention what THAT is....that would take too long to explain)

And that is what I'm talking about. http://vbaexpress.com/forum/images/smilies/045.gif Filtering text for a range of dates using advanced filtering.

I hope I was clear....if not, I'll elaborate further :thumb

What format have you applied to th cells?

A workbook would have been of more help.

I know, Bob...but it has sensitive data.....give me a little bit and I'll clear out the data and post a dummy workbook. (Not to mention the code is REALLY extensive....but I'll do it anyway)

And the cells are formatted as text, BTW

Here's a dummy workbook of what I'm doing.


Basically I want users to be able to perform advanced filtering without having to write up the criteria. So I made this in place of that...I know it's extensive....but It's much easier than teaching the users about advanced filtering :)

The way I have what I'm asking for now will filter for the months of the criteria fields, instead of specific dates. You'll see what I'm talking about.



Tell me what you think, too....I may submit something like this as a KB entry :thumb

EDIT:
Lemme give a little description about what happens....

User will only see three sheets: Forecast, Actual, Results

When users press the "Advanced Filter" button, the userform will show up. The form will have all unique values for filtering. The top frame in the form is for text, and the bottom for date values. When the user selects what they want to filter and presses "Filter", the form will make a layout of the criteria on the sheet "Criteria". It also makes a seperate layout for the "Results" sheet (because of my problem) because it needs a different layout for the dates. When the user selects the "Month" option (which will filter for the month asked for)...this poses no problem for my filter. The "Dates Between" is what is the problem...and the way I have it now is similar to the "Months" option....except when the user enters "Start Date" and "End Date" the procedure I wrote will only use the months of those values to filter (which is not exactly accurate).

And that is a little more of an explanation. I hope it is enough.

Quote:

---

Originally Posted by malik641

Here's a dummy workbook of what I'm doing.


Basically I want users to be able to perform advanced filtering without having to write up the criteria. So I made this in place of that...I know it's extensive....but It's much easier than teaching the users about advanced filtering :)

The way I have what I'm asking for now will filter for the months of the criteria fields, instead of specific dates. You'll see what I'm talking about.



Tell me what you think, too....I may submit something like this as a KB entry :thumb

EDIT:
Lemme give a little description about what happens....

User will only see three sheets: Forecast, Actual, Results

When users press the "Advanced Filter" button, the userform will show up. The form will have all unique values for filtering. The top frame in the form is for text, and the bottom for date values. When the user selects what they want to filter and presses "Filter", the form will make a layout of the criteria on the sheet "Criteria". It also makes a seperate layout for the "Results" sheet (because of my problem) because it needs a different layout for the dates. When the user selects the "Month" option (which will filter for the month asked for)...this poses no problem for my filter. The "Dates Between" is what is the problem...and the way I have it now is similar to the "Months" option....except when the user enters "Start Date" and "End Date" the procedure I wrote will only use the months of those values to filter (which is not exactly accurate).

And that is a little more of an explanation. I hope it is enough.

---

If it were me, I would add a hidden column with the date formula in there, and use that in th Advanced Filter. I was thinking along the lines of another hidden column extracing the date out of that coilumn, but it seems way overkill to me, seeing as you already get it somewhere else.

Quote:

---

Originally Posted by xld

If it were me, I would add a hidden column with the date formula in there, and use that in th Advanced Filter. I was thinking along the lines of another hidden column extracing the date out of that coilumn, but it seems way overkill to me, seeing as you already get it somewhere else.

---

I'm assuming you mean when you press "Filter" on the userform. And I thought about that too...but I just thought a new sheet was the easiest. I can clear all cells on every new filter search and not have to keep track of where hidden columns were on the sheets.

The plan is to use the "Criteria" sheet's data and create a defined name for the Criteria in the advanced filter for the "Forecast" and "Actual" sheets. Then use the "Results Criteria" sheet for the other "Results" filter criteria by giving the data from the "Results Criteria" sheet another defined name to use.

The problem is I'm on a pretty heavy deadline...so I don't have much time to rethink the design...like you said, I've gotten too far with this one.


It's a shame because I know I could've thought of a better way if I had more time.

Joseph,

I am not suggesting a fundamental re-design, just a hidden column with that date in, and use that in your criteria sheet rather than the existing concatenated column which cannot be used in the criteria.

Bob,

I'm sorry, but I'm not sure I understand what you mean. When you say "a hidden column with that date in"....which date? The dates from the userform? If so, under which option (Month, or Between Dates)?
And what exactly do you mean that I can't use the concatenated column for the advanced filtering? Do you mean by the dates?
(My apologies for the miscommunication, BTW)


And I think I may have a clever way of handling this...I realized that the "Results" sheet displays dates (Projected: dd/mm/yyyy) which are the same dates in the "Forecast" sheet. So whatever hidden rows there are after the filter on the "Forecast" sheet, will be the same on the "Results" sheet. So I need a way to find out with rows are hidden on the "Forecast" sheet and hide the same ones on the "Results" sheet. It's quick and dirty, but it will work :devil:

I'll see what I come up with.

And one other thing, too. On the "Forecast" sheet. Try performing an advanced filter in range "A6:R500" for...let's say, sponsor code. Just use any sponsor code. For some reason...it won't hide the blank rows????? Por que???

I got this.

Tell me what you think :thumb

No input? :dunno