Mimic the "double-click functionality" of pivot tables without pivot

Hey all, I'm trying to get something similar to the "create table upon double-clicking a cell" functionality of a pivot table without actually using a pivot table.

I am creating a table of key figures (my "results" sheet) for a large set of data, and I have to do it without pivot. I currently maintain a duplicate of this table in form of a cluster of pivot tables because I need the user to be able to quickly get the entries in my database that create any given key figure in my results.

Because of some quirks of pivot tables (which are the reason I'm not using them as my main method of gathering and presenting those key figures) I need to manually adjust them quite often. This isn't going to work for me in the long term, so I need to find another way to get the desired result.

Now, ideally I'd be able to just click on a cell in my "results" sheet and jump to my "database" sheet where the filters are set automatically to show only the entries of my db on which the result is based. If possible, I'd like to do so without VBA.

I sincerely hope that someone will be able to help me with this, as I have to present the result in an important meeting next Monday

Thanks in advance & please don't hesitate to ask if you need further information!

Nick

BUMP

Please, guys, ANY HELP or even just a hint at a possible solution will do. If I don't get this done in time I'm in serious trouble

Originally Posted by royUK

What quirks of pivottables?

For starters, pivot tables aren't "clean". They've got the filters above them, and they cannot be freely manipulated.

To get an idea of what my desired "results" sheet looks like, please have a look at this (simplified) version of it.
FYI, I chose to provide a picture because the important thing here is the layout.

Specifically, these are the quirks I'm referring to:

- I need about 60 of them side-by-side because the results I'm trying to get are the result of different report filters.

- I cannot (or don't know how to) get each pivot table to show the same row labels, because some tables don't get results for every possible row label and those labels are therefor not shown. This makes it impossible to hide all but the first table's labels in order to get the same seamless look I get without pivot (pic above).

- I'm working with templates. The blank template has a completely blank "database" sheet (except for the column headers) into which the data is pasted manually. Since all my report filters rely on the content of those columns, the filters get messed up (i.e. "forget" what I selected) if I save the template without "dummy data" in the database. Thus, after pasting new data in the db I have to correct half the pivot tables' report filters, which is exactly why I want to get rid of them.

- The pivot tables tend to cause problems with my conditional formatting.

- Pivot tables need to be updated. If I use plain formulas, they're always "up-to-date".

@Andy Pope: Thank you for trying to help. The only problem I've got with your solution is that I'm not allowed to use macros. I realize that I won't be able to replicate the double-click stuff without VBA, but I hope to get somewhere near that.

Thank you guys!