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

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

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