increment Address formula

[Gizmo]

Excel 2003
I have the following formula.
="Reference " & ADDRESS(1,1,4,TRUE)
When I try to drag the formula down to copy it to more cells, the formula
doesn't increment the row.
ex:
Cell "I1" ="Reference " & ADDRESS(1,1,4,TRUE)
Cell "I2" ="Reference " & ADDRESS(1,1,4,TRUE)
etc.

I want:
Cell "I1" ="Reference " & ADDRESS(1,1,4,TRUE)
Cell "I2" ="Reference " & ADDRESS(2,1,4,TRUE)
etc.

What am I doing wrong?

 

[Gary''s Student]

="Reference " & ADDRESS(ROW(),1,4,TRUE)

 

[Teethless mama]

="Reference " & ADDRESS(ROW(I1),1,4,TRUE)
copy down

 

[Niek Otten]

="Reference " & ADDRESS(ROW(),1,4,TRUE)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Excel 2003
| I have the following formula.
| ="Reference " & ADDRESS(1,1,4,TRUE)
| When I try to drag the formula down to copy it to more cells, the formula
| doesn't increment the row.
| ex:
| Cell "I1" ="Reference " & ADDRESS(1,1,4,TRUE)
| Cell "I2" ="Reference " & ADDRESS(1,1,4,TRUE)
| etc.
|
| I want:
| Cell "I1" ="Reference " & ADDRESS(1,1,4,TRUE)
| Cell "I2" ="Reference " & ADDRESS(2,1,4,TRUE)
| etc.
|
| What am I doing wrong?

 

[Ragdyer]

Try this:

="Reference " & ADDRESS(Rows($1:1),1,4)

"True" is superfluous in this case, since it's omission also returns the A1
style.

 

[Gizmo]

Thanks Gary,

Worked like a charm.