like a pivot but not a pivot

with Pivots I know you can click on a cell and it will bring back the data, be it a single item or if you click on a total it will bring back all the items that make up that total.

My question is, is it possible to do the same thing but without using a pivot.

so lets say I have a hidden file with all my data and I have built a table that has been grouped with totals. [in the same way as a pivot]

now lets say I click on one of the amount. I want it to bring back the items that make up that amount and put the details on a new sheet.

Possible or not.
Like-a-pivot-1-.xlsm
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Jagwarman,

Unfortunately if you click the amount and want to get the totals of it..That will be too complicated to get and not perfect all the times..I will recommend taking the pivot table approach only as thats the best way to get to the show details of your data what you are looking for...

Saurabh...
ProfessorJimJamMicrosoft Excel ExpertCommented:
it is possible to do it, but requires lots of coding and pre-setup of data and hyperlinks. but like Saurabh mentioned, it is easy to use pivot table rather than the vba overkill
JagwarmanAuthor Commented:
Hmm! I've seen it done by someone but unfortunately I don't have access to code and I don't know who that someone was.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Saurabh Singh TeotiaCommented:
Jagwarman,

You can do this but this will be done on the the filter you want to bill and getting the details on that filter..rather then the amount..If you are interested i can build up logic that if you enter date it will get all yours date transaction for that date...

Saurabh...
JagwarmanAuthor Commented:
Hi Saurabh from what you guys have said it sounds like it could be a lot of work and I don't want you to spend loads of time on it if it's too difficult.
Saurabh Singh TeotiaCommented:
Nah if you want to do the date logic it's not too much work...However if you want to do it on the sum account that you are getting that will be tricky one..

Quick thing..Can you give me your sample data on which you have made this pivot table let me design a solution for you here..
[ fanpages ]IT Services ConsultantCommented:
Hi Jagwarman,

I don't think what you want would take a great deal of coding effort (given previous questions I have answered of a similar nature), but I have two(-ish) queries:

1) Is the PivotTable (worksheet) you have hidden necessary, or was this just to demonstrate what you wanted to achieve?

2) Why do you want to use this approach (rather than a PivotTable)?  Is this just for the sake of it (to prove it can be done), or because of other reasons/restrictions?

If this is part of a larger project, I want to be sure that what I provide is not going to require constant, or ongoing, maintenance & if only I am prepared to assist you now, I may be producing something for you that nobody else (or very few others) are then prepared to assist you with in the future (should I not be available).
JagwarmanAuthor Commented:
Hi Saurabh  the sample data is in the hidden sheets

Regards
JagwarmanAuthor Commented:
Hi Fanpages

1) Is the PivotTable (worksheet) you have hidden necessary, or was this just to demonstrate what you wanted to achieve?............ just to demonstrate what I wanted to achieve

 2) Why do you want to use this approach (rather than a PivotTable)?  Is this just for the sake of it (to prove it can be done), or because of other reasons/restrictions?............ My users have asked me to do this for them. They tell me they often have problems with pivots.

I take your [last] point but if it can be done would be great and I will certainly learn to support it
Saurabh Singh TeotiaCommented:
@ Fanpages...

Not sure where you want to go with this comment of urs...

hat what I provide is not going to require constant, or ongoing, maintenance & if only I am prepared to assist you now, I may be producing something for you that nobody else (or very few others) are then prepared to assist you with in the future (should I not be available).

Because the point out here if he want's a similar show details feature without pivot that itself is complex assuming he just have figure to look at it..another alternative will be which i'm working on him is to give him data for date which he supplied to get the data for..

@Jagwarman.. Will you have pivot always their or it's here in the hidden worksheet but in actual scenario will it be always their??
[ fanpages ]IT Services ConsultantCommented:
Hi again,

Thanks for your prompt reply.

If Saurabh wishes to tackle this, that is fine, by all means; the more suggestions/potential solutions available to you, the better.

I probably have a majority of the code pre-written in earlier questions & personal projects; I just need to apply the bespoke requirements; primarily, the Worksheet_Change() event code that will trigger the delivery of the results.

I may not be able to look at this further until (much) later today or, possibly, over the coming weekend, though.

Is it OK, if I come back to you by Monday, or do you have a need for this today?
Saurabh Singh TeotiaCommented:
@Jagwarman

I'm assuming this is what you are looking for i took your data as a reference point and build the code around the date..so now when you double click any calculation i.e. in C Column..I will show you the data basis of Date in front of it..plus i have used the layout of your data to pick up whether you want to pick for our receipt or their receipt

In additional every-time you double click on that range it will create a new sheet automatically.. Just wanted to empathize on one point again this is build on the date logic which i was talking earlier...

I will be interested to see myself by @fanpages that how he builds the logic on the calculation and not on date what originally i was saying ...
not-a-pivot-1.xlsm
JagwarmanAuthor Commented:
Hi Fanpages, Monday or any day next week would be fine... Thanks
JagwarmanAuthor Commented:
Saurabh, thanks for this. I agree will be great to see both.

Have a great weekend.
JagwarmanAuthor Commented:
Fanpages meant to say have a great weekend
[ fanpages ]IT Services ConsultantCommented:
Thanks.

Fast work Saurabh! :)

If you wish to accept this proposal as the "answer", Jagwarman, if it meets your requirements, I don't mind at all.

I will just post again with a different 'solution' next week for information (for you both).
ProfessorJimJamMicrosoft Excel ExpertCommented:
I like this conversation :-D
JagwarmanAuthor Commented:
Fanpages/Saurabh Hope you both had a good weekend.

Thanks Saurabh that works great with the date format. I have had a chance this morning to look at the other formats we use and I am attaching a sample file. Don't know if the macro can be amended to take into account this format [probably not]

Thanks for your help
Like-a-pivot.xlsm
Saurabh Singh TeotiaCommented:
Their you go i believe this is what you were looking for now it does pull out the data basis of where you will double click.. In additional it will pull out data of total as well, Leaving the grand total...

Saurabh...
Like-a-pivot.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Saurabh many thanks for the work you put into this very much appreciated.
[ fanpages ]IT Services ConsultantCommented:
I haven't forgotten about you! :)

I just have been busy working on something else (that has a specific deadline tomorrow).

Please close this thread & pass the points to Saurabh due to the effort involved to date.

Once I have the chance, I will post my suggestion using another method to tackle the same issue, just for information/interest.
JagwarmanAuthor Commented:
ok thanks Fanpages
Saurabh Singh TeotiaCommented:
Jagwarman

You are welcome..Always happy to help..

Saurabh...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.