+ Reply to Thread
Results 1 to 9 of 9

Index only visible cells

  1. #1
    Registered User
    Join Date
    05-01-2020
    Location
    California
    MS-Off Ver
    1908
    Posts
    4

    Index only visible cells

    Hi,
    I'm struggling to pull data out of a filtered table via the Index function.
    I have tried to follow the examples provided in similar posts but failing - please help.

    Basically I want to filter on "SD Theme", column L.
    Then using my "Designator" in column B populate the 'action tracker' tab. The arrangement in 'action tracker' will then be copied to powerpoint to present the overall status.
    I use some helper cells to position the graphs on the final slide and so my formula (without attempting to just read visible cells) is =IFERROR(INDEX(T.Data[Designator],B$2+4*($A5-1)),"")

    Thank you for your help!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index only visible cells

    what do you mean visible cells? how will your index change if it takes into account only visible cells? maybe you can explain what are you trying to achieve, and you will get help on understanding how to. Perhaps visible cells is not necessarily the answer.
    are you trying to achieve

  3. #3
    Registered User
    Join Date
    05-01-2020
    Location
    California
    MS-Off Ver
    1908
    Posts
    4

    Re: Index only visible cells

    When I filter on column L, some rows are filtered (hidden).
    In my action tracker I want to only show those cells that are still visible (not hidden).

    Is that more clear?

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index only visible cells

    not really.
    so if you filter only "boring stuff" in column L "Data" sheet, showing only 1.03_S;1.08_R; 1.14_S in designator column - you expect "action trackers" to show only columns B, D, and F and hide all the rest?

  5. #5
    Registered User
    Join Date
    05-01-2020
    Location
    California
    MS-Off Ver
    1908
    Posts
    4

    Re: Index only visible cells

    Sorry. No.

    Unfiltered, the action tracker tab should show 15 graphs.


    If I filter on "boring stuff", I want the action tracker tab to only show 3 graphs (1.03_S, 1.08_R, 1.14_S)
    Currently the same 15 graphs are shown.

    This is what I would like to see instead (3 graphs):
    Attached Images Attached Images
    Last edited by Stello; 05-06-2020 at 06:21 PM.

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index only visible cells

    sorry dont know a way of doing it, perhaps not even possible in excel, maybe a VBA code will allow that...you should try and post a link in VBA foum.

    BTW - the attachments dont open but I understood your request.

  7. #7
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Index only visible cells

    You can use
    Please Login or Register  to view this content.
    if you need 0 from hidden cell

    or
    Please Login or Register  to view this content.
    if you would like to hide value from chart but charts will always present.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index only visible cells

    Quote Originally Posted by belinda200 View Post
    sorry dont know a way of doing it, perhaps not even possible in excel, maybe a VBA code will allow that...you should try and post a link in VBA foum.

    BTW - the attachments dont open but I understood your request.
    Please do not recommend that members post the same query in another sub-forum - duplicate posting is not allowed here.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  9. #9
    Registered User
    Join Date
    05-01-2020
    Location
    California
    MS-Off Ver
    1908
    Posts
    4

    Re: Index only visible cells

    Thank you BMV.
    So there is no way to get my action tracker to look like the screenshot?
    Attachment 675895
    Attached Images Attached Images
    Last edited by Stello; 05-06-2020 at 06:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index only visible cells
    By profecientiamnot in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-19-2021, 07:28 AM
  2. INDEX formula to only pull visible cells
    By chrislid33 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-18-2018, 07:01 PM
  3. [SOLVED] using Index, Match, Large, and Visible Cells in one formula
    By jomili in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-10-2013, 01:58 AM
  4. [SOLVED] Copy/Pasting Values from visible cells (using autofilter) to visible cells
    By evakotsi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2012, 07:49 AM
  5. Replies: 2
    Last Post: 06-20-2012, 12:22 PM
  6. Copy Visible cells and paste values only to visible target cells
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2010, 04:09 AM

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