Solved

Using a dropdown to choose a group of account for totalling

Posted on 2015-01-27
12
50 Views
Last Modified: 2015-01-28
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
Comment
Question by:Patrick O'Dea
  • 6
  • 5
12 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40573887
Is a macro solution acceptable?
0
 

Author Comment

by:Patrick O'Dea
ID: 40573899
Martin,

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

Regards,
Padraig
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40573911
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
 

Author Comment

by:Patrick O'Dea
ID: 40573948
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40573951
OK be back soon.
0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 40574128
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 250 total points
ID: 40575807
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
 

Author Comment

by:Patrick O'Dea
ID: 40576062
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40576102
No, just select a value in K2.
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 40576174
Thanks folks,
two excellent solutions.

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

Thanks again.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40576237
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
 

Author Comment

by:Patrick O'Dea
ID: 40576271
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now