On 2/18/2018 5:06 PM, GS wrote:
OK, this is a perfect task for SUMIF() because fund names are duplicated in
the various pools, but will be totalled in a single record on your
'consolidation' sheet. The *key* to making this possible is to use a
'structured' template for the consolidation sheet, and VBA to auto-populate
it with the appropriate data.

This is not a daunting task for most Excel programmers since this is a very
common type of task. The repetative nature of the task sets the prerequiste
for using a structured template designed to meet your 'report' needs, which
can be inserted into any workbook where needed. The easy part is that your
consolidation sheet (I call it "Summary" in my projects) only has to pull
data from 1 sheet (I assume), not pull from several sheets.

Perhaps we could collaborate on this project so you get started in this
concept with some measure of comfort?

I sorta' grok the idea; SUMIF() didn't precisely come to mind other than for
the numerics it certainly can compute the desired totals...I was trying to
figure out there was surely some way to merge with data tables or the like;
seemed like precisely what something like the CONSOLIDATE engine out to be
built to do...

What is your idea of how to "collaborate" here?

I would need a workbook from you with sample (dummy) of every conceivable type
of input (source) data the project may encounter on 1 sheet, and a sample
consolidate sheet to build the Summary template. From there I'll mock up an app
and send it for your review/feedback; -this will be a back&forth situation and
so you may want to use email for direct exchange rather than posting numerous
download links here.

I've moaned about learning the VBA syntax for all the stinkin' Excel objects
before...it's trivial to pull the data out and use a real programming
language on it and put it back, but that only works while I'm around to do
it, unfortunately.

If this is a repetitive, dedicated task then we can automate it so VBA does all
the work via menus the user interacts with. IOW, an Excel-based application
that runs as an Addin.

The 'summary' template will contain formulas as well as constants, all inserted
with VBA.

FYI:
Visual Basic IS a real programming language, BTW! Apps that support VBA merely
exposed their ObjectModel to it. Macro is just another name for program
procedures, but VBA programs support all that VB includes. The last version of
VB was 6.0, and is what VBA was/is based on up to MS Office2007. As of MSO2010,
VBA7 is used in both 32bit and 64bit editions.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion