Macro or Formula to filter a column based on another cell value ?

[JumboCactuar]

Hi,

Example:
in cell A1 on sheet 1

20161211

sheet 2 (column AB)

Dates:
20161211
20161210
20161211
20161207
20161211
20161212
20161209
20161208

so from the above for today i just need to filter 3 entries for 20161211



Sheet 2 has lots of data from over the past week, how can i filter this for todays date (based on the value in sheet 1 A1) ?

Sheets("Sheet2").Range("AB:AB).AutoFilter Field:=1, Criteria1:="(whatever is in CELL A1 sheet 1)"

any suggestions?

thanks

 

[Eric W]

Can you just use Excel's built-in Filter tool?

Make sure column AB on Sheet2 has a header row. Select column AB. Click Filter (Either Home tab > Editing section > Sort & Filter > Filter, or Data tab > Sort & Filter section > Filter). Then click the down arrow in column AB, uncheck All, and check the date you want.

HTH

 

[JumboCactuar]

Can you just use Excel's built-in Filter tool?

Make sure column AB on Sheet2 has a header row. Select column AB. Click Filter (Either Home tab > Editing section > Sort & Filter > Filter, or Data tab > Sort & Filter section > Filter). Then click the down arrow in column AB, uncheck All, and check the date you want.

HTH

Thanks for the suggestion but i need to automate this as the data is in a locked sheet. I then have a button to filter the data then print

 

[Eric W]

So sheet1 is the summary, and sheet2 is locked and has the data? Do you just want a list of matching data from sheet2 populated to sheet1? Where on sheet1? Are there other rows from sheet2 you want included on sheet1? Is the date on sheet2 an actual Excel date, or is it a date formatted as text?

 

[JumboCactuar]

ADVERTISEMENT

Can you just use Excel's built-in Filter tool?

Make sure column AB on Sheet2 has a header row. Select column AB. Click Filter (Either Home tab > Editing section > Sort & Filter > Filter, or Data tab > Sort & Filter section > Filter). Then click the down arrow in column AB, uncheck All, and check the date you want.

HTH

What i have currently:
Sheet1 (summary) which contains a cell to enter date then buttons to print (sheet3)

Sheet2 has the data i use for filtering which i filter then copy/paste values to sheet3 A1 which then analyses the values in a neat report.

Dates are just formatted as text

 

[Eric W]

It would help if you could post a sample of your sheets. Show the cell from Sheet1 with the date, show the data on Sheet2 that you want filtered, and show the expected results on Sheet3. The HTML maker in my signature is helpful to do that.

Also, do you want a formula solution, or a macro? It sounds like you already have a macro to filter the data then print. Could you post that?

 

[JumboCactuar]

Thanks for the reply eric, appreciate the help

I figured this out just not long ago (well at least it looks as if its working)

Simple as adding this to the autofilter

Criteria1:=Range("A2").Value

 

[Eric W]

Nice. That's definitely simpler than I could have come up with without seeing your sheet. Glad you got it working.