need a shorter formula

[niyrho]

I need another way to do this:

=IF(E5=0,D4,IF(H5=0,G4,IF(K5=0,J4,IF(N5=0,M4,""))))

This formula works for me, but I can't make it long enough. It only allows me to use 7 IF funtions. I have more than that to put it, and it has to look them up in order.

 

[niyrho]

I'm not sure if that'll work, I just dont' get that code.

My sheet looks something like:

C D E F G H
4 job 1 in / job 1 out / trv-brk Job 2 in / job 2 out / trv-brk
5 job name / trv time job name / trv time

And it just goes on like that, up to job 12.
The code I tried using worked perfect, I just couldn't make it long enough.

=IF(Sheet!3E4="break",Sheet3!D4,IF(Sheet3!H4="break",Sheet3!G4,""))

That did exactly what I needed, but only for the first 7 jobs. I couldn't make it any longer than that.

All I need is something that will do the exact same thing but allow to go accross 12 "jobs".

 

[Yogi Anand]

I'm not sure if that'll work, I just dont' get that code.

My sheet looks something like:
<font color="blue">
C D E F G H
4 job 1 in / job 1 out / trv-brk Job 2 in / job 2 out / trv-brk
5 job name / trv time job name / trv time
</font>
And it just goes on like that, up to job 12.
The code I tried using worked perfect, I just couldn't make it long enough.
<font color="red">
=IF(Sheet!3E4="break",Sheet3!D4,IF(Sheet3!H4="break",Sheet3!G4,""))
</font>
That did exactly what I needed, but only for the first 7 jobs. I couldn't make it any longer than that.

All I need is something that will do the exact same thing but allow to go accross 12 "jobs".

I am missing something here ...
Can you show us <i>what exactly is it </i>that you got by posting your data on the Board using HTMlmaker, and how does your formula shown in RED with your data shown in BLUE does <i>exactly what you need?</i>

 

[niyrho]

I didn't use htmlmaker. I just typed it in. I don't konw why they're different colours. That's hardly the point anyway.

 

[Yogi Anand]

I didn't use htmlmaker. I just typed it in. I don't konw why they're different colours. That's hardly the point anyway.

Hi niyrho:

Of course color is hardly the point ... I put in the colors so you can see your own data and the formula you used. To put it in as simple words as I can, it does not appear to me the formula you have used with the data you have shown will get you exactly what you need.

So using your own data and your own formula, post your solution on the Board using HTMLmaker to show how is the formula giving you exactly what you need ... this way you will be helping me understand what I am missing here.

 

[niyrho]

Sorry, I misunderstood your last post. Unfortunately, I'm not sure I completely understand yet. Using the =IF functions like that did exactly what I needed it to do. My only problem is that I can only use 7 =IF functions and I need 12. All I need is a formula that does the exact same thing, just over a wider range of cells.

 

[Yogi Anand]

Sorry, I misunderstood your last post. Unfortunately, I'm not sure I completely understand yet. Using the =IF functions like that did exactly what I needed it to do. My only problem is that I can only use 7 =IF functions and I need 12. All I need is a formula that does the exact same thing, just over a wider range of cells.

Hi niyrho:

Did you check the consistency of the data and the formula in your last post? -- they do not give you exactly what you need.

Consistent with your first post, I gave you the requested shortened formula in my solution. Try to reproduce exactly what I have shown ... if you succeed in reproducing what I showed in my post, you will be half way through ... then apply that methodology to your new (changed data in your most recent post). Good Luck!

 

[niyrho]

Sorry, I tried to simplify it a lil, make it easier to understand. I guess I failed.