Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
No autofill VLookup function
Hi,
I am using a vlookup to extract comments relating to particular ref. no's from a seperate worksheet. The vlookup has been created and works for one cell. However, when I try to autofill it down it copies the formula OK but the results don't change. Whne I double click in each cell and press enter the formula seems to refresh and starts to work. There are over 600 instances so this will take some time! Please help. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
No autofill VLookup function
Hi Owl,
If your formula contains and $ then try removeing them. This should allow your code to work. However, be very carefull when doing this as if you drag your function in the wrong direction it will stop working all together. I would suggest just removing all of the $'s from before the row number if dragging down or before the column letter if dragging accross. ie$A4 or A$4 as this will keep the other portion absolute. HTH Thanks, Simon owl37 wrote: Hi, I am using a vlookup to extract comments relating to particular ref. no's from a seperate worksheet. The vlookup has been created and works for one cell. However, when I try to autofill it down it copies the formula OK but the results don't change. Whne I double click in each cell and press enter the formula seems to refresh and starts to work. There are over 600 instances so this will take some time! Please help. Thanks. -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
No autofill VLookup function
Hi Simon,
I tried removing the $ but this didn't seem to work. The spreadsheet is as follows: Property comments 62411003 0 62411009 0 62411010 0 62411011 0 62411014 0 62418001 Working with Joe to Rehouse The formula in the first cell is as follows: =VLOOKUP(A2,'[Lease Exp 25th Sep.xls]Report!$1:$65536,22) So, It is the comments column that I want to fill using the vlookup. Does this shed any light at all? For the table array I tried using a range name but this didn't work. Thanks for your help so far. Owl37 "smw226 via OfficeKB.com" wrote: Hi Owl, If your formula contains and $ then try removeing them. This should allow your code to work. However, be very carefull when doing this as if you drag your function in the wrong direction it will stop working all together. I would suggest just removing all of the $'s from before the row number if dragging down or before the column letter if dragging accross. ie$A4 or A$4 as this will keep the other portion absolute. HTH Thanks, Simon owl37 wrote: Hi, I am using a vlookup to extract comments relating to particular ref. no's from a seperate worksheet. The vlookup has been created and works for one cell. However, when I try to autofill it down it copies the formula OK but the results don't change. Whne I double click in each cell and press enter the formula seems to refresh and starts to work. There are over 600 instances so this will take some time! Please help. Thanks. -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
No autofill VLookup function
ToolsOptionsCalculation.
Change it to "Automatic". Gord Dibben MS Excel MVP On Thu, 12 Oct 2006 07:38:02 -0700, owl37 wrote: Hi Simon, I tried removing the $ but this didn't seem to work. The spreadsheet is as follows: Property comments 62411003 0 62411009 0 62411010 0 62411011 0 62411014 0 62418001 Working with Joe to Rehouse The formula in the first cell is as follows: =VLOOKUP(A2,'[Lease Exp 25th Sep.xls]Report!$1:$65536,22) So, It is the comments column that I want to fill using the vlookup. Does this shed any light at all? For the table array I tried using a range name but this didn't work. Thanks for your help so far. Owl37 "smw226 via OfficeKB.com" wrote: Hi Owl, If your formula contains and $ then try removeing them. This should allow your code to work. However, be very carefull when doing this as if you drag your function in the wrong direction it will stop working all together. I would suggest just removing all of the $'s from before the row number if dragging down or before the column letter if dragging accross. ie$A4 or A$4 as this will keep the other portion absolute. HTH Thanks, Simon owl37 wrote: Hi, I am using a vlookup to extract comments relating to particular ref. no's from a seperate worksheet. The vlookup has been created and works for one cell. However, when I try to autofill it down it copies the formula OK but the results don't change. Whne I double click in each cell and press enter the formula seems to refresh and starts to work. There are over 600 instances so this will take some time! Please help. Thanks. -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
No autofill VLookup function
Hi Owl,
Give the below ago: =VLOOKUP(A2,'[Lease Exp 25th Sep.xls]Report!$A$1:$T$65536,22,FALSE) I have changed the table array to reference an actual range rather than the whole sheet and added FALSE as the Range lookup. Without this, your formula will find the closest mach to the lookup value. Also, you will need to make sure that the list you are looking up is sorded A- Z on the value you are looking for and that both lookup value and the righthand column of your table array are both the same format (either both text or both number). If you expand both columns you will typically find that text is justified to the left and numbers to the right. (if you using a later version of Excel you will also find that you have a little green triangle in the top lefthand corner of the cells) Once you have tried all of that please let me know if it still doens't work. Thanks, Simon owl37 wrote: Hi Simon, I tried removing the $ but this didn't seem to work. The spreadsheet is as follows: Property comments 62411003 0 62411009 0 62411010 0 62411011 0 62411014 0 62418001 Working with Joe to Rehouse The formula in the first cell is as follows: =VLOOKUP(A2,'[Lease Exp 25th Sep.xls]Report!$1:$65536,22) So, It is the comments column that I want to fill using the vlookup. Does this shed any light at all? For the table array I tried using a range name but this didn't work. Thanks for your help so far. Owl37 Hi Owl, [quoted text clipped - 25 lines] Please help. Thanks. -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
No autofill VLookup function
I believe that when you use FALSE or 0 as the fourth argument,the
lookup_value column does NOT ned to be sorted, and if an exact match is not found it returns #N/A. HTH REgards, Howard "owl37" wrote in message ... Hi, I am using a vlookup to extract comments relating to particular ref. no's from a seperate worksheet. The vlookup has been created and works for one cell. However, when I try to autofill it down it copies the formula OK but the results don't change. Whne I double click in each cell and press enter the formula seems to refresh and starts to work. There are over 600 instances so this will take some time! Please help. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pastable function using VLOOKUP? | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
Help with VLookup function | Excel Worksheet Functions | |||
Using the autofill function but increment by certain number in function | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions |