I have done this for clients. Here is a formula that uses a list of sheets
from sheet LU col F.
=SUMPRODUCT(SUMIF(INDIRECT(OFFSET(LU!F1,,,COUNTA(L U!F:F))&"!B1"),"PMI",INDIRECT(OFFSET(LU!F1,,,COUNT A(LU!F:F))&"!A3")))
It can also be done using a defined name for the sheets.
=SUMPRODUCT(SUMIF(INDIRECT(ms&"!$a1:$z1"),"ttl",IN DIRECT(ms&"!a"&ROW(A2)&":z"&ROW(A2)&"")))
or you can use macros
-
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TriciaZ" wrote in message
...
The data is from a spreadsheet created in Excel. I am helping someone
manipulate their data. The data contains information from 7 different
states
and many many counties within those states. The format of the 10
worksheets
is the same, yes. The end user needs the data in Excel in a specific
arrangement in order to send it to another application for analysis. I do
not know if Access would make this process any easier, in fact, the more I
think about it perhaps not. I do not know if the end user regularly
manipulates this data or not.

Basically, I have 10 spreadsheets full of data all set up like this:

State County Tons Commodity
IA ADAMS 143.97 AMMONIUM NITRATE
NE ADAMS 97 AMMONIUM NITRATE
OK ALFALFA 78.08 AMMONIUM NITRATE
OK ALFALFA 101 AMMONIUM NITRATE
IA ALLAMAKEE 72.88 AMMONIUM NITRATE
IA ALLAMAKEE 109.25 AMMONIUM NITRATE
KS ALLEN 1014.69 AMMONIUM NITRATE
LA ALLEN 118.78 AMMONIUM NITRATE


The sheet needs to read:
State County Tons Commodity
IA ADAMS 143.97 AMMONIUM NITRATE
NE ADAMS 97 AMMONIUM NITRATE
OK ALFALFA 179.08 AMMONIUM NITRATE
IA ALLAMAKEE 182.13 AMMONIUM NITRATE
KS ALLEN 1014.69 AMMONIUM NITRATE
LA ALLEN 118.78 AMMONIUM NITRATE

Hopefully this is a better explanation.
This is VERY simplified, but this is what I'm trying to help him do.
Thanks! I initially thought maybe a nested function - VLOOKUP and IF, or
even a visual basic program. I don't know what to do. Thanks for
helping.

--
taz0923


"tompl" wrote:

Well, you have not provided much detail with which I can be more
specific.
Where does the data come from originally? Why is it in Excel.? Why is
it on
ten separate worksheets? Is the format of the ten worksheets the same?
Why
do you want to put it back into Excel when you mentioned something about
sending it to another application? Maybe you could skip Excel altogether.
Keep the data in a table in Access, set up the query, then export the
data in
a format that can be used by your other application. Do you regularly
append
data to the existing? Do you get a completely new set of data
periodically?
So many questions!

Tom