[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • 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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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