displaying filter arrows when pasting excel into powerpoint

[confused]

Hi,

i have copied excel worksheets into my powerpoint presentation using 'Paste
special'->'Paste link' -> Microsoft Excel Worksheet object'.
the original excel sheets have filters and so displays a little arrows in
the first row of filtered columns.
the strange thing is, in powerpoint, the copied worksheet sometimes shows
this arrows and sometimes doesnt. I can understand why it is inconsistent. In
all cases the filter is 'on'. Idealy i am trying work out why, so that i can
ensure none of the arrows are displayed in powerpoint.
anyone got any ideas about this strange quirk?

thanks for any help!

 

[Brian Reilly, MVP]

Yes, this is confusing.
I can paste link a filtered range and get just the filtered data with
no filter arrow. But as soon as I update the link, I get the filters.

And if after updating the link I turn off auto filter in XL, I get the
entire range unfiltered that I copied but no filter arrow.

I'm assuming you want to show only the filtered values and hide the
rest without the filter arrow, and then do the same when you change
the filter in Excel.

To do this, I think you'd want to explore a second column that is
formula driven e.g. =if(A2="red","red", "") which will give you red in
that cell if A2 =red. If it doesn't equal red then it would be blank
"".

Then i think you'd want a little macro to iterate through the cells
and if they are blank then Hide the row.

Not too hard to do. But then I don't really know what you are reallly
trying to do.

You probably could also do a Pivot table solution, but I'm not going
to explain that one here.

Actually, the real right way to do this would be to use formulas in a
different column that would dynamically set the named range of the
filled in cells. Vasant, an Excel MVP, wrote a formula for me that did
that a while back. There are several examples on the web showing how
to do this. Google on Excel dynamic range names

Brian Reilly, MVP