OK, I'm slowly and painfully working my way through this!! One thing I would
like to do is have one cell in each row on the year sheet sum a column on the
related project sheet, if the transaction occurred in a given year. Rather
than entering the sheet name in the function I would like to refer to the
contents of a cell (Column A). So:

A ............ D

1 Project Commit
2 00387 nnnnn

Where 00387 is the project number and nnnnn is the sum of a column on
worksheet '00387'. So, basically, I need something like:

=SUMIF('(contents of A3)'!E8:E71,"AND(<1/1/2006,31/12/2004)",'(contents of
A3)'!D8:D71)

But something that works!

I've checked Help, and cannot figure out how to use the contents of A2 as a
reference to a sheet name. Any thoughts?

TIA.

"ShaneDevenshire" wrote:

Hi,

The solution depends on a lot of things, but here is a starting point:

=IF(2007!$S2<"Closed",2007!A2,"")

You could enter a formula like this in cell A2 of the 2008 sheet and copy it
to the right to column S and then down as far as necessary.

The problem with this technique is that Closed items will return blank rows
to the 2008 sheet. You did not make it clear if you want formulas of just
the values, but if you just want the values you can copy and paste all the
formulas on the 2008 sheet as values and then sort the data to get all the
blanks in one place.

If you want values only you can also use the Data, Auto Filter command.
Filter with the column S filter being Custom, Not Equal and the criteria
Closed.
Once filtered highlight the range and copy it, move to the 2008 sheet and
paste.

--
Cheers,
Shane Devenshire


"LongTermNoob" wrote:

I have a workbook containing many sheets. The first sheets are years ("2005",
"2006", "2007", "2008") followed by sheets for each individual project
("00387"..."00798"). The year sheets look up information from the project
sheets and this has not been a problem.

What I would like to do is have each year sheet look at the rows in the
previous year and if the final colum "S" is not equal to "Closed" then copy
the row into themselves.

I know the Lookup functions need to look at the firrst column. Any hellp
would be gratefully received!