+ Reply to Thread
Results 1 to 12 of 12

"Mirror" of filtered data, using formulas.

  1. #1
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,745

    "Mirror" of filtered data, using formulas.

    My intention is to show a simple way, using quite simple formulas, how to create in a separate sheet, a "mirror" of the data in the first sheet, before and after, using an automatic filter, in this first sheet.

    See and evaluate it!
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: "Mirror" of filtered data, using formulas.

    This is interesting as I didn't think about this before, but I have use for something similar to this.

    Thanks for sharing.

    abousetta
    Last edited by abousetta; 07-09-2012 at 01:46 PM.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: "Mirror" of filtered data, using formulas.

    Awesome ....nice spreadsheet.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,454

    Re: "Mirror" of filtered data, using formulas.

    Nice one, Fotis.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: "Mirror" of filtered data, using formulas.

    nice
    just a point tho fyi ROW()-ROW(D$4) is always a bit awkward maybe replace that with row(a1) or ROWS($A$1:A1) or even ROWS($A$5:A5)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,643

    Re: "Mirror" of filtered data, using formulas.

    Please Login or Register  to view this content.



  7. #7
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,418

    Re: "Mirror" of filtered data, using formulas.

    very nice @fotis!

    with 2007, this is a bit easier to achieve through the use of the Camera tool (which can be found under More Commands > All Commands). i have updated your file with that feature too for the benefit of those who may be looking for this sort of a functionality...
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: "Mirror" of filtered data, using formulas.

    btw, the camera tool is not new to 2007 and only gives you a picture - you can't then do anything further with those cells as you can with the original tip. it should also be used with care as it can grind any macros to a halt.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  9. #9
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,418

    Re: "Mirror" of filtered data, using formulas.

    super! good information. i wonder why macros that would be so - macro unfriendly?

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: "Mirror" of filtered data, using formulas.

    the camera tool works by creating a metafile (basically a vector image of whatever is in the region you took a snapshot of) which it displays in the control. since it's a live link this metafile has to be recreated when you make changes to the workbook, which slows things down even in the UI. the more complicated the image (eg you snapshot a range with charts & shapes etc) the more work involved. similarly the more snapshots you take, the more metafiles. normally in vba you turn off screenupdating to speed things up but this does not affect the camera images so the speed is affected badly. the simplest workaround is probably to use a conditional formula in the picture link so you can turn it off when you start processing. I reckon you could probably also use lockwindowupdate to stop the control from redrawing.
    having said all of that, it's still really useful if used well - especially as you can apply all kinds of effects to the picture.

    getting a mite sidetracked though I reckon ;-)
    Last edited by JosephP; 07-11-2012 at 08:20 AM.

  11. #11
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,418

    Re: "Mirror" of filtered data, using formulas.

    thank you, joseph. i think it just the right amount and type of information for me - no sidetracking there...

  12. #12
    Registered User
    Join Date
    06-15-2012
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    85

    Re: "Mirror" of filtered data, using formulas.

    Excellent, Fotis!

    Congr.!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1