L
LongTermNoob
I posted before with a problem which was answered and then tacked this onto
it, but I think it's worth starting another thread as it has gone unanswered
(probably already consigned to history by most!).
I have a workbook for all our capital projects. I have simplified it to
include one "summary" sheet and a separate sheet for each project, where all
information is stored in columnar format (e.g. a column for "committed", a
column for "Invoiced", etc.). This information may be spread over several
months or several years, and I need to get information from individual years
into separate cells on the summary sheet. The project sheets are named by
Project number, which is also the value in the first column of the Summary
sheet
So the summary sheet looks something like:
A ... D
1 Proj # Commit '05
2 00689 50000.00
3 00696 32500.00
and the project sheet (e.g. 00689) something like:
A B C D
1 Order Date Order Value Invoice Amount Invoice Date
2 12-Dec-04 15000.00 15000.00 25-Jan-05
3 15-Mar-05 25000.00 25000.00 01-May-05
4 21-Apr-05 25000.00 15000.00 15-Jan-06
So, I need to sum the values on the project sheet in Column B where Column A
is in 2005 in order to get my Commit for 2005 and put it into Column D of the
Summary sheet. The date column does not always precede the data I need to sum
(e.g. for Invoices) so Lookups are not an option as far as I understand it.
I would like to use the values in column A in the Summary sheet to point to
the worksheet where the information is as entering all the Project numbers
would take half a life-time. The following does not work, but may give you a
better idea of what I am trying to do:
=SUMIF(INDIRECT("'"&A2&"'!A8:A71"),"*05",INDIRECT("'"&A2&"'!B8:B71"))
The above returns 0, event though there are orders placed in nn-xxx-05.
Any assistance would be gratefully received.
it, but I think it's worth starting another thread as it has gone unanswered
(probably already consigned to history by most!).
I have a workbook for all our capital projects. I have simplified it to
include one "summary" sheet and a separate sheet for each project, where all
information is stored in columnar format (e.g. a column for "committed", a
column for "Invoiced", etc.). This information may be spread over several
months or several years, and I need to get information from individual years
into separate cells on the summary sheet. The project sheets are named by
Project number, which is also the value in the first column of the Summary
sheet
So the summary sheet looks something like:
A ... D
1 Proj # Commit '05
2 00689 50000.00
3 00696 32500.00
and the project sheet (e.g. 00689) something like:
A B C D
1 Order Date Order Value Invoice Amount Invoice Date
2 12-Dec-04 15000.00 15000.00 25-Jan-05
3 15-Mar-05 25000.00 25000.00 01-May-05
4 21-Apr-05 25000.00 15000.00 15-Jan-06
So, I need to sum the values on the project sheet in Column B where Column A
is in 2005 in order to get my Commit for 2005 and put it into Column D of the
Summary sheet. The date column does not always precede the data I need to sum
(e.g. for Invoices) so Lookups are not an option as far as I understand it.
I would like to use the values in column A in the Summary sheet to point to
the worksheet where the information is as entering all the Project numbers
would take half a life-time. The following does not work, but may give you a
better idea of what I am trying to do:
=SUMIF(INDIRECT("'"&A2&"'!A8:A71"),"*05",INDIRECT("'"&A2&"'!B8:B71"))
The above returns 0, event though there are orders placed in nn-xxx-05.
Any assistance would be gratefully received.