How do I correct this issue

Question

Test 4.xls

To whom it may concern,

My issue is a problem that I originally took care several weeks ago, with help from this forum. Actually received some great advise from folks on this forum.

Now my problem, I originally saved the template in a .xlsx format brcause I thought it was compatable with my third party form filling software. Supposedly you could import ,xlsx file but in reality that not the case. In reality the software to my disbelief can only import files in the ,xls format. My problem is some of the formulas in the .xlsx format didn't convert over to the.xls format file, when I went to save the template.

I created a sample file attached which illustrate the problems. I know how to basically write a small portion of the formula. But in this case it invoves a date with a criteria range and that where I'm lost. Hoping someone can help me.

The source data can be found on sheet 2, The search criteria is tothe left of sheet 1 and the date range at tthe top of sheet 1.

What I'm trying to do is in the "Sheet1" (AE16 : ..........) cells and that column/range cells, is to apply the GLA search criteria (G26) to (I26) and the date filter (AE4) and (AE6) to the data found via the "Sheet2"(J16:J1225). The row 1225 is the potential row but the data most likely will be short of that. Then insert the results into the (AE16 : .........) range. The source date is "Sheet2(F16 : .......)

The lot Size criteria is for the "Sheet1"(AG16 : ......) column/range of cells. The data to be search is from the "Sheet2"(N16 : .......) column/range. The search criteria is from "Sheet1"(G29) and (I29), filtered through the date filter of (AG4) and AG6). The source date is "Sheet2"(F16 ; ........).

Age criteria is from "Sheet1"(L16 : .......) column/range of cells. The search criteria data is from (G32) and (I32) with the date filter being between "Sheet1"(AL4) and "Sheet1"(AL6). The source data is from "Sheet2"(L16 : ....... ) and the date data is from "Sheet2"(L16 : ......) .

The odds are the source row will extend down 1225 row.

The odds are if you can figure out one formula, I can copy it over tothe other five columns.

Answer 1

Could you show an expected result on sheet1? Then I can have a try on it.