Displaying relevant information on another tab

[Tripp]

Hello,

I have a table with 3000+ rows and growing with document numbers and titles. I would like to be able to mirror this table to create a sort of Relevant Info Viewing tab that only shows rows that contain a date in date column.

The reason for this 'mirror' is it is a shared excel document with many people accessing at once. The mirrored table is for other members to view documents that have a date against them whilst I work on the main list.

I have experimented with pivot tables but this doesn't produce what I need so I am open to suggestions of what I could use to facilitate this. I am also wary of array formulas as this could make the workbook rather slow with so many rows such as an INDEX, MATCH, SMALL combo.

Any ideas?

Regards
Tripp

 

[Khalid NGO]

Hi,

Can be done via PQ, please share sample file.

Please note: it will require refresh every time the data is changed.

Regards,

 

[Logit]

This macro looks at Column Q. If the cell in the column is not blank, it copies the entire row and pastes to the search results sheet.

You can change the column Q to whatever column you are using for dates.
You can also edit the sheet names to match.
The macro is run from a Command Button ... that way the sheet your colleagues are viewing won't be changing everytime you type a letter or number.

Option Explicit

Sub CopyYes()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
 
    Dim myString As String

    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet2")
    Set Target = ActiveWorkbook.Worksheets("Search Results")
 
    j = 2    ' Start copying to row 1 in target sheet
    For Each c In Source.Range("Q1:Q5000")  ' Do 5000 rows
        If c <> "" Then  ' Then
          Source.Rows(c.Row).Copy Target.Rows(j)
          j = j + 1
        End If
    Next c
End Sub

 

[Tripp]

Thanks,

The client has requested that macros not be used so the power query option would be good. I will investigate this further.

 

[AliGW]

The attached is done in two steps with the following M code in PQ:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date received", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date received] = #date(2018, 10, 26)))
in
    #"Filtered Rows"

 

[Tripp]

After experimenting with PQ, it works really well so thank you for the suggestion. I am currently using the 'Existing Connection' function to link a new table with the master one and can remove certain rows and apply the filter.

What I would like to do next is remove the ability for someone to turn off the filter but ill see what I can find.

 

[Tripp]

Sorry Ali, I missed your message this looks like the ticket!

 

[Tripp]

I am having an issue that even though all my data sources are within the same workbook, MS is throwing lots of warning signs saying the PQ is using external data sources... is there a way to block/ turn off these sources to prevent these warning messages?

 

[AliGW]

Is that because you are working with the workbook I posted? Ideally you will build the PQ query yourself from your original sample file.

 

[Tripp]

I built the PQ from scratch so there are no external sources of data coming in. This is why I'm confused as to all the warning messages.

 

[AliGW]

Then I am afraid I can't help - sorry. No idea what is going on.

 

[Tripp]

It looks like refreshing the data for a PQ on excel online is not possible...

 

[AliGW]

I wouldn't know - I don't use it in Excel online, only on the desktop. That's a bit of a bummer!