• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

Using a dropdown to choose a group of account for totalling

Hi,

This might stretch the power of excel.
The 1-minute video explains my requirement.

http://screencast.com/t/k32zhfRPzN

Note that I cannot hard code the solution. It must be flexible.
Preferably , no VBA.

Thanks!
EE-Map1.xlsx
0
Patrick O'Dea
Asked:
Patrick O'Dea
  • 6
  • 5
2 Solutions
 
Martin LissOlder than dirtCommented:
Is a macro solution acceptable?
0
 
Patrick O'DeaAuthor Commented:
Martin,

My preference would be no macro - but I would happily except one.
Hopefully my video is clear.

Regards,
Padraig
0
 
Martin LissOlder than dirtCommented:
It is, but I do have some questions.

You say that there could be more then just "Wages" and "Rent" in the dropdown. where will the user parameters for the others be?
You also say that columns A to D are fixed data out of your control. How does it get there and what if anything in it might change?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Patrick O'DeaAuthor Commented:
Martin,

Good questions.
Perhaps , at this point, we will just keep the macro simple.
Let's not worry about making it flexible.  I might learn enough from the code in the macro.

THanks,
0
 
Martin LissOlder than dirtCommented:
OK be back soon.
0
 
Martin LissOlder than dirtCommented:
Try this. The range used to find the data (currently G and H) is based on K2's data validation formula, so it should be able to cope with addition of more columns.
Q-28605052.xlsm
0
 
Ejgil HedegaardCommented:
This formula sum the values, based on the validation selection

=SUMPRODUCT((ISERROR(MATCH(A2:A6,INDEX(G2:H4,,MATCH(K2,G1:H1,0)),0))=FALSE)*D2:D6)
Sum-validation.xlsx
0
 
Patrick O'DeaAuthor Commented:
Martin,

I am unsure what I should do with the sheet you provided.
Is there a macro I should run??
THanks again for your help.

Padraig
0
 
Martin LissOlder than dirtCommented:
No, just select a value in K2.
0
 
Patrick O'DeaAuthor Commented:
Thanks folks,
two excellent solutions.

I will now study the logic provided.   (THis could be interesting!)

Thanks again.
0
 
Martin LissOlder than dirtCommented:
My code contains some comments that I hope explain what's going on. The macro is triggered by a change in cell K2 (see the worksheet_change event). I'll be happy to answer any questions you might have. In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
 
Patrick O'DeaAuthor Commented:
Thanks Marty,

Much appreciated.
I am studying the code now.  (All very neat!).
I think I understand it all.

Thanks for being so helpful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now