Cell reference

[pvr928]

I have two worksheets.

On one worksheet, information is displayed on a monthly basis out for a number of years.

On the second worksheet, which is designed to be a summary of the infromation on the first worksheet, I want to display the information on an annual basis.

What I want to do on the summary worksheet is to sum the yearly information in the first worksheet, by matching it to the specific year on the summary worksheet.

The formula will therefore match the year end date on the summary worksheet in the annual (first worksheet), and use that cell in the annual worksheet as a reference to sum the annual information.

I think the formula will go along the lines of:

=SUM(OFFSET((MATCH([MONTH IN SUMMARY WORKSHEET],[NAMED RANGE IN ANNUAL WORKSHEET]),11,-12,1,12)

but I keep getting an error.

In simple terms, the month in the summary worksheet will be matched with the same month in the annual worksheet (reference cell), and then the figures in the row below the reference cell and in the 11 columns left of the reference cell (including the column in which the reference cell is situated) (1x12 range) will be aggregated.

Any ideas how to make it work?

Cheers

pvr928

 

[Brian from Maui]

This will sum on a yearly basis,

=SUMPRODUCT(--(YEAR(RANGE)=2003),RANGE TO SUM)

or post some sample data and expected results

 

[pvr928]

Brian

Thanks for the reply

Sample data in the unsummarised worksheet is:

Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05


478,359 478,359 462,928 478,359 462,928 478,359 478,359 432,066 478,359 462,928 629,713 609,400


So I want to use Jun-05 as the reference and then aggregate the cells in the row below it and across 11 columns to the left of it, including the cell in the same column as the reference.

How does this fit in with what you suggested?

Cheers

pvr928

 

[pvr928]

I think an alternative approach is to create a cell reference using MATCH.

I am currently using =MATCH([MONTH NAME],TABLE) (TABLE is all of the months from Jul-04 to Jun-13, 1 row deep). This formula produces 12 - which is the number of the column in which Jun-05 is located.

How can I convert that to a cell reference (ie the actual reference of that cell?)? I have tried using =CELL("address",[and then the above MATCH formula]), but this returns an error.

Any ideas?

Cheers

pvr928

 

[Brian from Maui]

I think an alternative approach is to create a cell reference using MATCH.

I am currently using =MATCH([MONTH NAME],TABLE) (TABLE is all of the months from Jul-04 to Jun-13, 1 row deep). This formula produces 12 - which is the number of the column in which Jun-05 is located.

How can I convert that to a cell reference (ie the actual reference of that cell?)? I have tried using =CELL("address",[and then the above MATCH formula]), but this returns an error.

Any ideas?

Cheers

pvr928

Are you trying to sum everything before jun-05?

 

[pvr928]

I am attempting to sum the twelve months to the date - so for Jun-05 it will be the twelve months to Jun-05 (ie from Jul04 to Jun05); Jul05 to Jun06 for Jun06, etc

Cheers

 

[Brian from Maui]

I am attempting to sum the twelve months to the date - so for Jun-05 it will be the twelve months to Jun-05 (ie from Jul04 to Jun05); Jul05 to Jun06 for Jun06, etc

Cheers

Try,

=SUMPRODUCT(--(RANGE>=A1),--(RANGE<=A2),RANGE TO SUM)

where A1 and A2 are date references.

Off to dinner, will check back later

 

[Aladin Akyurek]

...Sample data in the unsummarised worksheet is:

Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05


478,359 478,359 462,928 478,359 462,928 478,359 478,359 432,066 478,359 462,928 629,713 609,400


So I want to use Jun-05 as the reference and then aggregate the cells in the row below it and across 11 columns to the left of it, including the cell in the same column as the reference...

I guess this Jun-05 is the real date 1-jun-2005, formatted as mmm-yy.

Where exactly do these haeder months start (or what is the current range)?