OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Need help with CALC Formula

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jnocal
Newbie
Newbie


Joined: 16 May 2012
Posts: 4

PostPosted: Wed May 16, 2012 12:52 pm    Post subject: Need help with CALC Formula Reply with quote

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?
Back to top
View user's profile Send private message AIM Address
jnocal
Newbie
Newbie


Joined: 16 May 2012
Posts: 4

PostPosted: Wed May 16, 2012 2:12 pm    Post subject: Reply with quote

The more I look into this the more I think this is a VLOOKUP solution. However, I cannot get VLOOKUP to work.
Back to top
View user's profile Send private message AIM Address
Ed
Super User
Super User


Joined: 28 May 2003
Posts: 1041

PostPosted: Wed May 16, 2012 2:29 pm    Post subject: Reply with quote

Can you post the exact formulae you have tried?
Back to top
View user's profile Send private message
jnocal
Newbie
Newbie


Joined: 16 May 2012
Posts: 4

PostPosted: Wed May 16, 2012 2:36 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message AIM Address
jnocal
Newbie
Newbie


Joined: 16 May 2012
Posts: 4

PostPosted: Wed May 16, 2012 8:27 pm    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message AIM Address
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Fri May 18, 2012 12:14 am    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group