Excel Forum

Dashboard with General text filter?

Luke Ellis

New Member

Members

Forum Posts: 2

Member Since:
January 31, 2021

Offline

January 31, 2021 - 12:14 am

Hi All,

I am trying to develop a dashboard that display specific data to the particular client.

I have watched various videos and most show gathering empirical data and not generalised text.

Attached is the example document. I would like to be able to select a client fromt the dropdown menu in the dashboard. This then collects the collerating text from the running sheet. I have setup basic functions with (write a name in the running sheet and it automatically populate ID from the client list).

Could someone please provide a link to learn how to do the above or load the fucntions required.

I am hoping to use office 365 excel which is limited in it capabilites (doesn't sort macros).

Recently. tried using the new filter function however if the name is entered and there are no values it populates with a "0" on the dashboard.
Using 365 is not essential however is desirable.

Hope you can help 🙂

See Answer

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 956

Member Since:
December 7, 2016

Offline

January 31, 2021 - 8:39 pm

Hello,

Attached is a simple way to do it by using a helper column to get a unique identifier per row. Then you can use VLOOKUP (or any other lookup functions / methods you like) to get the data. In order to hide any zeros (due to empty cells) I did a custom formatting on the text cells.

Other approaches are to use Power Query or VBA.

Br,
Anders

Answers Post

Mynda Treacy

Admin

Forum Posts: 4782

Member Since:
July 16, 2010

Offline

February 1, 2021 - 1:24 pm

Hi Luke,

I'd use a PivotTable if I were you. Please see attached.

Mynda

Luke Ellis

New Member

Members

Forum Posts: 2

Member Since:
January 31, 2021

Offline

February 1, 2021 - 11:39 pm

Thanks for both of your responses.

I am having two problems.

1. When adding new data to the running sheet, it is recognised within the table however is not being displayed when I select the name. This is for both solutions.

2. When attempting to learn to develop a PivotTable I am unable to re-arrange it into a nice table like you were able to do Mynda.

I have attached a screenshot of my pivot table.

Do you have any more advice or any specific YouTube videos I could watch? I have watch some of your powerpivot videos however they are often for charts and empirical data.

Thanks for your time and I look forward to hearing your response.

Luke

Edit: I firgured it out,

1. Right click -> field settings -> change it to none and show as tabular form

2. Click the refresh button under PivotTable Analyze