Complicated work! Any idea??

[craig@help]

I would be grateful for your help.

I have a programme for inputting dates of any works that has started 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/C (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, and have duration and defect on both then all the information goes to TAB 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 the 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 bathroom then goes to Kitchen only in TAB 2 TAB 2 DURATION& DEFECT, J4 Duration and N4 Defect.

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

Cheers

[Ken Johnson]

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

[craig@help]

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 number is 10 and min number is 1. so if we have kitchen and bathroom both 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

[Ken Johnson]

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

[Ken Johnson]

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

[craig@help]

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?!?!

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

Craig

[Ken Johnson]

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

[craig@help]

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


column aj (1) 12/12/06
column av(1) (BLANK)
column ax (1) (LEAVE IT BLANK)

column aj (2) 12/12/06
column av (2) N/A
column ax (2) 12/12/06

column aj (3) (BLANK)
column av (3) (BLANK)
column ax (3) (LEAVE IT BLANK)

column aj (4) N/A
column av (4) 12/06/06
column ax (4) 12/06/06

column aj (5) 12/12/06
column av (5) 15/12/06
column ax (5) 15/12/06

ok here we go.....

in this project that im currently on ...i need to set up formulas/function that on the column ax,(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?


Thanks

Craig

[Ken Johnson]

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

[craig@help]

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

Hi Ken,

Thanks for getting back to me, i have given this a run today and worked 'ok' but not quite what i wish for.

Why it worked 'ok'? In column AJ2 (internals jobs which are kitchen works, bathroom, rewire etc) and AV2(externals jobs which are roof works and others), and in column AX is to stated that the propery is overall completed.

The reason we set up internals and externals was mainly for on how many 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 I leave the internals job as a 'blank' which to stating that the work is to start or in progress and the externals jobs are completed, the column AX will show the date of when the external jobs,so bascially the property is not completed.

(It will not make sense to the other part of programme that I have set it up and this will confuse other members of staff who will have access to this programme.)

Is there any other way that if we input the date for the external jobs as completed but leave it blank for the internal job and in the column ax remind 'blank' unless the internal job is marked as 'N/A', the same if it was other way round if the internal jobs completed and external jobs 'blank' unless marked 'N/A'.

It seems to me that i'm going on and on and on.......but the more information that i give to you, the better you understand what i'm after.

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

Craig

[Ken Johnson]

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

[craig@help]

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

[Ken Johnson]

Hi Craig,

That's good news.

Good luck with the rest of it.

Ken Johnson

[craig@help]

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(AY3>0),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

[Ken Johnson]

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(AY3>0),AY3,"") doesn't need the OR part,
=IF(AY3>0,AY3,"") should work.

Let me know how it goes.

Ken Johnson

[craig@help]

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(AY3>0),AY3,"") doesn't need the OR part,
=IF(AY3>0,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

[Ken Johnson]

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

[craig@help]

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

[Ken Johnson]

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