Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's the formula?
Excel 2003 on XP
Okay, here is the scenario. I am trying to create a workbook whereby text meeting a certain criteria is transfered from each of several worksheets onto a main worksheet. Example: On crew "alpha", which consist of 12 employees, John J is the only one absent. The crew leader puts a 1 next to his name in the absent column. On crew "beta", which consist of 10 employees, Mary B and Maria C are absent. The crew leader puts a 1 next to each individual in the absent column. How do I get those three names to show up in list format on Page 1? John J Mary B Maria C I used this formula on sheet one. ("N" is the absent column and "A" is the name) =IF(alpha!N5:N25,"0","alpha!a5:a25") Not only does this NOT work, but doesn't account for subsequent crews. What is the correct way to do this? -- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's the formula?
Hi,
Just a quick comment =IF(alpha!N5:N25,"0","alpha!a5:a25") Even assuming it did work what would you want it to do count, sum, average? For sum you could use =SUMIF(alpha!N5:N25,"0",alpha!a5:a25) Note also "0" is not legal in an IF statement try something like =IF(alpha!N5:N250,alpha!a5:a25,"") or look at =SUMPRODUCT(--(alpha!N5:N250),alpha!a5:a25) Also note that "alpha!a5:a25" would be considered a string (text) not a range, don't quote ranges except in INDIRECT. If this helps, please click the Yes button Cheers, Shane Devenshire "Rotata" wrote: Excel 2003 on XP Okay, here is the scenario. I am trying to create a workbook whereby text meeting a certain criteria is transfered from each of several worksheets onto a main worksheet. Example: On crew "alpha", which consist of 12 employees, John J is the only one absent. The crew leader puts a 1 next to his name in the absent column. On crew "beta", which consist of 10 employees, Mary B and Maria C are absent. The crew leader puts a 1 next to each individual in the absent column. How do I get those three names to show up in list format on Page 1? John J Mary B Maria C I used this formula on sheet one. ("N" is the absent column and "A" is the name) =IF(alpha!N5:N25,"0","alpha!a5:a25") Not only does this NOT work, but doesn't account for subsequent crews. What is the correct way to do this? -- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's the formula?
Shane,
Thanks for the interest in my problem. The Number of people absent from each crew sums to the Summary page quite well. On the crew page, using an "If" statement, I have been able to show the names of the absent people at the end of each row. I want to automatically list the names of the absent employees from each of the work crews onto the main Summary page. I have never used INDIRECT, but am trying it out. -- "Shane Devenshire" wrote: Hi, Just a quick comment =IF(alpha!N5:N25,"0","alpha!a5:a25") Even assuming it did work what would you want it to do count, sum, average? For sum you could use =SUMIF(alpha!N5:N25,"0",alpha!a5:a25) Note also "0" is not legal in an IF statement try something like =IF(alpha!N5:N250,alpha!a5:a25,"") or look at =SUMPRODUCT(--(alpha!N5:N250),alpha!a5:a25) Also note that "alpha!a5:a25" would be considered a string (text) not a range, don't quote ranges except in INDIRECT. If this helps, please click the Yes button Cheers, Shane Devenshire "Rotata" wrote: Excel 2003 on XP Okay, here is the scenario. I am trying to create a workbook whereby text meeting a certain criteria is transfered from each of several worksheets onto a main worksheet. Example: On crew "alpha", which consist of 12 employees, John J is the only one absent. The crew leader puts a 1 next to his name in the absent column. On crew "beta", which consist of 10 employees, Mary B and Maria C are absent. The crew leader puts a 1 next to each individual in the absent column. How do I get those three names to show up in list format on Page 1? John J Mary B Maria C I used this formula on sheet one. ("N" is the absent column and "A" is the name) =IF(alpha!N5:N25,"0","alpha!a5:a25") Not only does this NOT work, but doesn't account for subsequent crews. What is the correct way to do this? -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|