Using filters on a scroll table

[Qhawe]

Good day all,

I am a new member and I go by the name of Qhawe.

I have created a scroll table in which I would like to be able to filter the data on it without going to the table where the original data table is. If my question is not clear please see the Excel file I have uploaded, it should make my question clear.

Thank you
Qhawe

 

[blazy]

Hey Qhawe,

not sure if I understood correctly... Why not just appy filter on Scroll table sheet? Click on the headers -> Data -> Filter

Or am I missing something?

KR

 

[Qhawe]

Thanks KR,
Yes you are correct I could do that but then the scroll table will not show the the 6 entries at a time that I would like for it to show nor will it be able to calculate the subtotal that I want from the table. The actual table that I am working on also takes an average by region or location (using the subtotal formula and the subtotal formula references the data from the raw data sheet). Maybe my question on should be how do I filter a table from a different worksheet...if that makes any sense? Again I have attached my spreadsheet now with the subtotal cell I hope this will make more sense.

Qhawe

 

[Nebu]

Hi:

Would a pivot do this for you??

Thanks

 

[NARAYANK991]

Hi Qhawe ,

It's an interesting application , but I think doing it using formulae is not possible , since the formulae in the Scroll table tab are referring to the positional values within the data table in the Raw data tab.

When you apply a filter on the displayed table ( in the Scroll table tab ) , if you want the original data ( in the Raw data tab ) to be filtered , that can be done using VBA , but the problem is , the formulae are referring to cells by their address , irrespective of whether the cells are visible or not ; thus even if the original data is filtered , the formula will still display unfiltered data ; thus , the only possibility is to use VBA for everything , viz.

Display 6 results from the original data

Operate the scroll bar using VBA , so that as it is operated , the displayed 6 results change , as if the user were scrolling through the original data

Allow the user to filter the original data , using the AutoFilter buttons

All of the above would add up to a lot of code , but it can be done.

Narayan

 

[Qhawe]

Thank you guys for your replies. Narayan, this is what I feared the most, I have been avoiding VBA forever but I guess I will have to start learning if I want to create cool dashboards, once again thank you.

Qhawe