Hello. I'm trying to use Excel 2003 to output the following:
In the last sheet of http://www.natalievane.com/DAILYFLOWS.xls shows what I
want. (6 MB)

Essentially: I have about 40 sheets with each tab being a different month
and year since 2002. The sheets are the same format, with the same
information (water used) being in the same cell, like E45, in each sheet. I
am looking for a way to output the cells E45, E46, E47 (or whichever cell I
want) from each sheet into a summary sheet without manually clicking on each
of the 40 sheets to get it. I then need to run a linear regression on it to
project it into the future.

My goal is to get a table that says
SheetName | Cell E45 | Cell E46 | Cell E47
January 2007 | 31,220,000 | 123% | 38,434,458
... repeat for all tabs from Aug 2002 to Jan 2007


I tried: putting all the E45's into a variable name and then doing a sum of
that variable name, but that doesn't give me what I need to run my linear
regression. I can get an "average" but it doesn't error check me for when the
value doesn't exist, like on the TALLY tabs.

I also tried: putting in a concatenation formula where I put in the Name of
the Tab, the Cells I wanted to reference, and then concatenated that
together. After that, I pasted special the value and when I absoluted the
cells i.e. $C$3, $D$3 so it didn't shift cells when I pasted special, it
outputted the cell value, like 31,220,000. But that might be more tedious
than manually clicking on each cell in the separate worksheets.When I didn't
absolute the cell values and shifted the cell I was pasting into, the
concatenation value shifted the cells it was referencing so it no longer had
the correct Worksheet Name and failed to give me a value like
31,220,000.

Thank you. My name is Natalie Vane. If you have a solution, I'd love to
learn it. My email is and phone is 704-373-7132.

Hi Natalie

With this macro you can create a worksheet with formula links to the cells in every sheet
http://www.rondebruin.nl/summary.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Natalie" wrote in message ...
Hello. I'm trying to use Excel 2003 to output the following:
In the last sheet of http://www.natalievane.com/DAILYFLOWS.xls shows what I
want. (6 MB)

Essentially: I have about 40 sheets with each tab being a different month
and year since 2002. The sheets are the same format, with the same
information (water used) being in the same cell, like E45, in each sheet. I
am looking for a way to output the cells E45, E46, E47 (or whichever cell I
want) from each sheet into a summary sheet without manually clicking on each
of the 40 sheets to get it. I then need to run a linear regression on it to
project it into the future.

My goal is to get a table that says
SheetName | Cell E45 | Cell E46 | Cell E47
January 2007 | 31,220,000 | 123% | 38,434,458
.. repeat for all tabs from Aug 2002 to Jan 2007


I tried: putting all the E45's into a variable name and then doing a sum of
that variable name, but that doesn't give me what I need to run my linear
regression. I can get an "average" but it doesn't error check me for when the
value doesn't exist, like on the TALLY tabs.

I also tried: putting in a concatenation formula where I put in the Name of
the Tab, the Cells I wanted to reference, and then concatenated that
together. After that, I pasted special the value and when I absoluted the
cells i.e. $C$3, $D$3 so it didn't shift cells when I pasted special, it
outputted the cell value, like 31,220,000. But that might be more tedious
than manually clicking on each cell in the separate worksheets.When I didn't
absolute the cell values and shifted the cell I was pasting into, the
concatenation value shifted the cells it was referencing so it no longer had
the correct Worksheet Name and failed to give me a value like
31,220,000.

Thank you. My name is Natalie Vane. If you have a solution, I'd love to
learn it. My email is and phone is 704-373-7132.