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

HLOOKUP with IF formula

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


Joined: 19 Apr 2011
Posts: 3
Location: Denmark

PostPosted: Tue Apr 19, 2011 3:43 am    Post subject: HLOOKUP with IF formula Reply with quote

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
Back to top
View user's profile Send private message
mgroenescheij
Super User
Super User


Joined: 20 Apr 2011
Posts: 870
Location: Australia

PostPosted: Tue Apr 26, 2011 3:05 am    Post subject: Reply with quote

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


Joined: 19 Apr 2011
Posts: 3
Location: Denmark

PostPosted: Wed Apr 27, 2011 12:21 am    Post subject: HLOOKUP with IF formula Reply with quote

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
Wink
_________________
Regards
Sandra
Back to top
View user's profile Send private message
keme
Moderator
Moderator


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

PostPosted: Wed Apr 27, 2011 1:58 am    Post subject: Reply with quote

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


Joined: 19 Apr 2011
Posts: 3
Location: Denmark

PostPosted: Mon May 02, 2011 1:54 am    Post subject: Reply with quote

Hey,

I hope this will illustrate my problem better then what I've explained before. Confused
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
Back to top
View user's profile Send private message
keme
Moderator
Moderator


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

PostPosted: Mon May 02, 2011 4:20 am    Post subject: Reply with quote

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)
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