Sum(If(Year) and (Month) formula help needed

[pbt]

Good morning folks,

I have the following wooksheets;
Sheet 1

Test Formula.xls
	J	K	L	M
2	1/16/2006	$5,878.20
3	1/17/2006	$5,402.20
4	1/8/2007	$5,511.20
5	2/17/2006	$5,983.00
6	2/18/2007	$5,330.85
7	3/17/2006	$5,125.20
8	3/18/2007	$5,775.40
9	4/17/2006	$6,074.75
10	5/17/2006	$6,614.30
11	5/18/2007	$5,878.20
12	6/7/2006	$4,212.20
13	6/8/2007	$5,415.20
Sheet1

Sheet 2

Test Formula.xls
	A	B	C	D
1		CurrentYear		CurrentYear+1
2
3	JAN	$11,280.40		$5,511.20
4	FEB	$5,983.00		$5,330.85
5	MAR	$5,125.20		$5,775.40
Sheet2

The formulas on Sheet to continue for all twelve months.

What I am looking for, in col B, is something to replace =2006 to "current year" and col D, =2007 to"current year +1.

Also the range of 1470 is an abitrary number at this time. I would need the formula to look at the whole column because length of data may be greater than 1470 rows.

I have tried $J2:$J but that does not work. Don't know if this is an Index issue. Not well veresed on Index.

Any help appreciated.

Thanks
h.h.

 

[pbt]

That's where the confusion is. I posted that my first set up was posted for what I thought was simple to understand:

My apologies guys, I may have confused the situation by trying to build a sheet that I thought would read better

My second post has the actual sheets that I am using.

Again my apoligies

h.h.

 

[Aladin Akyurek]

That's where the confusion is. I posted that my first set up was posted for what I thought was simple to understand:

My apologies guys, I may have confused the situation by trying to build a sheet that I thought would read better

My second post has the actual sheets that I am using.

Again my apoligies

h.h.

This thread has become a jungle. May I suggest you to start a new thread along with exhibits showing no formulas but including the desired results?

 

[pbt]

Aladin,
I was just working on this, got it to work using your formula.

Here are the sheets just in the same:

SCSP 2006 FORECAST test 9.xls
	A	B	C	D	E
1	CURRENTYEAR	2006
2	JANUARY	LH	ST	RETENTION	TOTAL
3	DELWEBBSUNCITY	11,280.40	10,976.00	3,723.80	25,980.20
4	FUTURE1				0.00
5	FUTURE2				0.00
6	TOTAL				25,980.20
7	FEBRUARY
8	DELWEBBSUNCITY	5,511.20	4,688.00	1,823.15	12,022.35
9	FUTURE1				0.00
10	FUTURE2				0.00
11	TOTAL				12,022.35
CURRENT YEAR

SCSP 2006 FORECAST test 9.xls
	A	B	C	D	E
1	CURRENTYEAR+1	2007
2	JANUARY	LH	ST	RETENTION	TOTAL
3	DELWEBBSUNCITY	5,983.00	4,860.00	548.80	11,391.80
4	FUTURE1				0.00
5	FUTURE2				0.00
6	TOTAL				11,391.80
7	FEBRUARY
8	DELWEBBSUNCITY	5,330.85	0.00	0.00	5,330.85
9	FUTURE1				0.00
10	FUTURE2				0.00
11	TOTAL				5,330.85
CURRENT YEAR + 1

• Edited by Nate: Removed last Spreadsheet, missing closing tags, collapsed the thread.

 

[pbt]

Must of had to much info on last post, cut off name of third sheet.

Name of sheet is WEBB. Disregard col. L & M not required for this problem

Did not copy all col. "P-Q" & "U-V" probaly to wide for board. I think you get the idea though.

Could not get NBVC's formula to become absolute for B1 & A2 for copying to other months.

May I suggest you to start a new thread along with exhibits showing no formulas but including the desired results?

You you still need this let me know

Thanks
h.h.