Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
=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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
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: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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
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: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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
"Jim Thomlinson" wrote: 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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
One more method assuming your table width is more than 2 columns.
Select D1:H1 In D1 enter =VLOOKUP(K4,$A$3:$C$714,{2,3,4,5,6},FALSE) CTRL + SHIFT + ENTER as it is an array formula. BUT note that your original lookup table was only 3 columns wide so you would have to increase that table size to have any need to copy across more than two columns. Gord Dibben MS Excel MVP On Tue, 20 Nov 2007 16:00:01 -0800, Jim Thomlinson wrote: 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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
Sorry for the blank post...
I ran into a similar problem today. I have my calculation set to automatic. I was using the following formula: =IF(A2="C",$C$1*B2,$D$1*B2) It gave me the correct value for the row I was in. As I clicked that cell on the bottom right corner and dragged it down the column, it copied the formula correctly (noting both the relative and absolute references), but the value that appeared in each cell was the calculation from the first formula. In other words, in the first cell, I would see 48.9671, and after copying the formula, it showed as 48.9671 in each of the concurrent cells. Again, I made sure that automatic calculation was enabled. I tried opening a new workbook from the workbook I was working, copying the data and re-entering and copying the formula - same results. Then I opened another instance of Excel from the start menu and copied the data, re-entered the formula and copied. This time it worked. The workbook that I was working in was sent to me from another organization, but it was created through DTS for either SQL Server or Oracle, or Pervasive....I know they're using a SQL engine, just not sure which one. I was hoping somebody might have some insight into what's going on with this specific workbook. Oh, also forgot to mention, in our office 2007 suite, we have our default set to save in compatibility mode for 2003 users in our outside offices. "Jim Thomlinson" wrote: 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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
And remember that you have to have enough columns in the lookup range.
For instance, you couldn't return column 6 from A3:C714 (3 columns wide). 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |