Concatenating strings and numbers

Originally Posted by jonsch

Hello all! Any help much appreciated!...

I'm trying to concatenate strings and numbers. In Column A, I have the numbers 1-n.
In cell B1, I have typed the following formula:
=CONCATENATE("generate v",A1, "y=1 if v",A1,"=1")

My goal is to get a list of commands like this:
generate v1y=1 if v1=1
generate v2y=1 if v2=1
generate v3y=1 if v3=1
...etc.

The formula itself works perfectly in the first row. The problem is I am trying to have Excel make thousands of these commands (up to v5745), and several other colums of thousands of similar but different commands. After the first row, if I use the Autofill feature, the command comes out, but it does not update the number in the concatenated string. instead, each row has v1 (not v2, v3). I can manually click on the cell, then click on the A2 (or whatever) and it turns blue and updates, but until I do that, the autofill WILL NOT update. I have tried using TEXT(A1,0) in place of A1 just in case it needed to be converted first, but it made absolutely no difference.

I'm pulling out my hair!!! Does anyone know how to make Excel update these autofill relative cell references?

PS it's MS Excel 2002 (XP)
Thanks!

Hi
try

="generate v"&ROW()&"y=1 if v"&ROW()&"=1"

---

Originally Posted by jonsch

I tried: ="generate v"&ROW()&"y=1 if v"&ROW()&"=1"

The same problem exists. When I autofill ROW() stays as 1, for all rows, without updating.

Thanks for the quick reply though. Is this an XP version issue?

it works

set the formula in row 1, then grab the + in the bottom corner of the cell and formula-drag dowwards

then press F9

---