Coping formulas

[Guest]

I have a worksheet with data setup that is referenced by several other
worksheets using the vlookup formula. The first worksheet would use the
formula: =vlookup("a",a2:j10,2,false). This then needs to be copied over
changing the column in the formula each time. ie:
=vlookup("a",a2:j10,3,false), =vlookup("a",a2:j10,4,false) etc.

Is there an easier way of doing other than manually?

The other problem I have is that the same formula is used again on other
worksheets referencing the same data only starting on a different column.
For example a2:j10 would change to b2:j10 and then c2:j10. It moves over one
column for each worksheet.

Any help will be appreciated.

 

[Niek Otten]

Put numbers in the header of the column and refer to that cell, like in

=VLOOKUP("a",$A$2:$A$10,B$1,False)

This is an example where the formula is in B2 and below and B1 contains 1.
This formula can be copied down and right and adjusts automatically.

 

[Gord Dibben]

Jordan

One method to increment the column in the VLOOKUP formula is.........

Example only...........Select 4 cells B1:E1

Enter the formula in the active cell(B1) =VLOOKUP(A1,A2:E50,{2,3,4,5},FALSE)

This is an array formula and must be entered with CTRL + SHIFT + ENTER


Gord Dibben Excel MVP

 

[Richard9278]

You could also use:

=VLOOKUP("a",$A$2:$J$10,COLUMN(B$1),False). This should alleviate the
need to number the columns. You can Just copy it to the right and/or
down.

 

[Guest]

Thank You. This is going to save alot of time. I also have to take the same
formula and move it to other worksheets. For each worksheet the
col_index_number array will change by one number. For example Sheet One
would be {7,8,9,10} and Sheet Two would be {6,7,8,9}. The table_array also
has to change by one column. For example Sheet One would be $A$2:$E$7 and
Sheet Two would be $B$2:$E$7.

I have ten rows across and 50 sheets. What you gave me will save a ton of
time as I will make a copy from one sheet to the next and just modifiy the
first and last number in the array. I will also have to change the column on
the table_array for each sheet and then copy it over.

Any additional tips or insight will be apprecieated.

Thanks for what you sent, it's a big help.