EXCEL STRANGE ERROR 2

[gc1234]

Hi all,

I have a very strange error, i use this formula to look up cells

=VLOOKUP(TEXT(B9,"0"),Sheet2!$A$2:$C$3974,3,FALSE)

I also have some VB code that runs on a button control, when i run the code I up date certain cells in sheet2 with a number (increment the number already in there), after running the code, the VLOOKUP does not work?

=VLOOKUP(TEXT(B9,"0"),Sheet2!$A$2:$C$3974,3,FALSE)

any ideas?

 

[xlbo]

Well, you are looking up the textual representation of a number

VLOOKUP(TEXT(B9,"0"),

You are then incrementing a number in your other sheet.

I up date certain cells in sheet2 with a number (increment the number already in there)

When you increment the number, it will force excel to re-evaluate what is in the cell and change it to a true number. Therefore, becuase you are looking up a textual representation of a number, the procedure fails. I am willing to bet that

VLOOKUP(B9,Sheet2!$A$2:$C$3974,3,FALSE)
WILL work where
VLOOKUP(TEXT(B9,"0"),Sheet2!$A$2:$C$3974,3,FALSE)
doesn't

You have a few options

a: change ALL lookup/result data to numbers
b: chnage ALL lookup/result data to text
c: change your formula:

=if(isna(VLOOKUP(TEXT(B9,"0"),Sheet2!$A$2:$C$3974,3,FALSE)),VLOOKUP(B9,Sheet2!$A$2:$C$3974,3,FALSE),VLOOKUP(TEXT(B9,"0"),Sheet2!$A$2:$C$3974,3,FALSE))

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[gc1234]

Well, the cell i lookup is not the cell i update but is on the same row...

 

[gc1234]

My VLOOKUP as now packed up.. I've upgraded to 2003 excel, does anything need turning on?

What is the difference please with this

=VLOOKUP(B8,Sheet2!A2:C3974,1,FALSE)
to this
=VLOOKUP(B8,Sheet2!$A$2:$C$3974,1,FALSE)

 

[01Patrik]

The difference will be obvious if you try to copy and paste (fill) the cell somewhere else... $ locks the reference, without it your reference will move with the copy / paste. Try it with both the examples you mentioned and you will see.



// Patrik

 

[gc1234]

I see.. what about my other problem ;-)

 

[01Patrik]

I agree with XLBO, it should be enough with

=VLOOKUP(B9,Sheet2!$A$2:$C$3974,3,FALSE)

If you worry about the number of rows being changed, you can use

=VLOOKUP(B9,Sheet2!$A:$C,3,FALSE) instead.


If it doesn't work, please post your VB



// Patrik

 

[gc1234]

Well i get a message saying
"The number in this cell is formatted as text or preceded by a apostrohphe"?

Why, if i have made all cells number with 0 decimals does this happen/? the vlookup still wont work even though the cell exists in sheet2, I've tried the above code sent by XLBO many thxs

 

[gc1234]

Got it working, excellent, a collaboration

 

[xlbo]

Make cells number"....... FORMATTING WILL NOT CHANGE WHAT IS IN A CELL. The only way to turn text to numbers is to force excel to re-evaluate them or co-erce them into a value by appyling an addition of 0 or a multiplication of 1 to the entire range

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[gc1234]

Well, you dont have the answer to this one doe you?

http://www.tek-tips.com/viewthread.cfm?SQID=813827&SPID=68&page=1

 

[xlbo]

Already been answered and please do not cross post. This is NOT a helpdesk

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question