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!

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!

Thanks Shane. It's the values that I need, and I was hoping to have it update
automatically, but maybe I'm looking for too much!!

I kept the explaination simple, otherwise it would take a huge post, but
basically I want to carry over budget information from open projects into the
following year.

I'll play around with your suggestions and see what works best - thanks
again for the help.


"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!

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!

Sorry, that should have read:

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


"LongTermNoob" wrote:

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!

Hi,

Let's start with the reference to a cell to find the sheet - for this you
need INDIRECT.

For example suppose your sheet name is in A3 part of your formula would read:

=INDIRECT(A3&"!E8:E71")

However, you won't be able to use any function in the first argument of the
SUMIF function so you will need to use another approach, for example
SUMPRODUCT

=SUMPRODUCT(INDIRECT(A3&"!E8:E71"),"AND(<1/1/2006,31/12/2004)",INDIRECT(A3&"!D8:D71"))

Now the date test - where are the date cells? This part could be a separate
condition such as YEAR(INDIRECT(A3&"!B8:B71")=2005

So the above formula becomes
=SUMPRODUCT(INDIRECT(A3&"!E8:E71")*(YEAR(INDIRECT( A3&"!B8:B71")=2005)*INDIRECT(A3&"!D8:D71"))

Play around with these ideas.
--
Cheers,
Shane Devenshire


"LongTermNoob" wrote:

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!