Any Excel Gurus?

[flimper]

I have a probelm that I have given up on after wasting all of my morning on it, can anyone assist please?

 

[Taz]

what is the problem?

 

[flimper]

I am trying to conditionally format a column, but only to apply to the filtered cells

 

[srhutch]

I am trying to conditionally format a column, but only to apply to the filtered cells

Can you provide a sample sheet?

 

[GreyZed]

Use VLOOKUP to present the reduced data array instead of a filter then apply your conditional format to the column.

 

[flimper]

Could do, this is the formula I'm using to get the top 5% of years 1994-2016, how can I get it to apply to 2008 only when I filter. I know SUBTOTAL works for filtered cells only, but that doesn't have a function for PERCENTAGE I don't think.

=D3>=PERCENTILE($D$3:$D$12750,0.95)

 

[PerryGunn]

Normally you'd do something like this by selecting the visible cells and then applying the conditional format - Excel has a select visible cells button or, if your're using VBA , you can create a macro using

Cells.SpecialCells(xlCellTypeVisible).Select - this would select all visible cells on a sheet but you can restrict it to a range if you want

and you can apply conditional formatting using FormatConditions (just google something like excel vba .formatconditions and you should find lots of examples)

 

[flimper]

I tried using the select visible cells options but it didn't work. As for VBA, I'm out of my depth there

 

[PerryGunn]

If you know the range of cells (or thr row or column they're in), just record a macro then edit it so it contains something like

Sub Macro1()
Range("D512").Select
Selection.SpecialCells(xlCellTypeVisible).Select
End Sub

The Range("D512").Select line could as easily be Columns("B:E").Select or Rows("9").Select

 

[flimper]

How do I put a link to my excel spreadsheet?

 

[biojo]

you'd create a macro,

or a button then assign that vba to it

 

[buzyg]

How do I put a link to my excel spreadsheet?

If it's on a network drive I doubt that you can. If it's in a share point folder, that is not protected, which I doubt unless your at home on your private PC. you could copy the URL from the top of the screen and post it up. Otherwise I think you could send it as an attachment to a PM.

 

[buzyg]

Ignor my last doesn't look like you can attach files to a PM. Think You would need to send an e-mail with the file attached.

 

[Scooba_Steve]

Excel 2016 (and 13?) has the option to highlight top x%

What about pivoting to provide a summary?

 

[flimper]

I am using Excel 2007 unfortunately, never mind as given up on doing this now, too much hassle