Formula plus 1 then 2...

[GetHelp]

Hey there,

I have a column where I have a specific formula. Let's say it's the Sum() function. So I have in column A these values 1,2,3,4,5.

Then in B1 I have =Sum($A$1:$A$5). This gives me 15. So what I want to is in column C:

C1: =Sum($A$1:$A$5)+1
C2: =Sum($A$1:$A$5)+2
C3: =Sum($A$1:$A$5)+3

and so on.

What I want to do is to fill C1:C100 and my formula should always increase by 1.

I know I could simply add an extra column (let's say D) and write 1 to 100 in D1 to D100 and then say in C1: =Sum($A$1:$A$5)+D1. This I could fill but I think Excel should be able to do it without the extra step.

The Sum() function is just an easy example. Actually I have a very different formula. But I think it should be the same for any formula.

Thanks in advance
Gethelp

[ChemistB]

Try SUM($A$1:$A$5)+ROW()
Does that work for you?

[GetHelp]

Try SUM($A$1:$A$5)+ROW()
Does that work for you?

hehe, yeah this works in my example. But I was more looking for a general answer.

If I have a formula and then add 1 and in the next row I need this formula (which could be filled in such a way that the references aren't set constant ($)) and then simply add a number and then the same formula and add number+1.

This seems pretty easy to me. But I dont know if Excel is able to do it.

[Ron Coderre]

Perhaps something like this:
C1: =Sum(A1:A5)+1

Then...
C2: =C1+1
Copy that formula down through C100.

Is that something you can work with?

[GetHelp]

Perhaps something like this:
C1: =Sum(A1:A5)+1

Then...
C2: =C1+1
Copy that formula down through C100.

Is that something you can work with?

Unfortunately I can't use this in my case. This is my real case:
I have a formula that searches a given date (in B1) in column A. I use Index() and Match() to get my date. Then in B2 I need the next value from column A.

So let's say I'm looking for 1/1/2010 and this date does exist in A99.

Then in B2 I need A100. This would be Index(A:A;Match(B1;A:A;0)+1) and then Index(A:A;Match(B1;A:A;0)+2) and so on

[ChemistB]

More generally, you just need to put a reference in the first formula, let's say you want row 5 to be +1, then in C5

SUM($A$1:$A$5)+ROW(A1)

Does that work for you?

[scottylad2]

this will produce a 1 every time
C1: =Sum($A$1:$A$5)+Rows($A$1:$A$1)
C2: =Sum($A$1:$A$5)+Rows($A$1:$A$1)
C3: =Sum($A$1:$A$5)+Rows($A$1:$A$1)