You have hit the nail on the head in both instances. Our accounting dept.
had decided that Excel is what we would use and they set up this layout.
It's up to us to make it work as best we can. I'm just trying to make it
easier for the guys to use. The layout you have "laid out" here is exactly
the way we do it. I understand what your saying about summing "up" to a
blank. My problem is that none of us have any training on Excel. I've been
reading about and writing some of the macros we are now using but I'm lost
as how to detect a black cell in order to determine the amount of lines to
be summed. How would I do this?
Thanks again.
RJH
"COM" wrote in message
...
Though a pivot table may seem great, and yeah perhaps all the data should
be grouped together on a single worksheet, the overall process is not broken
to the point of needing a complete revamp. Besides each of the other two
fixes would require writing more code and performing more searches and still
be limited, and ultimately not the way that the the customer is looking for.
And actually, it almost sounds like this should all be done in Access rather
than Excel, but once the company has committed to a particular means of
working, it can be difficult to change it unless required by someone of
higher authority/there's a big problem.
Here is an idea that can be done keeping your existing formatting.
If the "total" that you are looking for is always in the same position in
relation to the numbers being totalled then you can write some
VB code to
sum up the items in your "group"
As I understand you have groups of data like the following:
Company1 Item1 $4.51
Company1 Item2 $5.23
Company1 Item3 $20.63
Company2 Item1 $6.52
Company2 Item2 $8.02
Company2 Item3 $10.41
Company2 Item4 $11.06
And say you want to add the "Total" just below each of the company
groupings, then you know that all items to be totaled are located "above"
the cell used to hold the total. The total cell could be anywhere in the
row, but as long as you (the Programmer) knows where the last piece of data
to be summed starts, then you can do the necessary addition.
The routine you would want would simply start at the last item to be
summed, and work "up" until a blank space is found. The result then is the
sum of all items for that group.