Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I test a range of cells for the presence of a border?
Excel 2007 / Vista
I have a scheduling worksheet showing individuals' names indicating in which area they will work on each day of the month. A subgroup of workers has their cumulative shifts assigned first to a "place holder name", and then each of those shifts is assigned to one of five individuals in the subgroup. As the individual assignments are made the cell changes color based on conditional fomatting for each name. However, I need to keep a count of the cells in each column that orriginally displayed the "place holder name". I thought I would assign borders to those cells before changing them to the individuals' names and then COUNTIF() the number of cells in each column that had borders (unchanged), but I can't find any way to count the number of cells in a range that have a particular border . |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I test a range of cells for the presence of a border?
If these five individuals are assigned only against the 'place holder' cells
then you need to count only for cells containing these names... Since you have only five names you can use Sumproduct like the one below; =SUMPRODUCT(--(A1:A100={"Name A","Name B","Name C","Name D","Name E"})) "PFB" wrote: Excel 2007 / Vista I have a scheduling worksheet showing individuals' names indicating in which area they will work on each day of the month. A subgroup of workers has their cumulative shifts assigned first to a "place holder name", and then each of those shifts is assigned to one of five individuals in the subgroup. As the individual assignments are made the cell changes color based on conditional fomatting for each name. However, I need to keep a count of the cells in each column that orriginally displayed the "place holder name". I thought I would assign borders to those cells before changing them to the individuals' names and then COUNTIF() the number of cells in each column that had borders (unchanged), but I can't find any way to count the number of cells in a range that have a particular border . |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I test a range of cells for the presence of a border?
Thanks for the suggestion. Unfortunately the "place holder" cells are
sometimes traded away to people outside of the 5 person share group and there are 30 people in total. I still need to be able to count the number of cell originally displaying the "place holder" even if some finally dislplay a non-share-group individual. "Sheeloo" wrote: If these five individuals are assigned only against the 'place holder' cells then you need to count only for cells containing these names... Since you have only five names you can use Sumproduct like the one below; =SUMPRODUCT(--(A1:A100={"Name A","Name B","Name C","Name D","Name E"})) "PFB" wrote: Excel 2007 / Vista I have a scheduling worksheet showing individuals' names indicating in which area they will work on each day of the month. A subgroup of workers has their cumulative shifts assigned first to a "place holder name", and then each of those shifts is assigned to one of five individuals in the subgroup. As the individual assignments are made the cell changes color based on conditional fomatting for each name. However, I need to keep a count of the cells in each column that orriginally displayed the "place holder name". I thought I would assign borders to those cells before changing them to the individuals' names and then COUNTIF() the number of cells in each column that had borders (unchanged), but I can't find any way to count the number of cells in a range that have a particular border . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I test a range of cells for the presence of a border?
On Nov 2, 10:08*pm, PFB wrote:
Excel 2007 / Vista I have a scheduling worksheet showing individuals' names indicating in which area they will work on each day of the month. *A subgroup of workers has their cumulative shifts assigned first to a "place holder name", and then each of those shifts is assigned to one of five individuals in the subgroup. * As the individual assignments are made the cell changes color based on conditional fomatting for each name. *However, I need to keep a count of the cells in each column that orriginally displayed the "place holder name". *I thought I would assign borders to those cells before changing them to the individuals' names and then COUNTIF() the number of cells in each column that had borders (unchanged), but I can't find any way to count the number of cells in a range that have a particular border . You'll need a UDF to access formats in a formula, at least for more than one cell. There is one in the morefunc group (search for it at download.com) that would work. Or someone with the skills could write a UDF. If you were to download this UDF and use it, this formula should work to count all cells with a bottom border: =SUMPRODUCT(--(XLM.GET.CELL(12,A2:A100)0)) I don't have xl2007, and it has apparently changed VBA a bit, so I can't vouch for this working in that version. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I test a range of cells for the presence of a border?
Hi,
This does not seem to work in Excel 2003 either. What am I doing wrong. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Spiky" wrote in message ... On Nov 2, 10:08 pm, PFB wrote: Excel 2007 / Vista I have a scheduling worksheet showing individuals' names indicating in which area they will work on each day of the month. A subgroup of workers has their cumulative shifts assigned first to a "place holder name", and then each of those shifts is assigned to one of five individuals in the subgroup. As the individual assignments are made the cell changes color based on conditional fomatting for each name. However, I need to keep a count of the cells in each column that orriginally displayed the "place holder name". I thought I would assign borders to those cells before changing them to the individuals' names and then COUNTIF() the number of cells in each column that had borders (unchanged), but I can't find any way to count the number of cells in a range that have a particular border . You'll need a UDF to access formats in a formula, at least for more than one cell. There is one in the morefunc group (search for it at download.com) that would work. Or someone with the skills could write a UDF. If you were to download this UDF and use it, this formula should work to count all cells with a bottom border: =SUMPRODUCT(--(XLM.GET.CELL(12,A2:A100)0)) I don't have xl2007, and it has apparently changed VBA a bit, so I can't vouch for this working in that version. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I test a range of cells for the presence of a border?
What's happening? It works for me. I guess the most obvious issues
might be: -Make sure you have installed morefunc. -Typos. -The "12" argument of XLM.GET.CELL checks the bottom border. A border could be a top border and appear like the bottom of the cell above, but would not be counted. There are arguments to check all 4 sides of cells for borders, the formula can be modified to suit. -You do have to force a manual calculation for this. Simply changing cells in the specified array will not re-calc this function, so hit F9. This is true of Excel's built-in CELL function, too, IIRC. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I test a range of cells for the presence of a border?
Thanks! This worked perfecly. I am a long-time but basic Excel user so I
had no idea that there was a source for UDF (which I had never heard of anyway). It took me a while to find them - hence the late return post. Interestingly, the new functions/macros only appear in the Fx drop-down listing on the ribbon, but not in the drop-down lists associated with the various category icons on the ribbon to the left of the Fx icon. "Spiky" wrote: On Nov 2, 10:08 pm, PFB wrote: Excel 2007 / Vista I have a scheduling worksheet showing individuals' names indicating in which area they will work on each day of the month. A subgroup of workers has their cumulative shifts assigned first to a "place holder name", and then each of those shifts is assigned to one of five individuals in the subgroup. As the individual assignments are made the cell changes color based on conditional fomatting for each name. However, I need to keep a count of the cells in each column that orriginally displayed the "place holder name". I thought I would assign borders to those cells before changing them to the individuals' names and then COUNTIF() the number of cells in each column that had borders (unchanged), but I can't find any way to count the number of cells in a range that have a particular border . You'll need a UDF to access formats in a formula, at least for more than one cell. There is one in the morefunc group (search for it at download.com) that would work. Or someone with the skills could write a UDF. If you were to download this UDF and use it, this formula should work to count all cells with a bottom border: =SUMPRODUCT(--(XLM.GET.CELL(12,A2:A100)0)) I don't have xl2007, and it has apparently changed VBA a bit, so I can't vouch for this working in that version. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I test a range of cells for the presence of a border?
This answer addressed another problem I was having, but I need some
clarification. I was able to make the SUMPRODUCT formula work just as you said until I changed the range to A9:U9 - Then I got the dreaded #N/A. As long as I left the range in a single column I was OK but when I changed it to a row - no luck. What am I doing wrong? "Sheeloo" wrote: If these five individuals are assigned only against the 'place holder' cells then you need to count only for cells containing these names... Since you have only five names you can use Sumproduct like the one below; =SUMPRODUCT(--(A1:A100={"Name A","Name B","Name C","Name D","Name E"})) "PFB" wrote: Excel 2007 / Vista I have a scheduling worksheet showing individuals' names indicating in which area they will work on each day of the month. A subgroup of workers has their cumulative shifts assigned first to a "place holder name", and then each of those shifts is assigned to one of five individuals in the subgroup. As the individual assignments are made the cell changes color based on conditional fomatting for each name. However, I need to keep a count of the cells in each column that orriginally displayed the "place holder name". I thought I would assign borders to those cells before changing them to the individuals' names and then COUNTIF() the number of cells in each column that had borders (unchanged), but I can't find any way to count the number of cells in a range that have a particular border . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test if a value is in a range of values | Excel Worksheet Functions | |||
Test within a range? | Excel Worksheet Functions | |||
Missing Blue Border in xml mapped range | Excel Discussion (Misc queries) | |||
test a range of cells and return an adjacent value | Excel Worksheet Functions | |||
Test for data in a range of cells | Excel Worksheet Functions |