I have several worksheet I am working from in Excel 2010. One worksheet has rows with account ID numbers. Also, there ae columns with the months for year 2012. I want to go into another worksheet and pull all the costs by account number for the appropriate months using the Vlookup formula. The formula works fine in the first cell but when I try to drag (copy) the formula, the result is either an REF# or N/A# error. Here is my formula =VLOOKUP($A6,'Subk'!$A7:$F18,4,FALSE) When I drag the formula, the number 4(column that I want it to look for the return value) does not change. I want it to change to 5, 6, etc as I drag it. so it picks up the value in column 5 of the other worksheet , then column 6, etc to avoid having to do a vlookup formulafor every single entry. What am I doing wrong? Here are samples of my worksheets:

FISCAL YEAR 2012

Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12

1100-000 Sub Labor

1100-020 Sub Fee

1100-800 Sub Costs Accrued

The other worksheet looks like:

Fy Pd

2012

Acct Id 06 07 08 09 10 11 12

1100-000 B SUB LABOR 32,349.61

1100-020 B SUB FEE 3,234.95

1100-000 E SUB LABOR 400.17

1100-000 B SUB LABOR 5,008.84

1100-800 D COSTS ACCRUED 31,119.91

1100-020 D SUB FEE 550.51

First, a posting tip: Before posting any more example data in this fourm, please click on the

blue lineat the end of this post and read the instructions found via that link.That said, I'm not following exactly what you want to do, so I'll address this one issue for now. If there's something else you need help with, come on back.

When I drag the formula, the number 4...does not change. I want it to change to 5, 6, etc as I drag itIf you want the 4 to increment, you have to use an argument that will increment automatically.

One way to do that is to use the ROW function. The ROW function does nothing more than return the number of the Row it is used in.

e.g. if B6 contains =ROW(), it will return 6

Well, 6 - 2 = 4, so if you put this in B6, you'll get 4, drag it down one Row, you'll get 5, etc.

=ROW()-2

Therefore using:

=VLOOKUP($A6, 'Subk'!$A7:$F18, ROW()-2, FALSE)

will return a col_num argument that is always 2 less than the Row in which the formula is used.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History