CODEWITHSUNDEEP
excelvbamacrosexcel-formula
Twi
Twi

Reputation: 825

Incrementing cell address by 2 in formula

I have a formula like this:

=CONCATENATE("some text";D20;"sometext too ";E20)

I would like to auto fill these by dragging down the corner of the cell, but If I do that, I get this:

=CONCATENATE("some text";D20;"sometext too ";E20)
=CONCATENATE("some text";D21;"sometext too ";E21)
=CONCATENATE("some text";D22;"sometext too ";E22)
...

But I like to see that:

=CONCATENATE("some text";D20;"sometext too ";E20)
=CONCATENATE("some text";D22;"sometext too ";E22)
=CONCATENATE("some text";D24;"sometext too ";E24)
...

Any solution for that? I used Google for hours, but no solution.

VBA macro solution also acceptable

Upvotes: 0

Views: 90

Answers (2)

ShirleyM
ShirleyM

Reputation: 36

I think you're original problem is just that you are using semicolon ; instead of comma , in your formula.

Personally, I never use the =CONCATENATE formula these days. You can achieve the same using &, for example:

="Some text "&A1&"some text too "&B1

Hope that helps

Upvotes: 1

Mrig
Mrig

Reputation: 11702

Try this:

=CONCATENATE("some text ",OFFSET(D$20,(ROWS(D$20:D20)-1)*2,0,1,1)," sometext too ",OFFSET(E$20,(ROWS(E$20:E20)-1)*2,0,1,1))

Upvotes: 0

Related Questions