IF Function based on Autofilter results

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hello All

I would like to be able to modify the results of a formula based on the
autofilter criteria sected by the user. So if the user filters by rows with
"Green" in, then I want to multiply a subtotal of filtered rows by a certain
number, eg value in one cell. If user selects "Blue", then a different
number should be used, eg 30, or value in another cell..

eg: =IF(User selected Blue, (a36*a38),(a36*a39))

What can I put in for "User selected Blue"?

Regards
Steve
 
Hi Steve
without VBA not possible.
For just summing use
=SUBTOTAL(9, A1:A100)

But to get the chosen criteria VBA is required
 
=IF(INDEX(A1:A10,MAX(ROW(A1:A10)*SUBTOTAL(3,OFFSET
(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1))))="Blue",...)

Array-entered, meaning press ctrl/shift/enter. I left out
the rest of the IF statment intentionally. Change the
range A1:A10 if needed.

HTH
Jason
Atlanta, GA
 
Frank Kabel said:
Hi Steve
without VBA not possible.
For just summing use
=SUBTOTAL(9, A1:A100)

But to get the chosen criteria VBA is required
Click to expand...

Thanks for your help, I did go down the VBA route, using this code, which
does what I want it to.

Private Sub Worksheet_Calculate()

If Worksheets("Thissheet").AutoFilter.Filters(2).On Then
Select Case Worksheets("Thissheet").AutoFilter.Filters(2).Criteria1
Case "=Green"
Worksheets("Thissheet").Cells(120, 3).Value =
Worksheets("Thissheet").Cells(2, 8).Value
Case "=Blue"
Worksheets("Thissheet").Cells(120, 3).Value =
Worksheets("Thissheet").Cells(3, 8).Value
Case Else
Worksheets("Thissheet").Cells(120, 3).Value =
Worksheets("Thissheet").Cells(1, 8).Value
End Select
End If

End Sub
 
Jason Morin said:
=IF(INDEX(A1:A10,MAX(ROW(A1:A10)*SUBTOTAL(3,OFFSET
(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1))))="Blue",...)

Array-entered, meaning press ctrl/shift/enter. I left out
the rest of the IF statment intentionally. Change the
range A1:A10 if needed.

HTH
Jason
Atlanta, GA
Click to expand...

Jason

I had a problem with this, it would only work if "Blue" was the first in the
list. I decided to use a macro anyway.

Thanks
Steve
 
Back
Top