Need help with CALC Formula

[jnocal]

I've hit the limit on a formula in Calc (Err:512).

I am trying to pull data from one spreadsheet into another. One one spreadsheet I have trucking firms and the freight they charge at different destinations.

On another spreadsheet I enter loads. I'd like to enter the truck firm and where the load came from. How do I build a formula that will match up the truck and destination from original spreadsheet and enter the appropriate charge?

[jnocal]

The more I look into this the more I think this is a VLOOKUP solution. However, I cannot get VLOOKUP to work.

[Ed]

Can you post the exact formulae you have tried?

[jnocal]

OK - Here is the Formula:

=VLOOKUP(Y2;Sheet6.A2:b16;2;0)

I actually had it working as part of an =IF function:

=IF(F2="Rios";VLOOKUP(Y2;Sheet6.A2:B16;2;0))

However, I want it to look up the values pulled from another spreadsheet (NOT a separate sheet in the same spreadsheet.

[jnocal]

Why can't I pull information between files? Here is my formula:

=IF(AND(F2="xxxx";OR(G2="yyyyy";G2="wwww"));VLOOKUP(Y2;'file:///C:/Users/Owner/Documents/OTC/Freight Charges.ods'#$xxx.A2:D16;3;0);0)

[ozzie]

Hi jnocal
I can see nothing obviously wrong with your formula, and could only question "Freight Charges.ods'#$xxx.A2:D16;3;0" that if your trying to pick up column "D" then "4" should be the third argument.
Without seeing your sheets and not knowing the errors I can only suggest some common VLOOKUP problems
1 "Y2" has to be in column "A" of the above data range
2 With mode "0" as the fourth argument the left column of datatable may be unordered, and the first exact match is found (searching from the top)
3 An exact match is required, which means that their can be no leading or trailing spaces which can't be seen with the naked eye but if you enter the cell and look in the formula bar see if the data string sits entirely to the left and that the mouse cursor is hard up against the end of the string.

If you are getting errors then looking at your formula and highlighting smaller portions (such as "AND(F2="xxxx";OR(G2="yyyyy";G2="wwww"))",then hitting "F9" a box will pop up with an answer to that portion and will start to narrow down the bad portions of your fomula (caution - hit "ESC" before hitting enter to exit this) if you don't the undo button will restore your formula.

If none of this helps then I can only suggest that you post a copy of both files to a site such as www.mediafire.com after you amend any confidential info.

Good luck.
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).