If null or if is not null question ...

[nedy_03]

Hello,

I'm having this situation: two worksheet "REPORT" and "NOT WORKED or NOT REQUESTED". In both of them I have on column A the names of employees. In the first worksheet for each day I have to put for every employee the number of hours worked in the specific field: "lcrt/trng/pb.th client/pb. Th. GRS/not requested/absent/intv" ... the columns of interest are the pink ones :P ... If I put something in them in front of the person that was "absent" or "not requested", in the second worksheet I need to be written "absent" or "not requested"

EX: if in "REPORT!H4" I heve, let's say, 4:00 (hours) in "NOT WORKED or NOT REQUESTED!D3" I need to be written "not requested" and if in "REPORT!I4" I have 4:00 (or any value) , I need in "NOT WORKED or NOT REQUESTED!D3" to be written "absent".

Please help ... the request would be more complex but for the moment I would be very grateful if u help me on that ...

If is to complicated with two worksheets let's supose that we have two worksheets in one main worksheet! Maybe I will figure out how it works and do it myself .... until then need u guys!

Thx,
Nedy

[JimmyTheHand]

Until you receive a better solution, try this. I made a formula for the task. I'm not sure how references to REPORT.XLS will work, because I have seen full path of the file in the formula, whereas I've only put "INDEX([REPORT.xls]Project1!$1:$65536;" there. Excel seems to be aware of file locations as well. Nevertheless, the formula can be copied to any cell of interest. Also, I'm not sure if parameters of CELL function translate well, so I give what I think is the English equivalent. This goes into cell D2.

=IF(ISNUMBER(INDEX([REPORT.xls]Project1!$1:$65536,CELL("row",D2)+1,(CELL("column",D2)-3)*7+1)),"not requested",IF(ISNUMBER(INDEX([REPORT.xls]Project1!$1:$65536,CELL("row",D2)+1,(CELL("column",D2)-3)*7+2)),"absent",""))

(I see the formula is cut in two by the forum software. It's actually 1 line)

HTH
Jimmy

[acw]

Hi

What happens if you have numbers in Report H4 and report I4? Concatenate the reasons?


Tony

[nedy_03]

Thx,

It never happens that way because if someone works or doesn't work in one day, let's consider 01/01/2007, it will be noted in just one field from D to J column.

But let's consider that it happens ... it is ok to semnalate in "NOT WORKED or NOT REQUESTED" with "absent/nesolicitat" for one of two or both ...
I just need the second worksheet to indicate if someone didn't come to work ... uncaring if he wasn't requested or didn't come (absent) ...

[Bob Phillips]

In D2:

=IF(INDEX('[REPORT.xls]Project1'!3:3,,(COLUMN()-3)*7+1)<>"","Not requested",
IF(INDEX('[REPORT.xls]Project1'!3:3,,(COLUMN()-3)*7+2)<>"","Absent",""))

[nedy_03]

I used this simple "IF" formula : " =IF([REPORT.xls]Project1!H3>0,"absent/nesolicitat"," ") " ... but how do I concatenate iy with " =IF([REPORT.xls]Project1!I3>0,"absent/nesolicitat"," ") " ... ?? .. I need them to go like this: .. :P ... To have one nice formula :d

[Bob Phillips]

Don't understand you.

[nedy_03]

Hello,

I found a solution .. I used this formula : =IF([REPORT.xls]Project1!H3<>"","not requested",IF([REPORT.xls]Project1!I3<>"","absent"," ")) ... now the problem is that at the end in AJ column I have to count the number of aparitions of "not requested" and "absent" values .. the issue is that a counta formula it caounting the empty cell too ... Ex : in AJ2 it shows 31 when it should be only 5 ...

Thx,
Nedy

[nedy_03]

The ideea is that I want just the values to be counted , not the formulas ...

[nedy_03]

Hi,

Another question, if I pull the fromula to the right I want it to go from H to P to V ... that would be to jupt from 5 to 5 cells .. any ideea how can I doo that??