View previous topic :: View next topic 
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 

Back to top 


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 

Back to top 


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$59H8;IF(N8="Kim";'file:///M:/spreassheet3'.$T$63H7 ))
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$59H9;AND(IF(N8="Abel";O8H9));IF(N9="Kim";'file:///spreadsheet3.$T$63H9;AND(IF(N8="Kim";O8H9))))
Hope it all makes sense
regards
Sandra
_________________ Regards
Sandra 

Back to top 


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. 

Back to top 


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")));O8H9; OR(IF(N8="Kim"(AND(IF(N9="Kim")));O8H9;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 


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");O8H9; IF(AND(N8="Kim";N9="Kim");O8H9;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);O8H9;0) 

Back to top 


