[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
SandraN
Newbie

Joined: 19 Apr 2011
Posts: 3
Location: Denmark

 Posted: Tue Apr 19, 2011 3:43 am    Post subject: HLOOKUP with IF formula Hey I haven't used OO before and have some questions regarding how to write a formula that allows me to deduct 10 working hours in April from a project that lasts 50 hours. Seeing as there isn't enough time in April the formula then needs to deduct the working hours in Mai from the project time. The working hours is in a different spreadsheet the the rest of my data. I have tried with HLOOKUP but keep getting an err. message. The formula that allows me to do the above needs to be combines with an IF formula, so that I can: 1. I choose an A in my list and get 20 hours in the next cell (solved) 2. I then choose a name in another list, that name tells the formula which row to look at the other spreadsheet. I know that it's a lot of questions, but I really really hope there is some one that can help me._________________Regards Sandra
mgroenescheij
Super User

Joined: 20 Apr 2011
Posts: 871
Location: Australia

 Posted: Tue Apr 26, 2011 3:05 am    Post subject: Hi Sandra, How does the HLOOKUP formula look? What is the error message? Is the 10 working hours you like to deduct the available unassigned working hours, than you don't like to deduct 10 hours, but available hours and assign the remaining hours for your project to the next month. Can you give us some more details, otherwise we're unable to assist. Regards, Martin
SandraN
Newbie

Joined: 19 Apr 2011
Posts: 3
Location: Denmark

 Posted: Wed Apr 27, 2011 12:21 am    Post subject: HLOOKUP with IF formula Hello Martin, I've tried without the HLOOKUP formula and instead used the following: =IF(N8="Abel";'file:///spreadsheet3'.\$T\$59-H8;IF(N8="Kim";'file:///M:/spreassheet3'.\$T\$63-H7 )) Meaning that if I choose Abel in my list I get the time from cell T59 in the separate spreadsheet then it deducts the amount of time the job takes (cell H7) showing the result in cell O8. The same goes if I choose Kim in my list. That works perfectly but I still have difficulties with the next step. If I choose Abel in my list(cell N7) and get result in cell O7, I want the list below(N9) to deduct the time in cell O7 BUT only if I have chosen Abel in both lists. I've tried with this formula, but get error 504: =IF(N9="Abel";'file:///spreadsheet3.\$T\$59-H9;AND(IF(N8="Abel";O8-H9));IF(N9="Kim";'file:///spreadsheet3.\$T\$63-H9;AND(IF(N8="Kim";O8-H9)))) Hope it all makes sense regards Sandra _________________Regards Sandra
keme
Moderator

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

 Posted: Wed Apr 27, 2011 1:58 am    Post subject: Check your cell references. As far as I can see, the explanation does not match the formulas, in particular a few instances of "time to deduct". If you have useable lists (single item/single row), it's probably better to use list handling functions than having a lot of IF functions and hardcoded references. If you explain how your lists are laid out, it may help us to help you.
SandraN
Newbie

Joined: 19 Apr 2011
Posts: 3
Location: Denmark

Posted: Mon May 02, 2011 1:54 am    Post subject:

Hey,

I hope this will illustrate my problem better then what I've explained before.
 Code: H   I    J     K     L   M   N     O                       TIME required               Time awable                    20   4   30   22,5   19      Abel    136                  (row 8) 20   4   22,5   15   15      Abel    Err:509             (row 9) In cell O9 I have this formula:

=IF(N8="Abel"(AND(IF(N9="Abel")));O8-H9; OR(IF(N8="Kim"(AND(IF(N9="Kim")));O8-H9;0)))

As you can see I get err: 509

The hours in cell O8 is from a different spreadsheet.

Regards
Sandra
_________________
Regards
Sandra
keme
Moderator

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

 Posted: Mon May 02, 2011 4:20 am    Post subject: The spreadsheet formula language differs a bit from natural spoken language. Your ands and ors are a bit misplaced, it seems. My interpretation of your formula, translated to proper syntax: =IF(AND(N8="Abel";N9="Abel");O8-H9; IF(AND(N8="Kim";N9="Kim");O8-H9;0)) If you want to cater for more names than "Abel" and "Kim", you should probably use a different approach. See if the cells contain text and are equal, perhaps: =IF(AND(ISTEXT(N8);N8=N9);O8-H9;0)
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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