Excel Filtering Question Will some VBA code fix my small dilemn

[Dazzybeeguy]

I have a spreadsheet i.e Sheet 1 Cell A1:Z500 that has ongoing stuff in it, completed etc. So for example when everything is completed they would click on the relevant row i.e Cell Z2 and select yes from the Data Validation drop down.

It is a filtered spreadsheet so you could filter all the cases to show all the completed ones.

I want to have another sheet (Sheet2) just with the completed ones and did a simple =if($Z2="Yes",Z2,"") and I can copy this for each cell from A2:Z2 to bring the cell info across, I add the Filter to only show the filtered Yes from column Z.

The problem is as other rows on the sheet 1 are changed to completed in column Z in sheet 2 you physically need to click on the filter for that sheet to show the additional ones.

Is there a way to get around the user having to refilter ? Maybe VBA code ?

Thanks for your help.

 

[kevin9999]

I note you have 365. The Filter Function should be available to you, and you could achieve your end result without VBA. See FILTER function

 

[Dazzybeeguy]

I note you have 365. The Filter Function should be available to you, and you could achieve your end result without VBA. See FILTER function

Hi, thanks didn't know about that.

The only issue seems to be that for empty date fields it inputs 00/01/1900 instead of just leaving blank

 

[kevin9999]

Hi, thanks didn't know about that.

The only issue seems to be that for empty date fields it inputs 00/01/1900 instead of just leaving blank

You could get around that if you give the date fields in Sheet2 the Custom number format:

d/m/yyyy;;""