Did you try suing multiple consolidation ranges when you set up your povot
table. doing that you can point at a number of sheet...
--
HTH...

Jim Thomlinson


"ryguy7272" wrote:

Right on Jim! Using a Pivot Table didn't even occur to me. I tried it with
Date (row) and summed the Total Jobs (data). This will almost do it (why is
it always almost...). However, some incidences of 6/1/2007 fall all over the
workbook. I think I have to copy/paste all data from all sheets (28, 30, 31,
depending on the month) in each workbook and create one summary sheet and
then build the PT there.

I looked at Ron de Bruin's site:
http://www.rondebruin.nl/tips.htm


He offers some AWESOME code for doing many things, but I didn't notice an
easy way to copy data from all sheets in a closed workbook (or open an book
for that matter) into a summary sheet in the active workbook. There is an
example of how to do it for one specific worksheet, but I didnt see a way to
copy/paste data from all sheets into a single summary sheet. Did I miss
something?




--
RyGuy


"Jim Thomlinson" wrote:

A few things... You could use a pivot tabel with multiple consolidation
ranges to do what you want. All other solutions are pretty much going to
require you to have the summary sheet (to be effective and manageable). If
you have the summary sheet then you could use sumproduct formulas... Here are
some links for you to look at...

http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"ryguy7272" wrote:

Hello all! I am trying to figure out the very simplest way to do something
very complicated. I am looking for a way to sum up all the dollar amounts
that meet two criteria. For instance, I want to sum all the expenses on
6/1/2007, that fell into category LM, and sum all expenses on 6/1/2007, that
fell into category BD, etc. All together, there are 5 categories (BD, VISIT,
LD1, LM, ADJ, & LD2). This is tricky because it requires a double lookup.
Vlookup doesnt seem to be able to handle the demand. I tried Index/Match
and didnt get the results I was looking for. Can someone offer a better
suggestion as to how to do this? I am ok with VBA, but still learning and I
dont feel like I know where to start with this thing. I think it will
require a VBA solution. Finally, the data is stored on multiple sheets,
titled 6-1, 6-2, 6-3 (days in June). Is there a way to find every incidence
of 6/1/2007 in the workbook, then sum the dollar figures that correspond to
LM, BD, etc? I was thinking of taking all values on all sheets, copying them
to one summary sheet, and then working only with that summary sheet for the
lookup, index/match, find, whatever. Does this make sense? I am open to
literally anything. The workbook that I inherited doesnt work at all so I
am trying to divorce myself from it and start over anyway.

Regards,
Ryan---

--
RyGuy