Filter data by using From and To dates without using Macros

  • Thread starter Thread starter tonyfdes
  • Start date Start date
T

tonyfdes

I want to get data filtered onto another sheet from the data collecte
based on FROM and TO dates as filters. Can this be done with out usin
Macros. I want only the Keyword to be in the other sheet and th
remaining I can use VLOOKUP to get the other dat
 
Hi

I think it'll be best when I use an example.

You have in some workbook a sheet Table with columns Date and Value in it,
and a sheet Report with From and To dates in cells B1:B2.

You can refer to cells $B$1 and $B$2, but I prefer to define them as named
ranges From and To.
Add a column to left of table on sheet Table, let's name this column as
OrderRange. Further I assume that in my example your data are in range
B2:C10, and OrderRange is column A.
Into cell A2 enter the formula:
=IF((Table!$B$2:$B2>=From)*(Table!$B$2:$B2<=To)=0,"",SUMPRODUCT((Table!$B$2:
$B2>=From)*(Table!$B$2:$B2<=To)))
and copy it down at least so long as there are data in your table (but you
can have it further down - waiting for would-be entries).
Hide the column A.

On sheet Report, p.e. into cell A4 enter p.e. "Nr", into cell B4 "Date" and
into C2 "Value"
A5=IF(ISERROR(VLOOKUP(ROW()-4,Table!$A$2:$C$10,1,FALSE)),"",VLOOKUP(ROW()-4,
Table!$A$2:$C$10,1,FALSE))
B5=IF(A5="","",VLOOKUP(A5,Table!$A$2:$C$10,2,FALSE))
C5=IF(A5="","",VLOOKUP(A5,Table!$A$2:$C$10,3,FALSE))
Select range A5:C5, and copy down as much as you think you ever will need.
 
Back
Top