Create a Search table

[peterderrington]

Hi,
I'm wanting to create a Frontpage dashboard element in my Excel file. I have the 'raw data' on one of the sheets but what i would like is the ability to search and display results from that sheet onto the 'front page'. Eg have a drop down box on the front page that allows me to pick Team Leaders and then show me those peoples workload, the raw data has a column with that persons name in. The table on the front page would be dynamic to only show that person (or object) selected, or have a searchable box where i could input the ID number of a specific project and it would pull that data through.
Ideally i would like to be able to alter information on this front page and have it write it back to the original data sheet.
Any suggestions?

[torachan]

lot easier to give a satisfactory solution if we have the workbook to work with.
see big yellow banner - how to upload your workbook - remember to de-sensitise your data.
torachan.

[peterderrington]

So i can't upload a copy but what i can do is use some screen shots to show what i'm trying to do....

So for example i'd like to click on "Rons projects" and for the area in the red circle:

highlited Dashboard example.jpg

to populate from this sheet, in this instance by his initials "RT" (Where the "RT" is in Column D of Sheet "Current")

data.jpg

[torachan]

Pictures are impossible to manipulate - a workbook is worth a thousand pictures.
Nobody is going to take time constructing your workbook when it already exists.
If the data is sensitive then de-sensitise it with as many 'Mickey Mouses' as required.
It is the structure and method of data store/retrieval that is required, I can only emphasise, pictures are of no use to me to give you a solution.
torachan.

[peterderrington]

I have mocked up a version of the dashboard.

Thanks.

[torachan]

Attached a userform front end for filtering and showing some status conditions.
The three comboboxes can be used singly or combined on the filter.
There are no on-sheet formula to maintain - your main data table will expand/contract dynamically as you add/delete data.
The defaults for the comboboxes are obtained from your data table - eliminates the need for seperate data validations for searches.
torachan.

[peterderrington]

Thanks for this that's great.
Is there anyway of being able to embed that pop up box "Data Filter" within a set of cells or similar? Just from a design / end user perspective I can see my end users getting confused lol

Also is it possible to only pull through certain columns, so for example rather than the full A:P maybe something like A,B,C,D,E,O,P? (that's just off the top of my head - just to see if its possible)

Thank you!

[torachan]

1) Defeats the flexability of the UserForm by putting anything on the sheet in cells - coding becomes very messy - not my approach - the form can be visible and placed wherever.
2) Name the columns - simple to do with present approach.
torachan.