Vlookup function in excel not working! It's driving me crazy!

[cutestuff]

hi,

I'm so confused. I've been using the Vlookup function in excel so many times and all of a sudden it's not working. The weird thing is, it's just on this one sheet. All my other sheets seem to work fine.

What happens is that the formula doesn't seem to read as a formula. For example, when I type this in the cell:
=VLOOKUP(D7,tblPunchStats'!$A:$G,7,FALSE)
it shows up EXACTLY like this (like it's a text and not a formula.)

Any ideas?
Please?

thank you...

 

[Cordury2]

Is your lookup value a number? Multiply D7 by one and see if that helps.

 

[Cordury2]

Oh, I did not read the entire post. Hit Ctrl + the "tilde" key to the left of the 1 key.

 

[mhoyt]

Cordury2, can you explain why that will help? I've had the same problem, the exact same formula works fine in certain cells of my spreadhseet, and stubbornly displays as text in other cells. I suspect it is a cell formatting issue but I've never stuck with it long enough to figure it out. (Easier to just delete the offending columns and re-type the formula in a new column)

 

[MSIsam]

It may be that the cell is formatted to text

Can you check the format of the cell to see if it is text? If so, reset the format to general (or some other appropriate formula). You will have to enter and exit the cell to see the correction take place.

 

[Cordury2]

I think that function just displays the actual formulas in the cells and not just the formula bar...but am not sure.

 

[Zathras]

Did you copy and paste? If so, it may be the apostrophe that is throwing it off. This works for me:
[tt]
=VLOOKUP(D7,tblPunchStats!$A:$G,7,FALSE)
[/tt]

 

[cutestuff]

hi!

thanks to all that replied. however, none of those worked still. I actually have a vlookup link to another spreadsheet, but when i pasted the formula i just trimmed it down so I didn't have the ' in it.

I finally got it to work though but all i did was when it opened it told me "unable to read file" or something like that and then it brought up another msgbox where I was able to select "update source" (i've never seen it before though) and I just updated the link from there. It's so weird.

if you guys ever find out why it does this though, let me know!

thanks a bunch!

 

[GlennUK]

Did you take MSIsam's hints? You say that none of things suggested worked, and wondered if you actually tried that one.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

almost certainl;y, the cell you put the formula in was pre formatted as text. Re-format as general and re-enter the formula to test this....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cutestuff]

yes, i actually tried to reformat it. it didn't work.

okay, here's the whole story:
i have a spreadsheet that keeps track of how many outstanding items each system has. each of the columns (for instance, open A's, open B's) has a vlookup formula to grab the values from another spreadsheet. what happened was they did some reorganizing of files and moved the spreadsheet to another location. hence my link broke. when i tried to re-establish the link (that is, redo the vlookup on a cell that already has an inactive vlookup), it wouldn't work. that's why i couldn't figure out why. i had it there before, in those exact cells and they worked. all of a sudden they wouldn't.

thanks to all who replied though!
appreciate it!

 

[xlbo]

did you re-enter the formula after re-formatting as general ???

you cannot just reformat and expect it to work - you must re-enter the formula

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Or after re-formatting just hit F2 and then ENTER.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------