please help me keep my hair.......

[izzyt1972]

Happy new year to you all.

I have a worksheet ‘Job Sheet – Monday’! which has details of all work
carried out, listing all the different services done. I need to extract all
“Reg No†from “F6:F29†that have a company code of “1†in “C6:C29†and when
the corresponding cells in the job code column “V6:V29†are showing “Xâ€
These are to be listed in worksheet ‘Work Details – Monday’! with the “Reg
No†in “E14:E27 and the cells in “AA14:AA29†need to show a “1†when the
corresponding cell in “E17:E27†is populated. I also need to transfer other
services done on the same reg no from the first sheet to the second if the
same rules above apply with columns running up to “BS†but I want to get the
first part working before I sort that.

Thanks in advance for your help, I am quickly losing my hair over this.

 

[TWR]

Don't the ranges need to be of the same size...F6:F29 to E14:E27?

 

[izzyt1972]

No, I dont think so, as not all the services carried out in a day are to be
reported on. I am only interested in reporting on the final service of the
day in V6:V29, and nothing else. I could make them the same if it would be
easier.

 

[TWR]

If the ranges are the same size, thr placing this formula in WorkDetails Cell
E6
=IF('Job Sheet – Monday'!C6=1,IF('Job Sheet – Monday'!U6="x",'Job
Sheet – Monday'!F6,""),"")

The single ticks ' are needed if the worksheet name has spaces. Make sure
the "X" is the correct case ( UPPER or lower). Then you can select cell E6
and fill down to E29.

Then in the same worksheet, cell AA6 use =IF(E6<>"",1,"") meaning if the
value in E6 is not empty, place a 1 in AA6.

Make sense?

 

[izzyt1972]

That works wonders, but do you know how to make it so there are no gaps in
the rows on Work details - Monday? That takes the data across, but if there
are reg nos in F6 to F12, but only F6, F9, and F12 have an "X" in column V,
it leaves gaps where there is a reg no in F7, F8, F10 and F11. It would be
helpful if there was a "chooseif" but there isnt.

 

[TWR]

I'm sure there are many other ways to complete your task, but the only other
way I know of is to write some VBA Code to populate the Work Details sheet.

 

[izzyt1972]

ok, thanks. I will have a go at that. thanks for your time.