Complicated work! Any idea??
 

I would be grateful for your help.

I have a programme for inputting dates of any works that has starte
and completed including a duration and defect.

However in this programme there are two tabs, 1= Master, 2= Duration
Defect,

TAB1= MASTER

Kitchen:
In Column K5 Duration – any number to input
In Column L5 Defect - any number to input

Bathroom;
In Column T5 Duration – any number to input
In Column U5 Defect - any number to input

(Bear in the mind, there may have N.W.R (no work require) or D/
(disclaimer) in either or on it own or both kitchen and bathroom)

TAB 2= DURATION & DEFECT

Kitchen & Bathroom
D4 Duration
H4 Defect

Kitchen Only
J4 Duration
N4 Defect

Bathroom Only
P4 Duration
T4 Defect.

I require formulas/functions to help me to make this work:

TAB 1 = MASTER both kitchen and bathroom works has been completed, an
have duration and defect on both then all the information goes to TA
2= DURATION& DEFECT, kitchen & bathroom = D4 Duration and H4 Defect.

If in TAB 1= MASTER , N.W.R or D/C in Kitchen but work completed in th
bathroom, then all the information goes to TAB 2 DURATION& DEFECT
Bathroom only P4 Duration and T4 Defect.

The same as above if kitchen work only and N.W.R or D/C on bathroo
then goes to Kitchen only in TAB 2 TAB 2 DURATION& DEFECT, J4 Duratio
and N4 Defect.

Make any sense to you? Let me know if this doesn't!!!

Cheer

--
craig@hel
-----------------------------------------------------------------------
craig@help's Profile: http://www.excelforum.com/member.php...fo&userid=3620
View this thread: http://www.excelforum.com/showthread.php?threadid=56316

Complicated work! Any idea??
 

Hi Craig,

Will this work"?

In Duration & Defect!D4...

=IF(AND(ISNUMBER(Master!$K$5),ISNUMBER(Master!$T$5 )),Master!$K$5+Master!$T$5,"")


In Duration & Defect!H4...

=IF(AND(ISNUMBER(Master!$L$5),ISNUMBER(Master!$U$5 )),Master!$L$5+Master!$U$5,"")


In Duration & Defect!J4...

=IF(AND(ISNUMBER(Master!$K$5),NOT(ISNUMBER(Master! $T$5))),Master!$K$5,"")

In Duration & Defect!N4...

=IF(AND(ISNUMBER(Master!$L$5),NOT(ISNUMBER(Master! $U$5))),Master!$L$5,"")

In Duration & Defect!P4...

=IF(AND(NOT(ISNUMBER(Master!$K$5)),ISNUMBER(Master !$T$5)),Master!$T$5,"")

In Duration & Defect!T4...

=IF(AND(NOT(ISNUMBER(Master!$K$5)),ISNUMBER(Master !$T$5)),Master!$U$5,"")


Hope I didn't stuff-up anywhere, it wasn't easy keeping track of what
goes where!

Ken Johnson

Complicated work! Any idea??
 

Ken Johnson Wrote:
Hi Craig,

Will this work"?

In Duration & Defect!D4...

=IF(AND(ISNUMBER(Master!$K$5),ISNUMBER(Master!$T$5 )),Master!$K$5+Master!$T$5,"")


In Duration & Defect!H4...

=IF(AND(ISNUMBER(Master!$L$5),ISNUMBER(Master!$U$5 )),Master!$L$5+Master!$U$5,"")


In Duration & Defect!J4...

=IF(AND(ISNUMBER(Master!$K$5),NOT(ISNUMBER(Master! $T$5))),Master!$K$5,"")

In Duration & Defect!N4...

=IF(AND(ISNUMBER(Master!$L$5),NOT(ISNUMBER(Master! $U$5))),Master!$L$5,"")

In Duration & Defect!P4...

=IF(AND(NOT(ISNUMBER(Master!$K$5)),ISNUMBER(Master !$T$5)),Master!$T$5,"")

In Duration & Defect!T4...

=IF(AND(NOT(ISNUMBER(Master!$K$5)),ISNUMBER(Master !$T$5)),Master!$U$5,"")


Hope I didn't stuff-up anywhere, it wasn't easy keeping track of what
goes where!

Ken Johnson

Hi Ken,

No you haven't stuff-up at all!!;)

I have tried what you have given me and it worked like a dream!!

One more thing, if it is possible, on the defect parts, the max numbe
is 10 and min number is 1. so if we have kitchen and bathroom bot
input 10, then it will add up 20, how do we get round that?

If unable, don;t worry, i see what i can do, but many thanks again.

Craig:

--
craig@hel
-----------------------------------------------------------------------
craig@help's Profile: http://www.excelforum.com/member.php...fo&userid=3620
View this thread: http://www.excelforum.com/showthread.php?threadid=56316

Complicated work! Any idea??
 

Hi Craig,

In Duration & Defect!H4...

=IF(AND(ISNUMBER(Master!$L$5),ISNUMBER(Master!$U$5 )),MIN(Master!$L$5+Master!$U$5,20),"")

might solve the maximum defects = 20 problem.

It's nice to know I got the main bit right. Thanks for the feedback.

Ken Johnson

Complicated work! Any idea??
 

Hi Craig,

Now I have stuffed it up!

Maximum defects is 10, not 20 so try...

=IF(AND(ISNUMBER(Master!$L$5),ISNUMBER(Master!$U$5 )),MIN(Master!$L$5+Master!$U$5,10),"")

Ken Johnson

Complicated work! Any idea??
 

Hi Ken,

This time, you haven't stuffed it up!!;)

Many thanks for your help, i'm grateful for your support on my
project......other thing...how did you work all those function stuff?
Are you connect to the pc?!?!:cool:

Anyway, may catch up with you again if i'm stuck.

-Craig-:)


--
craig@help
------------------------------------------------------------------------
craig@help's Profile: http://www.excelforum.com/member.php...o&userid=36201
View this thread: http://www.excelforum.com/showthread...hreadid=563166

Complicated work! Any idea??
 

Hi Craig,

Thanks for the feedback.

I'm happy to help when the need arises.
Good luck with the rest of your project.

Ken Johnson

Complicated work! Any idea??
 

Ken Johnson Wrote:
Hi Craig,

Thanks for the feedback.

I'm happy to help when the need arises.
Good luck with the rest of your project.

Ken Johnson

Hi Ken,

I would be grateful for your help once more again and hopefully the
last one!!

I will explain but if you could see below firstly......

COLUMN AJ INTERNAL WORKS COLUMN AV EXTERNAL WORKS COLUMN AX
OVERALL COMPLETED


1) 12/12/06 NO (LEAVE
IT BLANK)
2) 12/12/06 N/A
12/12/06
3) (BLANK) (BLANK) (LEAVE
IT BLANK)
4) N/A 12/06/06
12/06/06
5) 12/12/06 15/12/06
15/12/06

ok here we go.....

in this project that im currently on, and the one that you helped me
with...i need to set up formulas/function that on the column ax, if you
look at line 1) *internal works * a date has been input but not in the
*external works *then in the *overall completed *should leave it a
blank..

if in line 4) *internal works *input n/a and the *external works *has
date input then in the *overall completed *should have the date as
12/06/06



But if both *internal works *and *external works *has been completed,
then in the *overall completed *need to look for the last date input
e.g line 5) 15/12/06.......make any sense to what im' trying to
explain?:confused:


Thanks

Craig;)


--
craig@help
------------------------------------------------------------------------
craig@help's Profile: http://www.excelforum.com/member.php...o&userid=36201
View this thread: http://www.excelforum.com/showthread...hreadid=563166

Complicated work! Any idea??
 

Hi Craig,

Firstly, I have noticed a problem with dates. Excel always interprets
the cell input 12/12/06 to mean "December 12, 2006". By that I mean
Excel will always take the first part to be the month, second part the
day and last part the year. You can format the cells to DISPLAY the
date in a different order, however, that format does not affect the
required order when the date is typed into the cell. So, if the cell is
formatted as Date and Locale (location): = "English (Australia)" (I
live in Sydney Australia, where we put the day before the month) and if
I type 6/12/06, thinking it means December 6 2006, Excel still
interprets this to be June 12 2006 and the cell will display 12/06/06.
How's that for confusing!
Also, when you enter 15/12/06 you are obviously meaning the December 15
2006, but Excel couldn't give a damn and refuses to convert it to a
date and treats it as text, so it ends up aligned on the left side of
the cell like all other text inputs.

The simplest solution to this day/month confusion is to format all
cells with dates using one of the unambiguous options such as
14-Mar-06, which you should see in the list of date format types when
you go Format|Cells|Number tab|Date.

Try this formula in AX2, filled down as far as required...

=IF(UPPER(AJ2)="N/A",AV2,IF(UPPER(AV2)="N/A",AJ2,IF(ISNUMBER(AV2),MAX(AJ2,AV2),"")))

I've used the UPPER function so that you don't have to worry about case
sensitivity with the "N/A" inputs, you will get the same result with
"N/A", "n/a", "n/A" and "N/a"

Give it a thorough testing and let me know how it goes.

Ken Johnson

Complicated work! Any idea??
 

Ken Johnson Wrote:
Hi Craig,

Firstly, I have noticed a problem with dates. Excel always interprets
the cell input 12/12/06 to mean "December 12, 2006". By that I mean
Excel will always take the first part to be the month, second part the
day and last part the year. You can format the cells to DISPLAY the
date in a different order, however, that format does not affect the
required order when the date is typed into the cell. So, if the cel
is
formatted as Date and Locale (location): = "English (Australia)" (I
live in Sydney Australia, where we put the day before the month) an
if
I type 6/12/06, thinking it means December 6 2006, Excel still
interprets this to be June 12 2006 and the cell will display 12/06/06.
How's that for confusing!
Also, when you enter 15/12/06 you are obviously meaning the Decembe
15
2006, but Excel couldn't give a damn and refuses to convert it to a
date and treats it as text, so it ends up aligned on the left side of
the cell like all other text inputs.

The simplest solution to this day/month confusion is to format all
cells with dates using one of the unambiguous options such as
14-Mar-06, which you should see in the list of date format types when
you go Format|Cells|Number tab|Date.

Try this formula in AX2, filled down as far as required...

=IF(UPPER(AJ2)="N/A",AV2,IF(UPPER(AV2)="N/A",AJ2,IF(ISNUMBER(AV2),MAX(AJ2,AV2),"")))

I've used the UPPER function so that you don't have to worry abou
case
sensitivity with the "N/A" inputs, you will get the same result with
"N/A", "n/a", "n/A" and "N/a"

Give it a thorough testing and let me know how it goes.

Ken Johnson

Hi Ken,;)

Thanks for getting back to me, i have given this a run today and worke
-*_'ok'__*-but not quite what i wish for.

Why it worked '_-*ok'*-_? In column aj2 (internals jobs which ar
kitchen works, bathroom, rewire etc) and av 2(externals jobs which ar
roof works and others), and in column ax is to stated the propery i
overall completed.

The reason we set up internals and externals was mainly for on how man
internals and external jobs was completed in month march, april etc.

So with the function that you have give and i tried it out, and if
leave the internals job as a 'blank' which to stating that the work i
to start or in progress and the externals jobs are completed, th
column ax will show the date of when the external jobs,so bascially th
property is not completed.

-(It will not make sense to the other part of programme that i have se
it up and this will confuse other members of staff who will have acces
to this programme.)-

Is there any other way that if we input the date for the external job
as completed but leave it blank for the internal job and in the colum
ax remind 'blank' unless the internal job is marked as 'N/A', the sam
if it was other way round if the internal jobs completed and externa
jobs 'blank' unless marked 'N/A'.....it seem to me that i'm going o
and on and on.......*but* the more information that i give to you, th
better you understand what i'm after.

Anyway, thanks for your help again, (i wish i was in Australia, if
can only afford the flight!)

Craig:

--
craig@hel
-----------------------------------------------------------------------
craig@help's Profile: http://www.excelforum.com/member.php...fo&userid=3620
View this thread: http://www.excelforum.com/showthread.php?threadid=56316

Complicated work! Any idea??
 

Hi Craig,

You'll probably end up with two of this reply, last time didn't seem to
work, but it will probably turn up eventually.

Anyhow, try this...

=IF(UPPER(AJ2)="N/A",AV2,IF(UPPER(AV2)="N/A",AJ2,IF(AND(ISNUMBER(AJ2),ISNUMBER(AV2)),MAX(AJ2 ,AV2),"")))

Let me know how it goes, I'll get it right eventually.

Did my comment about inputting dates make sense?

Just to satisfy my curiosity, where are you based?

Ken Johnson

Complicated work! Any idea??
 

Ken Johnson Wrote:
Hi Craig,

You'll probably end up with two of this reply, last time didn't seem
to
work, but it will probably turn up eventually.

Anyhow, try this...

=IF(UPPER(AJ2)="N/A",AV2,IF(UPPER(AV2)="N/A",AJ2,IF(AND(ISNUMBER(AJ2),ISNUMBER(AV2)),MAX(AJ2 ,AV2),"")))

Let me know how it goes, I'll get it right eventually.

Did my comment about inputting dates make sense?

Just to satisfy my curiosity, where are you based?

Ken Johnson

Hi Ken,

That's the one i'm after! Yes it's finally worked.

Yes your comment about inputting dates make sense and I'm based at
West Bromwich, West Midlands, England (U.K).

Craig:)


--
craig@help
------------------------------------------------------------------------
craig@help's Profile: http://www.excelforum.com/member.php...o&userid=36201
View this thread: http://www.excelforum.com/showthread...hreadid=563166

Complicated work! Any idea??
 

Hi Craig,

That's good news.

Good luck with the rest of it.

Ken Johnson

Complicated work! Any idea??
 

Ken Johnson Wrote:
Hi Craig,

That's good news.

Good luck with the rest of it.

Ken Johnson

Hi Ken,;)

Can you help me with this one? I'm very grateful for your help over the
last few days, the programme is working pretty well.

Anyway, I have been asked to “do one more piece of work” on to the
programme that I’m still currently working on.

Here we go……

Kitchen (H3)
Bathroom Full (N3)& Part (P3)
Rewire Full (V3) & Part (X3)
Central Heating Full (AD3) & Part (AF3)
Roof (AM3)
Others (AR3)

So basically in column AX is -*=H3+N3+P3+V3+X3+AD3+AF3+AM3+AR3*-
This is for if any works first start/date input, it will state that the
property is now ‘in progress’.

However the above result will come up in number so I changed it by
going via ‘-*Format Cell’ ‘Number’ ‘Custom’ “Yes”;”Yes”;”No”’*-
This will enable us to count how many those properties are *-'in
progress' -*and those who are not.

The rest you have helped me with so I will type it out so you can see
what I’m after…..

Column AJ= Internal Handover
Column AV= External Handover

Column AY = Overall Completed
=IF(UPPER(AJ3)=”N/A”,AV3,IF(UPPER(AV3)=”N/A”,AJ3,IF(AND(ISNUMBER(AJ3),ISNUMBER(AV3)),MAX(AJ3 ,AV3),””)))

-Bear in your mind, the column AY is hidden as if in either AJ and AV
is typed “N/A”, there is number comes up ’00/01/1900’-


So I have added another column AZ = Overall Completed
=IF(OR(AY30),AY3,””)

So……what I would like is that when the ‘Overall Completed’ date (Column
AZ) is inputted (via AJ + AV), is that the AX become a ‘NO’ as the
property is now completed.

Does this make sense to you?

Craig:)


--
craig@help
------------------------------------------------------------------------
craig@help's Profile: http://www.excelforum.com/member.php...o&userid=36201
View this thread: http://www.excelforum.com/showthread...hreadid=563166

Complicated work! Any idea??
 

Hi Craig,

I'm struggling a bit...

Does this sound right?

You've got a formula in column AX eg AX3
"=H3+N3+P3+V3+X3+AD3+AF3+AM3+AR3"
and you would like column AX to show "NO" if AZ is not blank.

If I'm right then you could change the formula in AX3 to...


=IF(AZ<"","NO",H3+N3+P3+V3+X3+AD3+AF3+AM3+AR3)

If I've not got it right then one possibility is, if at all possible,
email a copy of the sheet to me. My account is a gmail account so it's
just kencjohnson before the @ and gmail.com after the @.

Also, the formula =IF(OR(AY30),AY3,"") doesn't need the OR part,
=IF(AY30,AY3,"") should work.

Let me know how it goes.

Ken Johnson

Complicated work! Any idea??
 

Ken Johnson Wrote:
Hi Craig,

I'm struggling a bit...

Does this sound right?

You've got a formula in column AX eg AX3
"=H3+N3+P3+V3+X3+AD3+AF3+AM3+AR3"
and you would like column AX to show "NO" if AZ is not blank.

If I'm right then you could change the formula in AX3 to...


=IF(AZ<"","NO",H3+N3+P3+V3+X3+AD3+AF3+AM3+AR3)

If I've not got it right then one possibility is, if at all possible,
email a copy of the sheet to me. My account is a gmail account so it's
just kencjohnson before the @ and gmail.com after the @.

Also, the formula =IF(OR(AY30),AY3,"") doesn't need the OR part,
=IF(AY30,AY3,"") should work.

Let me know how it goes.

Ken Johnson

Hi Ken,

After I sent my previous message ‘across the world’ to you, I started
to ‘play it around’ to find the function that I would like it to work.

However I have managed to find the function but it looks bit ‘long and
heavy to read’, by the way, I have changed around the columns just to
make easy to read with other information for the others…..here we go….

Column D
=IF(OR(J3+P3+R3+X3+Z3+AF3+AH3+AO3+AT3=0),IF(OR(J3 +P3+R3+X3+Z3+AF3+AH3+AO3+AT3=1),IF(OR(BA3=""),1, 0)))

It will show ‘FALSE’ so I have hidden it and use the next column E

Column E
=IF(ISNUMBER(D3),D3,0)


And it worked well; perhaps I sent you a copy of sheet to you?

Cheers

Craig;)


--
craig@help
------------------------------------------------------------------------
craig@help's Profile: http://www.excelforum.com/member.php...o&userid=36201
View this thread: http://www.excelforum.com/showthread...hreadid=563166

Complicated work! Any idea??
 

Hi Craig,

I think I'd better wait for you to email a copy.
That formula you said works breaks a lot of rules and makes very little
sense to me.

Ken Johnson

Complicated work! Any idea??
 

Ken Johnson Wrote:
Hi Craig,

I think I'd better wait for you to email a copy.
That formula you said works breaks a lot of rules and makes very
little
sense to me.

Ken Johnson

Hi Ken,

Check out your email when you get the chance.


Craig;)


--
craig@help
------------------------------------------------------------------------
craig@help's Profile: http://www.excelforum.com/member.php...o&userid=36201
View this thread: http://www.excelforum.com/showthread...hreadid=563166

Complicated work! Any idea??
 

Hi Craig,
Email received.

I'm in the process of trying to absorb what's there and what is needed.

I might still have to ask the odd question.

Meanwhile, leave it in my culpable, oops I mean capable, hands;-)

Ken Johnson