Adding sums in rows and columns and colouring cells with condition
Hi all,
I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/M...UzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another row so i thought that if i could add an "a" and an "h" after the number of days in the cell i could set up a conditional format and a sumif, but i simply could not get this to work!! Is there another way...? I hope my problem is clear and would appreciate pointers to move ahead - and i hope it can be achieved simply in excel without getting into complicated vba etc as i am not a professional. thanks in advance. best manosh |
Adding sums in rows and columns and colouring cells with condition
I looked at the file, and the problem is made more complex by the fact that
you n ot only need to total both groups of days (home and away), but you need to do math with the numbers. So there's some issues with trying to deal with entries like 1.0H or 3.0A (which are text, and not numbers). I'd suggest going with the extra row to indicate H or A for the value in the row above or below it, then us SUMIF() in your formulas at the end of the rows containing the numbers based on the contents of the values in the associated row with the A/H entries. You can also use conditional formatting to set the shading based on those H/A entries. "Manosh" wrote: Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/M...UzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another row so i thought that if i could add an "a" and an "h" after the number of days in the cell i could set up a conditional format and a sumif, but i simply could not get this to work!! Is there another way...? I hope my problem is clear and would appreciate pointers to move ahead - and i hope it can be achieved simply in excel without getting into complicated vba etc as i am not a professional. thanks in advance. best manosh |
Adding sums in rows and columns and colouring cells with condi
Thanks J
I understand that this would be the easier solution but was hoping i might get lucky! "JLatham" wrote: I looked at the file, and the problem is made more complex by the fact that you n ot only need to total both groups of days (home and away), but you need to do math with the numbers. So there's some issues with trying to deal with entries like 1.0H or 3.0A (which are text, and not numbers). I'd suggest going with the extra row to indicate H or A for the value in the row above or below it, then us SUMIF() in your formulas at the end of the rows containing the numbers based on the contents of the values in the associated row with the A/H entries. You can also use conditional formatting to set the shading based on those H/A entries. "Manosh" wrote: Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/M...UzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another row so i thought that if i could add an "a" and an "h" after the number of days in the cell i could set up a conditional format and a sumif, but i simply could not get this to work!! Is there another way...? I hope my problem is clear and would appreciate pointers to move ahead - and i hope it can be achieved simply in excel without getting into complicated vba etc as i am not a professional. thanks in advance. best manosh |
All times are GMT +1. The time now is 10:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com