Auto-fill (1 Viewer)

[ScottXe]

I have a formula, =VLOOKUP("*"&TEXT($A5;"000000")&"*";Vlookup!$A$2:$O$1405;4;FALSE) and would like to drag the lower corner of the cell to the right columns. How can I make the "4" to automatically add 1 for next cell? Thanks!

 

[CJ_London]

use the columns function e.g.

columns(A$:A) returns 1

so perhaps columns(A$:A)+3

 

[ScottXe]

Hi CJ,

Thanks for your suggestion! I tried columns(A$:A)+3 without success but fine with columns($A:A)+3. I am at loss of the syntax and could you please shed some light on this function?

 

[CJ_London]

my bad - should be columns($A:A)+3

re the column function - google 'excel columns function' or see the excel help

re the total function you posted, no idea what it is supposed to do but looks like it is looking up a number in column A formatted with preceding zeros and bracketed with asterisks in column A (i.e. a number 1020 is converted to *001020*) of a worksheet called vlookup (dangerous to call it the same name of a function) columns A to O to find a value in column D