|
please help me keep my hair.......
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. |
please help me keep my hair.......
Don't the ranges need to be of the same size...F6:F29 to E14:E27?
"izzyt1972" wrote: 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. |
please help me keep my hair.......
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" wrote: Don't the ranges need to be of the same size...F6:F29 to E14:E27? "izzyt1972" wrote: 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. |
please help me keep my hair.......
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" wrote: 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" wrote: Don't the ranges need to be of the same size...F6:F29 to E14:E27? "izzyt1972" wrote: 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. |
please help me keep my hair.......
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" wrote: 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" wrote: 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" wrote: Don't the ranges need to be of the same size...F6:F29 to E14:E27? "izzyt1972" wrote: 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. |
please help me keep my hair.......
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" wrote: 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" wrote: 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" wrote: 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" wrote: Don't the ranges need to be of the same size...F6:F29 to E14:E27? "izzyt1972" wrote: 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. |
please help me keep my hair.......
ok, thanks. I will have a go at that. thanks for your time.
"TWR" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: Don't the ranges need to be of the same size...F6:F29 to E14:E27? "izzyt1972" wrote: 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. |
| All times are GMT +1. The time now is 12:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com