No autofill VLookup function

[Guest]

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.

 

[smw226 via OfficeKB.com]

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

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.aspx/excel-functions/200610/1

 

[Guest]

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,

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

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.aspx/excel-functions/200610/1

 

[Gord Dibben]

Tools>Options>Calculation.

Change it to "Automatic".


Gord Dibben MS Excel MVP

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,

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

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.aspx/excel-functions/200610/1

 

[smw226 via OfficeKB.com]

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

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]

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200610/1

 

[L. Howard Kittle]

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