Lookup on different worksheet not working.

[Gruuuu]

Ok I'm stumped here. The answer has to be something really stupid to evade me so well.

I have a workbook that has several tabs with items across the top, and my stores down the left side. The data cells look up the item values (text) in a separate worksheet for each store. Folks at the stores have a sheet that has the items listed, and they key in the number of each item they want mailed to them.

This method has been working for years.

We added a new item, and now it's not working for this item unless the store form is open (not the best solution, I would like to avoid opening each file every week to update this.)

The formulas for the lookups are as follows:

=IF('Z:\Path\[file.xls]Store Order Form'!$D$63<NOW()-7,0,INDEX('Z:\Path\[file.xls]Store Order Form'!$A$1:$A$65536,MATCH(M$1,'Z:\Path\[file.xls]Store Order Form'!$C$1:$C$65536,0)))

$D$63 on the store forms is the current date, which they key in

I've changed this to a Vlookup, because I've heard somewhere that Index/Match doesn't work on external files. Vlookup also does not work, furthermore, Index/Match works for the other items.

So I figured it was something to do with the item name which I'm looking up.

The cell text is exactly the same (copy/paste special values)
The formats are the same.
I copied the format from a different item on both the main sheet and the store forms.

Any ideas?

 

[SkipVought]

hi,

Do you have BOTH alpha-numeric and numeric only Items in the lookup range? And was the new Item numeric only?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

The lookup range has only alphanumeric data, formatted to General. The new item is alphanumeric, formatted to General.
Here's a small sample:
[tt]
1-Up Blue Bar
4-Up Blue Bar
End Cap Sale Sign <---vile offender
Veal Labels
Lamb Labels
[/tt]

Also I forgot to mention the error the formula is giving. I'm getting the #N/A! error.

 

[SkipVought]

Check for leading or trailing spaces in BOTH the lookup value and the value in the lookup range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Confirmed, no leading or trailing spaces in either location.
as a test, I also put in a formula off to the side, like so:

='[file.xls]Store Order Form'!$C$25=M$1

$C$25 is the location of the item on the form

After inputting the formula in the main book, the result is TRUE.
After closing and recalculating the formula, the result is #N/A!

So I will go into even further detail.

I'm working in 2007, both files are 2003 files (.xls)

If I run the compatibility checker, it gives me warnings about linking to other files and file links being longer than 255 characters. My longest reference to an outside workbook is only 111 characters.

If I copy the formula that is returning an error, and paste it directly over another data cell for a different item, I get the expected results.

One odd thing I've noticed is that if I update the links one at a time after opening and closing all the linked files, the first link causes the first two linked forms to return an error, whereas the rest only throw an error when their store is updated. Not really sure what that's all about.

 

[SkipVought]

You know that you can have characters that SEEM to be identical, but the ASCII values can be different.

COPY the value that you entered from the keyboard and PASTE it into the lookup sheet in the proper row.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Yep. Tried that too. A few different ways even.
I tried to:

type it directly in both,

type in one and copy/paste special values to the other,

type in one and copy/hard paste into the other and change the formatting to what it needed to be,

type in one, go to the formula bar, select and copy the text, go to the formula bar of the other cell and paste the text in

Huh. And just now, I tried something different. I put in a direct link to the external file for the item in question (residing in $C$25).
When the file is closed, it gives me the #N/A!.
When I change the reference to $C$24, I get the correct value.

So... ever heard of that one?

 

[SkipVought]

I assume that you have Excel 2003.

What happens if you just reference the columns ...
[tt]
=IF('Z:\Path\[file.xls]Store Order Form'!$D$63<NOW()-7,0,INDEX('Z:\Path\[file.xls]Store Order Form'!$A:$A,MATCH(M$1,'Z:\Path\[file.xls]Store Order Form'!$C:$C)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

I am using 2007, the workbook is a 2003 workbook. I tried saving as 2007 macro enabled workbook (there are macros that do other things), but it still does the same stuff.

Also I forgot to mention that I tried to change the column references as well. They just automatically convert back to what's posted (and I'm just going to change it to something like $A$1:$A$200 anyway, once it gets working again!)

 

[SkipVought]

I did your experiment of 14 Mar 11 11:28 and got TRUE wheher my target workbook on my netwok drive was either open of closed.

Is this a 'Shared' workbook?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

It is not a shared workbook. Not unless it was somehow set as such accidentally. I will have to check it on Wednesday.

 

[Gruuuu]

Confirmed, not shared.

I tried a different test. I created a new worksheet and linked A1 to the store form's A1 cell. I made the reference relative and copied the formula to something like E65, which encompasses the extent of the store form.

D25 and E25 (the item name and pack quantity of the new item) returns #N/A! when the sheet is closed. I checked the protection of the cell and it's the same as the others around it. I copied the item above and hard pasted over the new item, and changed the name. None of that worked. Is it possible that cells can somehow become corrupt?

 

[GlennUK]

Just curious Gruuuu, how many formulas are in your sheets, and what are your usedranges?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Gruuuu]

The page in question has 12 columns, 51 rows. Ctrl&End takes me to M51. That's 612 cells for that sheet

The form has 4 columns, 64 rows. 256 Cells for each form. (that was not by design, even though I have a natural affinity for the powers of 2)

 

[SkipVought]

BTW, lest we get misguided. the ONLY part of your formula that is relevant to the #NA! is...
[tt]
MATCH(M$1,'Z:\Path\[file.xls]Store Order Form'!$C$1:$C$65536,0)
[/tt]
D25 and E25 have nothing to do with the cause of the #NA!

The INDEX function has nothing to do with the #NA!

This issue is the VALUE, End Cap Sale Sign, which I assume, is in ROW 1 as a lookup value and in column A in file.xls Store Order Form sheet.

So what happens if you 1) OPEN file.xls, 2) COPY the value End Cap Sale Sign from row 1 and PASTE in the corresponding
row in Store Order Form, in an empty cell to the right of the table, and ASSUMING that THAT column is AA and THAT row is 10, enter the following formula in AB10...
[tt]
AB10: =MATCH(AA10,$C$1:$C$65536,0)

[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

D25 and E25 (the item name and pack quantity of the new item) returns #N/A! when the sheet is closed.

Sorry, I meant C25 and D25

So what happens if you 1) OPEN file.xls, 2) COPY the value End Cap Sale Sign from row 1 and PASTE in the corresponding
row in Store Order Form, in an empty cell to the right of the table, and ASSUMING that THAT column is AA and THAT row is 10, enter the following formula in AB10...

AB10: =MATCH(AA10,$C$1:$C$65536,0)

Works fine, gives me the row it's in, just as expected.

After that, I tried a couple more experiments:

Opened the store file, copied the End Cap Sale Sign, pasted into G25 of the same file. Saved and closed the file. References to that cell return #N/A!

Opened the store file, copied a different cell, pasted into G25 of the same file. Saved and closed the file. References to that cell work correctly!

Opened the store file, copied a different cell, pasted into G25 of the same file. Typed in End Cap Sale Sign. Saved and closed the file. References to that cell return #N/A!

Whaaat the heck?

 

[Gruuuu]

Ok so it gets weirder, but more predictable now.

If I go to an old item, edit the item name, save and close the file, that cell returns #N/A!s as well. I'm thinking it has to be some sort of formatting thing with 2007. Will dig deeper.

 

[Gruuuu]

A very poor workaround I've found (other than the much less desirable one of opening all the files I link to) is to save the external file as a 2007 excel document. I've never run into this before. I do not understand what's causing this.

And of course this workaround doesn't work for us since the stores use 2003 for now.



my day continues to brighten.