+ Reply to Thread
Results 1 to 20 of 20

Linking cells to filtered results in a separate worksheet

  1. #1
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Linking cells to filtered results in a separate worksheet

    Hi all,

    I have a workbook with about 20 worksheets, each containing information on linkedIn groups for a certain product. Every worksheet is formatted with the same data. So one worksheet might have 30 different linkedIn group names with one column that has regions like US, India, Europe, etc.The first worksheet is a chart with the total count on each worksheet of the groups by region.

    I want to link the cells in the chart to each worksheet, but only have the link show the filtered results. Say i click the count of 24 for India under the Software product listing on the chart. I want that to link to the Software tab, but only showing the 24 groups from India. I know how to hyperlink to tabs, but I can't figure out how to link to only the filtered results, and I'm trying to avoid making a lot of tabs with each product separated by region.

    Any help would be appreciated

    Thanks,
    Greg

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,626

    Re: Linking cells to filtered results in a separate worksheet

    Hi

    Would you upload your workbook so that we can see the request in context?
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    Example spreadsheet.xlsx

    I uploaded an example spreadsheet here. Basically, I want to be able to click on the 8 (software in europe) and have it hyperlinked to go to the software spreadsheet, but show only groups from Europe. So apply some kind of autofilter to the hyperlink.

    Thanks,
    Greg

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,626

    Re: Linking cells to filtered results in a separate worksheet

    Hi,

    Does the attached help. It's now a .xlsm macro enabled file. When you double click a row in the table on the Total sheet the software sheet will be filtered accordingly.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    That's pretty much what I need. Is there a way to run a macro on each number, so if i double click a number in the HR column it will link to the filtered HR page?

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,626

    Re: Linking cells to filtered results in a separate worksheet

    Hi,

    See attached

    You'll now need to double click in either column B or Column C
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    Perfect! Thats exactly what I needed. Is there a way to copy the macro to my actual workbook?

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,626

    Re: Linking cells to filtered results in a separate worksheet

    Hi,

    You'll first need to create the two dynamic range names that I've given to the data range on the Software and HR sheets. And note that I deleted the completely blank columns in your data.

    Then copy the macro in the Total sheet's Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) procedure to the same sheet procedure in your actual workbook.

  9. #9
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    Ok, i got it to jump to the right sheet, but I can't get the filter.

    If Not Intersect(Target, Range("D:D")) Is Nothing Then

    stRegion = Range("A" & ActiveCell.Row)
    Call Module1.FilterHW
    Sheet5.Activate

    Do i need to change the "FilterHW" in the above?

  10. #10
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,626

    Re: Linking cells to filtered results in a separate worksheet

    Hi,

    The complete sheet change procedure in the Total sheet should be

    Please Login or Register  to view this content.
    Change the refs. to columns B&C if your original data has changed location.

    The second Call Module1....should be Call Module1.FilterHW. I should have named the procedure FilterHR to be consistent but a typo crept in. You may change it if you prefer.

  11. #11
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    Capture.PNG

    I keep getting this error message. I created dynamic ranges ( SAPHR and SAPBI) and changed the code to reflect the correct sheets, but it just gives me this error.

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,626

    Re: Linking cells to filtered results in a separate worksheet

    Hi,

    You'll need to upload the workbook you are using. I can't tell from a picture.

  13. #13
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    Attachment 258467

    I keep getting this error message. I created dynamic ranges ( SAPHR and SAPBI) and changed the code to reflect the correct sheets, but it just gives me this error.

  14. #14
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    sorry repost. Can I send it to you in a private message?

  15. #15
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,626

    Re: Linking cells to filtered results in a separate worksheet

    No. It will need to be in the forum.

  16. #16
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    LIG Macro.xlsm

    Ok, I added all the dynamic ranges, but I'm not sure where I'm going wrong writing the macros.

    Thanks,
    Greg

  17. #17
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,626

    Re: Linking cells to filtered results in a separate worksheet

    Because you'd not added code for all of the 25 sheets. There are still only the original two which only detect when columns B & C are clicked

    Had you said there were 25 sheets rather than just the two your original posting mentioned I would have coded something different and avoided all the dynamic range names. With only two that was the simplest approach. It pays to mention all relevant factors when you first post.

    I've changed the labels on row 3 so that they match your sheet names and removed all the dynamic range names.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    Sorry about that, I mentioned 20 sheets in my first post but uploaded a sample workbook.

    Right now the cells link until column N, and I get a runtime error saying the subscript is out of range. There's only a couple lines of code on Sheet1, so I'm not sure what to copy to make all the cells link to their respective sheets.

  19. #19
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,626

    Re: Linking cells to filtered results in a separate worksheet

    Hi,

    There is no sheet named "IBM Websphere".

    You need to ensure that all the labels on Row 3 of the Totals Sheet have a sheet of the same name.

  20. #20
    Registered User
    Join Date
    08-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Linking cells to filtered results in a separate worksheet

    Aha, just fixed that, its working great now. Thanks a lot Richard, you were a ton of help and I couldn't have done it without you.

+ 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. Linking Charts to rows on separate worksheet
    By stevenette in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 07-11-2011, 10:03 PM
  2. Showing filtered results on separate sheets
    By mhodges in forum Excel General
    Replies: 7
    Last Post: 06-11-2010, 04:03 AM
  3. Linking into filtered cells
    By md1972 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-30-2008, 08:49 AM
  4. Linking to a worksheet in a separate workbook
    By AcesUp in forum Excel General
    Replies: 3
    Last Post: 06-03-2006, 09:55 AM
  5. [SOLVED] Separate Worksheet Linking
    By JaeP in forum Excel General
    Replies: 0
    Last Post: 05-17-2005, 12:06 PM

Tags for this Thread

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