LinkBack URL
About LinkBacks
Hello experts,
i have a complicated report as attached. my struggle is to automate the name list due to the changes/updates in the feeding data query and a bunch of other criteria.
Please help.
thanks.
How to make name list dynamic
Hello experts,
i have a complicated report as attached. my struggle is to automate the name list due to the changes/updates in the feeding data query and a bunch of other criteria.
Please help.
thanks.
Last edited by weiwei; 09-07-2010 at 04:05 PM.
There are some hefty array formulas, but I'm not a big fan of them. By simply adding a "key" column you can create a quick index of each row that fits your criteria. I used column H. You can hide this column if you wish. The formulas in I and J do their work off of that index in column H.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Welcome to the forum!
This looks like a Pivot Table might help out, though aggregating total for projects that are less than $1000 may be a little harder. See the attached. I am using Excel 2003, so I had to back convert the file.
Make%20List%20Dynamic(1).xls
Bob
Click my star if my answer helped you. Mark the thread as [SOLVED] if it has been.
I missed the GreenSmall requirement cell. I've added that in this updated version.
Hello Jerry,
thanks for your quick response.
the formulas in the project name list is very cool. but there is another problem unsolved:
i want the project spendings bigger than 1000 to be displayed, but those project spending records appear multiple times, so it is the sum of all the "AA" projects to be the total spendings i am looking at instead of the spending in each single record.
could you advise on this one? thank you very much.
Thanks Bob, but when i feed the names into a structured report, i don't know a good way to make it dynamic and auto-update when the query changes.
but thanks again.
Can you demonstrate exactly what you mean? Did my second workbook get the correct project names? What totals would you expect instead? Add them in column K so I can compare.
Hello Jerry,
please see attached file.
this time, i changed one record for AA, made every single spending record of AA is smaller than 1000, but the total spending of AA is larger than 1000 ( it needs to be listed to the report individually but the current logic was unable to pick it up).
Hmm.... so you want to sum ALL the AA rows together regardless of their column F values, and for the ones that have a grand total above 1000, then display the lines that have a match in column F, too?
Try this in H7 and copied down...
=IF(AND(F7=$J$6, SUMIF($C$7:$C$24, $C7, $G$7:$G$24)>1000, COUNTIF($A$7:$A7,$A7)=1), $H6+1, $H6)
Hi Jerry,
It works!
i used this :
=IF(AND(F7=$L$6, SUMIF($A$7:$A$24, $A7, $G$7:$G$24)>1000, COUNTIF($A$7:$A7,$A7)=1), $I6+1, $I6)
in cell I2,
Thank you so much!
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks