Wayne's Wisdom
New Member
- Joined
- Jan 10, 2014
- Messages
- 8
I know it sounds like this has been covered but I have looked and I have found nothing.
I have created a speadsheet for a tournament that lists the team names down column A (leaving a heading row). I have a formula in the top row which copies each row heading to a column heading 2 columns wide (to be able to record a result) Formula: =INDIRECT(ADDRESS(ROUNDUP((COLUMN()+1)/2,0),1)). This way it will be possible to keep the results of the tournament on a grid. I would like to put in conditional formatting that meets 3 criteria...
1, it must fill every second row but only fill cells that have column and row headings (ie. rows that will have content in them which will be determined by the number of entrants). eg. if i have 10 teams then every odd row from row 1 to 11 will be filled. If 40 teams register then every odd row from 1 to 41 will be filled. 2, the fill needs to only fill cells that have column and row headings eg. if 10 teams register then every odd row from column 1 to 21 will be filled. Then 3, If the row heading and column heading are equal then it need to gray out or fill a different colour (or lock cell contents to empty if possible) but again, only fill cells that have column and row headings.
I have 2 rules. Rule 1: =(ADDRESS(1,EVEN(COLUMN()-1)))=INDIRECT(ADDRESS(ROW(),1)) PROBLEM: It needs to only fill cells that have a column and row heading which it doesn’t, it fills the correct cells untill there is no column heading then fills every other cell in columns beyond the last column with a heading. Rule 2: so far this works: =AND(MOD(ROW(),2)>0) but when i try to restrict it to cells with row and column headings it doesnt work. This is what I had: =AND(MOD(ROW(),2)>0,IsEmpty(INDIRECT(ADDRESS(ROW(1),COLUMN()))<>"")=FALSE) OR =AND(MOD(ROW(),2)>0,CELL("contents"<>"",ADDRESS(ROW(1),COLUMN()))) Please teach me!
Kindly, thank you in advance.
I have created a speadsheet for a tournament that lists the team names down column A (leaving a heading row). I have a formula in the top row which copies each row heading to a column heading 2 columns wide (to be able to record a result) Formula: =INDIRECT(ADDRESS(ROUNDUP((COLUMN()+1)/2,0),1)). This way it will be possible to keep the results of the tournament on a grid. I would like to put in conditional formatting that meets 3 criteria...
1, it must fill every second row but only fill cells that have column and row headings (ie. rows that will have content in them which will be determined by the number of entrants). eg. if i have 10 teams then every odd row from row 1 to 11 will be filled. If 40 teams register then every odd row from 1 to 41 will be filled. 2, the fill needs to only fill cells that have column and row headings eg. if 10 teams register then every odd row from column 1 to 21 will be filled. Then 3, If the row heading and column heading are equal then it need to gray out or fill a different colour (or lock cell contents to empty if possible) but again, only fill cells that have column and row headings.
I have 2 rules. Rule 1: =(ADDRESS(1,EVEN(COLUMN()-1)))=INDIRECT(ADDRESS(ROW(),1)) PROBLEM: It needs to only fill cells that have a column and row heading which it doesn’t, it fills the correct cells untill there is no column heading then fills every other cell in columns beyond the last column with a heading. Rule 2: so far this works: =AND(MOD(ROW(),2)>0) but when i try to restrict it to cells with row and column headings it doesnt work. This is what I had: =AND(MOD(ROW(),2)>0,IsEmpty(INDIRECT(ADDRESS(ROW(1),COLUMN()))<>"")=FALSE) OR =AND(MOD(ROW(),2)>0,CELL("contents"<>"",ADDRESS(ROW(1),COLUMN()))) Please teach me!
Kindly, thank you in advance.
