Forum Discussion

Dave Thackeray's avatar
Dave Thackeray
Copper Contributor
Jul 23, 2018

Show filtered data in one spreadsheet clicking buttons in another

Morning, champions!

Two spreadsheets in one workbook.

In one spreadsheet I have thousands of rows. I have identified about 350 groupings of these rows and by clicking one of 350 corresponding links in an accompanying index spreadsheet (in same workbook) I want to show only the filtered rows in the first spreadsheet.

I imagine I would tag the grouped rows with a numerical identifier. However there may be occasions where the same row falls into different groupings of rows so I don't know if this kind of straightforward identifier would work.

Desired process
User selects index spreadsheet
Finds required story ID
Clicks corresponding link or macro button
Original spreadsheet appears automatically filtered to display only rows corresponding with Story ID.

Is this possible? Or could the desired data simply appear in third spreadsheet that would in effect act as a view of filtered data?

Thank you so much.
Macros & VBA
VBA
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jul 23, 2018
    Hi Dave,
    Have you used Power Query at all? You could use that to grab the first full list and then filter by the 2nd Index List (using a merge operation) and output the filtered list to a new sheet.
    I can attach a demo file.
    What version of Excel are you on Excel 2016?
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jul 23, 2018
    Also it would be great if you could attach a small mock up example as I'm not quite clear on the grouping aspect : "However there may be occasions where the same row falls into different groupings of rows so I don't know if this kind of straightforward identifier would work."
    • Dave Thackeray's avatar
      Dave Thackeray
      Copper Contributor
      Jul 23, 2018

      Hey, Wyn. Thank you so much for volunteering your support. It's much appreciated.

      I'm an advanced beginner in the dark art of Excel so haven't yet discovered the wonders of Power Query.

      To expand on my requirement:

       

      • I'm identifying a huge number of tasks our website visitors need to accomplish online.
      • Each task comprises a series of URLs and 'auditor comments' (two separate columns) displayed in the contentAudit spreadsheet
      • There's a second spreadsheet called topTasks that serves as an index of all the tasks (there will be hundreds)
      • Each of the tasks will have an identifier which will be used in a column on the contentAudit spreadsheet to group all the corresponding URLs for each task
      • Both spreadsheets are in the same workbook.
      • People will use the topTasks spreadsheet to identify which tasks require immediate attention (based on cells showing the priority of each task)
      • A link or button on each row of the topTasks spreadsheet will display the contentAudit spreadsheet with only the URLs corresponding to that task, shown. 

      I imagine this process will be repeated hundreds of times, which means I do not want a new tab created every time someone clicks a link to see the filtered contentAudit spreadsheet.

       

      I've attached a couple of screengrabs to show what I mean.

       

      This is contentAudit showing the URLs filtered according to the ID of 1. I hope we can achieve this automatically when the user clicks a link in the topTasks row for task 1.This is topTasks - the index - showing IDs and tasks

       

      Does this help?

       

      Thanks for giving this a shot for me. Much appreciated!

Resources