CODEWITHSUNDEEP
excelexcel-formulacounterformula-editor
user1874594
user1874594

Reputation: 2483

MS Excel : changing the counter in formula to some other than the one Excel has assumed

An excel formula looks like this 

=", " &($C$1)+1

Here's how the data looks when I paste special --> forumulas 

    1
, 2
, 3
, 3

I want it to increase the LAST Number +1 in the counter . Something like 

    1
, 2
, 3
, 4

I can of course fix it many "work-around" ways like having 1 more column for the step up counter and then using it in the formula. Here Excel BY DEFAULT will step up the counter on Column C1 like this

 =", " &(C1)+1

and increase C1 to C2 C3 ..Cn on a "paste special" ..unless you freeze it with $C$1 but then it's not sensible enough to consider the last +1 as the counter and use that. So the counter remains the same and the value is the same. How to tell excel to use the last + 1 as the counter

Upvotes: 0

Views: 75

Answers (2)

Tim Williams
Tim Williams

Reputation: 166351

Maybe 

=", " & SUBSTITUTE(C1,", ","")+1

Upvotes: 1

nbayly
nbayly

Reputation: 2167

Assuming your first formula is in A1 you can use:

=", "&($C$1)+(1-ROW($A$1)+ROW(A1))

When you copy down, the original ROW($A$1) will stay the same while the second ROW(A1) will change with your copy range. Cheers,

Upvotes: 1

Related Questions