What Do I do NOW

[TiggerToo]

I have this formula:
=if(or(c6=11,c6=0),"",vlookup(c6,dutycode!a4:a15,4,))

My problem is when a number is not on the lookup list I get a #N/A in the same cell as the formula (F6).
sheet 01 cell c6 is where I input a code,
sheet(dutycode) is where vlookup searches for the correct codes to enter in CELL F6.
I do not know what to do?
this is what is also needed;
if c6>=39
if c6<=410
if c6>=412
if c6<=610
if c6>=612,"")
The codes that need to have a response to in cell F6 are 11,12,26,27,29,31,32,38,411,611
that is why I want everything else to show a blank.
Thank you,
Walter

 

[IML]

On 2002-03-05 19:27, TiggerToo wrote:
I have this formula:
=if(or(c6=11,c6=0),"",vlookup(c6,dutycode!a4:a15,4,))

My problem is when a number is not on the lookup list I get a #N/A in the same cell as the formula (F6).
sheet 01 cell c6 is where I input a code,
sheet(dutycode) is where vlookup searches for the correct codes to enter in CELL F6.
I do not know what to do?
this is what is also needed;
if c6>=39
if c6<=410
if c6>=412
if c6<=610
if c6>=612,"")
The codes that need to have a response to in cell F6 are 11,12,26,27,29,31,32,38,411,611
that is why I want everything else to show a blank.
Thank you,
Walter

I think
=IF(COUNTIF(dutycode!$A$1:$A$10,C6),VLOOKUP(C6,dutycode!$A$1:$D$10,4,0),"")

would do the trick, assuming your range is A1:D10 on you dutycode page. the countif range should be the left most column of your range.

 

[Aladin Akyurek]

On 2002-03-05 19:27, TiggerToo wrote:
I have this formula:
=if(or(c6=11,c6=0),"",vlookup(c6,dutycode!a4:a15,4,))

My problem is when a number is not on the lookup list I get a #N/A in the same cell as the formula (F6).
sheet 01 cell c6 is where I input a code,
sheet(dutycode) is where vlookup searches for the correct codes to enter in CELL F6.
I do not know what to do?
this is what is also needed;
if c6>=39
if c6<=410
if c6>=412
if c6<=610
if c6>=612,"")
The codes that need to have a response to in cell F6 are 11,12,26,27,29,31,32,38,411,611
that is why I want everything else to show a blank.
Thank you,
Walter

Walter,

The formula that IML suggests in response to your current thread has been already given to you in the previous thread.

Apparently, such a formula doesn't fit your needs. That's surprising.

Care to post the lookup table that you're trying to use?

Aladin

 

[TiggerToo]

Here is the lookup table I am using.
column A Column B Column C
DUTY CODES SHRED CODES EXPLANATION
11 Direct Labor (REG) Blank space
411 Over Time OT
611 Compensate CT
12 Direct TDY
26 30 Shop Clean-up SHPC
27 1 Training TRNG
29 Union Activities UNIN
31 Annual Leave AL
32 Sick Leave SL
33 44 Holiday HOL
38 Compensatory Leave CTL
What I explained earlier, I would like for
a blank to appear whenever a number that
is not on the lookup table is entered in
column

Below is a small sample of my excel sheet that I use for time. This is the way

col a col b col c col d
date code hrs type code

______________________________
M 3-1-02 | 27 | 3 | TRNG
M 3-1-02 | 11 | 3 | (REG)Work is Blank
M 3-1-02 | 31 | 2 | AL
T 3-2-02 | 11 | 8 |
W 3-3-02 | 11 | 2 |
w 3-3-02 | 27 | 2 |Inserted extra line
W 3-3-02 | 32 | 4 | SL

NOW For Part 2!!!

I also need a formula to total each day's hours, not to exceed 8 hrs, unless OT code is in col B.
My other problem is on the summary sheet, I have 14 columns, by 26 rows (PayPeriods), One column for each day of the payperiod. The only times that go on this summary sheet are ANNUAL LEAVE, SICK LEAVE or HOLIDAY. I do not know how to write a formula to look at more than one line if the same date goes on more than one line.
If a line is inserted in the above form, Annual Leave will move over one day later which I do not want to happen)or it will not appear at all.
# PayPeriod.....S.M.T.W.T.F.S/S.M.T.W.T.F.S
06 Mar 24 Apr 06....3.4
07 Apr 07 Apr 20....
08 Apr 21 May 04
09 May 05 May 18
10 May 19 Jun 01

HELP,

Thanks
Walter
This message was edited by TiggerToo on 2002-03-07 02:39
This message was edited by TiggerToo on 2002-03-08 18:24