Formulas

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

Use absolute cell referencing. Like this:

=VLOOKUP(K4,$A$3:$C$714,3,FALSE)

The $ symbol locks the cell reference so that it won't increment when copied.

$A3 would lock just the column
A$3 would lock just the row
$A$3 locks both row and column

HTH,
Elkar


"Copying VLOOKUP formulas" wrote:

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

replace your form with:

=VLOOKUP(K4,$A$3:$C$714,3,FALSE)

now the K4 will change, but the lookup table will not
--
Gary''s Student - gsnu200757


"Copying VLOOKUP formulas" wrote:

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

=VLOOKUP(K4,$A$3:$C$714,3,FALSE)

The $ fixes that range so that the address (either row or column or both) won't
change when you copy that formula.



Copying VLOOKUP formulas wrote:

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

--

Dave Peterson

That worked great...with one exception. When I click the bottom left corner
of the cell and drag it down the column, the formula copies correctly but the
returned value is incorrect. The formula copies correctly but it won't
return the correct value unless I click on the formula bar and then hit
enter. I don't have to change anything in the formula itself, just
highlighting the formula bar and then hitting ENTER corrects the returned
value. Any trick to doing that all at once?

"Gary''s Student" wrote:

replace your form with:

=VLOOKUP(K4,$A$3:$C$714,3,FALSE)

now the K4 will change, but the lookup table will not
--
Gary''s Student - gsnu200757


"Copying VLOOKUP formulas" wrote:

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

Your calculation is probably set to manual. Tools |Options |Calculation
(Check Automatic)...

F9 will do a one time calc for you...
--
HTH...

Jim Thomlinson


"Copying VLOOKUP formulas" wrote:

That worked great...with one exception. When I click the bottom left corner
of the cell and drag it down the column, the formula copies correctly but the
returned value is incorrect. The formula copies correctly but it won't
return the correct value unless I click on the formula bar and then hit
enter. I don't have to change anything in the formula itself, just
highlighting the formula bar and then hitting ENTER corrects the returned
value. Any trick to doing that all at once?

"Gary''s Student" wrote:

replace your form with:

=VLOOKUP(K4,$A$3:$C$714,3,FALSE)

now the K4 will change, but the lookup table will not
--
Gary''s Student - gsnu200757


"Copying VLOOKUP formulas" wrote:

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

Is there a way to get the cell # that you want to return data from to change
as you copy it across several columns? (i.e. ",3,false) ,4, false) .5,false))

"Dave Peterson" wrote:

=VLOOKUP(K4,$A$3:$C$714,3,FALSE)

The $ fixes that range so that the address (either row or column or both) won't
change when you copy that formula.



Copying VLOOKUP formulas wrote:

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

--

Dave Peterson

You can use the Row() function for that...

=VLOOKUP(K4,$A$3:$C$714,Row() - 1,FALSE)

Note that this makes the function volatile though which could negatively
effect performance...
--
HTH...

Jim Thomlinson


"pulling my hair out" wrote:

Is there a way to get the cell # that you want to return data from to change
as you copy it across several columns? (i.e. ",3,false) ,4, false) .5,false))

"Dave Peterson" wrote:

=VLOOKUP(K4,$A$3:$C$714,3,FALSE)

The $ fixes that range so that the address (either row or column or both) won't
change when you copy that formula.



Copying VLOOKUP formulas wrote:

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

--

Dave Peterson

Actually, if copying across columns, you'd want to use the COLUMN() function
rather than ROW().

COLUMN(C:C) would return a value of 3 (since C is the third column). When
copied to the right, C:C would increment to D and thus return 4, etc...

HTH,
Elkar


"Jim Thomlinson" wrote:

You can use the Row() function for that...

=VLOOKUP(K4,$A$3:$C$714,Row() - 1,FALSE)

Note that this makes the function volatile though which could negatively
effect performance...
--
HTH...

Jim Thomlinson


"pulling my hair out" wrote:

Is there a way to get the cell # that you want to return data from to change
as you copy it across several columns? (i.e. ",3,false) ,4, false) .5,false))

"Dave Peterson" wrote:

=VLOOKUP(K4,$A$3:$C$714,3,FALSE)

The $ fixes that range so that the address (either row or column or both) won't
change when you copy that formula.



Copying VLOOKUP formulas wrote:

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

--

Dave Peterson

Ooops. I was thinking about the first post where the op was copying down
rows... My Bad. Thanks for catching that...

To avoid the performance hit associated with column() and row() functions
(volatile) you might consider just putting the number you want into a cell
and dragging it to increment the value. You can then just (relative)
reference the cell.
--
HTH...

Jim Thomlinson


"Elkar" wrote:

Actually, if copying across columns, you'd want to use the COLUMN() function
rather than ROW().

COLUMN(C:C) would return a value of 3 (since C is the third column). When
copied to the right, C:C would increment to D and thus return 4, etc...

HTH,
Elkar


"Jim Thomlinson" wrote:

You can use the Row() function for that...

=VLOOKUP(K4,$A$3:$C$714,Row() - 1,FALSE)

Note that this makes the function volatile though which could negatively
effect performance...
--
HTH...

Jim Thomlinson


"pulling my hair out" wrote:

Is there a way to get the cell # that you want to return data from to change
as you copy it across several columns? (i.e. ",3,false) ,4, false) .5,false))

"Dave Peterson" wrote:

=VLOOKUP(K4,$A$3:$C$714,3,FALSE)

The $ fixes that range so that the address (either row or column or both) won't
change when you copy that formula.



Copying VLOOKUP formulas wrote:

I have the following formula inserted into a cell. I know it works because
it returns the correct value.

=VLOOKUP(K4,A3:C714,3,FALSE)

I now need to copy this same formula in the same column over about 700 rows
of data. The "K4" will obviously change since that's the value I'm looking
for, but how do I lock the range table, i.e. "A3:C714", so it won't increase
when I copy the formulas down? Also, is there something I need to do in
Paste Special to make the formula work in the other rows? I've tried
everything and I can't get the formula to copy correctly. I obviously don't
want to retype it 700 times.

Any help would be GREATLY appreciated.

--

Dave Peterson