Filtering on Formulas (makes messy sheets)

  • Thread starter Thread starter Progster
  • Start date Start date
P

Progster

P.S. Re: Advanced Filtering and formulas such as '=X2<=V2'

Is there any way, inside Excel, via a macro, or via an add-on, that I can
do filtering on such formulas (you can see I'm just comparing two values in
different columns) WITHOUT having to place the formula first into a cell in
the spreadsheet itself.

Alternatively, if someone can offer general advice about where to best place
such formulas so as to reduce the likelyhood of messing up the spreadsheet
now or in the future, I'd appreciate that advice.
 
for the critiera range, put in a dummy header, like Dummy (shouldn't match
any of your real header names)

below the dummy header put in your formula

Dummy
=X2<V2

where the range references refer to the row below the header row.

Worked for me.

so a macro:
Sub CopyData()
Range("AC1") = "Dummy"
Range("AC2").Formula = "=X2<V2"
Range("AE1:AG1").Value = Array("Header1", "Header22", "Header24")
Range("A1:Z50").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("AC1:AC2"), _
CopyToRange:=Range("AE1:AG1"), Unique:=False

End Sub






Regards,
Tom Ogilvy
 
Back
Top