Edit formulas to display the required data range

[kayoke]

Hello,
I'm facing problems doing up the formulas for the PreSearch tab, upper and lower range month columns (columns I &J). This current database is built to record training workshops that the employees have attended. The formulas should bring about the same results as column L & M (upper and lower year columns). I.e. to ask SearchEngine macro to return the data in the required range.

For example, I want to know the employees' data for month range of Jan to Apr, it should give me data in the SearchResults for Jan to Apr only and not the rest of the months. So far I've only figured out that the formulas for "Year" range works because it is a self keyed in range while the month range is based on a drop down box linked to Miscellaneous tab. However, I do not know how may I edit the formuals so that it functions as intended. Please advise. Thanks.


Regd,
Kay Yoke

[kayoke]

I've solved the question:

Because the formulas are
IF(SearchEngine!$P$9="",1,IF(Database!$I8>=MAX(SearchEngine!$P$9,0),1,0))
and
IF(SearchEngine!$Q$9="",1,IF(SearchEngine!$Q$9>=Database!$I8,1,0))
they are meant to compare no. but instead, I'm using this to compare text (Jan, Feb, Mar ...). Of course it doesn't work. To change solve it, I've to change my input of month to be in numericals (i.e. Jan = 1, Feb = 2, Mar = 3 ...)

Unless somebody can come up with a formula for recognizing text, if not this method would be the best so far.

[jason.b75]

Where A1 holds the month in text format,

=MONTH(1&A1)

would return the month as numeric, would nesting that in your existing formula do what you need?

[kayoke]

I don't under how your formula goes into my formula. Can you do it out for me?

[kayoke]

somebody in http://www.ozgrid.com/forum/showthread.php?t=167117 suggested using
=LOOKUP(A1,{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9})
but this is only applicable if A1 is in text (i.e. Jan, Feb ...) and I supposed the A1 meant the cell link for my drop down box to select the months? If so, my A1 would be in no. instead. I tried reversing the formula by putting the lookup no. in front then the text i.e.
=LOOKUP(A1,{4,8,12,2,1,7,6,3,5,11,10,9},{"April","August","December","February","January","July","June","March","May","November","October","September"})
but it doesn't work also.

I don't really know how all these formulas work so I just try-an-error for them.