Very strange: vlookup shows only half of results characters!?

[ulsterijussi]

Here's what is bugging me;

have 2 workbooks:
1st is a production lot sheet
2nd is a weekly production planning sheet

I want to type in a lot# on 1st workbook and then get all the relevant data from 2nd workbook.

2nd workbook (weekly production planning) has 52 sheets (hence 52 weeks in a year =)), and the production lot #'s are in column A in descending order. The data (numbers and text, such as raw material type, thickness etc.) are in columns B to K.

So far I have installed morefunc to be able to access the second workbook by THREED func.
I´ve tried VLOOKUP and INDEX/MATCH functions. With VLOOKUP I could get the numbers returned correctly, but not text.. If a cell had the text "MONKEY" the function would return only half of it, ie MON. No matter what I wrote in the cell, Excel would only return half of the letters!


=VLOOKUP(B2;THREED('[PRODUCTIONPLANNING.xlsx]1:52'!A1:K44);2;0)</pre>Grateful for any help I can get!

Ulsteri

 

[Aladin Akyurek]

Hi again,

googled MID function. Understand it now.

I tried following:

=MID('[PRODUCTIONPLANNING.xlsx]2'!$C$5;4;1)

and it returned K (4th character of MONKEY, the cell's content) so this is correct!

Can someone help me understand all of this?!

Would you do a straight VLOOKUP (without THREED) directly to the relevant sheet delivering that MONKEY result, so we can see whether THREED is the culprit?

 

[ulsterijussi]

Aladin,

yes! VLOOKUP without THREED returns MONKEY quite correctly!

THREED seems to be the problem here. I guess I'm doomed since I wouldn't like to change the two separate workbook with the other one having 52 sheets. They have been in use here for quite a time and people get used to working in a specific manner I'm afraid.

Must be some kind of programming error in the morefunc addon.

Anyone heard of a THREED func substitute? After a quick google session I atleast didn't find any...

Thanks a lot Aladin for staying with me one this one. Saved me from jumping out the window

 

[Aladin Akyurek]

Aladin,

yes! VLOOKUP without THREED returns MONKEY quite correctly!

THREED seems to be the problem here. I guess I'm doomed since I wouldn't like to change the two separate workbook with the other one having 52 sheets. They have been in use here for quite a time and people get used to working in a specific manner I'm afraid.

Must be some kind of programming error in the morefunc addon.

Anyone heard of a THREED func substitute? After a quick google session I atleast didn't find any...

Thanks a lot Aladin for staying with me one this one. Saved me from jumping out the window

If you reduce the number of sheets you feed to THREED, do you still get the same result? What is the Excel version you use?

 

[ulsterijussi]

Reduced to two sheets only, didn't help. When attempted to reduce to one sheet, gave #REF error.

I'm using Windows 7 and Excel 2007 (Finnish).

 

[Aladin Akyurek]

Reduced to two sheets only, didn't help. When attempted to reduce to one sheet, gave #REF error.

I'm using Windows 7 and Excel 2007 (Finnish).

Let's say sheet 1 has the desired result... Change the formula to something like...

=PHAKU(B2;THREED('[PRODUCTIONPLANNING.xlsx]1:2'!A1:K44);2;0)

Adjust to include the relevant sheet plus an additional one. What I'm up to is to investigate whether there is a limit to THREED...

 

[ulsterijussi]

Made the changes. Unfortunately didn't help.

THREED has a limit of 65k or something cells if I recall correctly.

Tried also to reduce lookup matrix to 12 cells only but this didn't help either.