Blanks problem

[hbomb]

hi all,
I need some help with a 'blanks problem

I have a sports workbook that operates as follows:
If I enter data (scores) into sheet 3, sheet 2 (fixtures & results) will sort the data into its respective fixtures and sheet 1 (League table) is automatically updated. I have created a 4th sheet (Postponed) that replicates sheet 2.

On sheet 4, I'd like the results cells to return a 'P' value ONLY if one score is entered of a particular fixture and left blank if nothing is entered in either (if both scores are entered, the results will show the scores as if the fixture has been completed)
The problem I'm having is that ALL cells in a selected range are showing a 'P' even though no data has been entered in sheet 3

Is there any way I can overcome this problem?

regards,
H

Example of how I want Sheet 4 to look in relation to the relevant score scenarios recorded on sheet 2

<table rules="all" style="; background-color: #FFFFFF; border: 1px solid; border-collapse: collapse; border-color: #BBB; color: #000;" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th width="16">
</th><th width="68">A</th><th width="35">B</th><th width="35">C</th><th width="68">D</th><th width="68">E</th><th width="69">F</th><th width="41">G</th><th width="35">H</th><th width="66">I</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;" width="68">Sheet 2</td> <td style="font-weight: bold;text-align: center;;" width="35">
</td> <td style="font-weight: bold;text-align: center;;" width="35">
</td><td style="font-weight: bold;text-align: center;;" width="68">
</td><td style="font-weight: bold;text-align: center;;" width="68">
</td><td style="font-weight: bold;text-align: center;;" width="69">Sheet 4
</td> <td style="font-weight: bold;text-align: center;;">
</td> <td style="font-weight: bold;text-align: center;;">
</td> <td style="font-weight: bold;text-align: center;;" width="66">
</td></tr> <tr> <td style="color: #161120;text-align: center;">2</td> <td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="68">
</td> <td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">
</td> <td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">
</td> <td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="68">
</td> <td style="font-weight: bold;text-align: center;;" width="68">
</td> <td style="font-weight: bold;text-align: center;;" width="69">
</td> <td style="font-weight: bold;text-align: center;;">
</td> <td style="font-weight: bold;text-align: center;;">
</td> <td style="font-weight: bold;text-align: center;;" width="66">
</td> </tr> <tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="68">Team 1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="68">Team 2</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;" width="68">THEN</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="69">Team 1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="41">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="66">Team 2</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;;" width="68">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="69">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="41">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="66">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;;" width="68">
</td><td style="font-weight: bold;text-align: center;;" width="69">
</td><td style="font-weight: bold;text-align: center;;" width="41">
</td><td style="font-weight: bold;text-align: center;;" width="35">
</td><td style="font-weight: bold;text-align: center;;" width="66">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="68">Team 1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">
</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">2</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="68">Team 2</td><td style="font-weight: bold;text-align: center;border-left: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;;" width="69">
</td><td style="font-weight: bold;text-align: center;;" width="41">
</td><td style="font-weight: bold;text-align: center;;" width="35">
</td><td style="font-weight: bold;text-align: center;;" width="66">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;" width="68">OR</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;;" width="68">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="69">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="41">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="66">
</td></tr> <tr> <td style="color: #161120;text-align: center;">8</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="68">Team 1</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">1</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">
</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="68">Team 2</td> <td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;" width="68">THEN</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="69">Team 1</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="41">P</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">P</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="66">Team 2</td> </tr> <tr><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;;" width="68">
</td><td style="text-align: center;border-top: 1px solid black;;" width="69">
</td><td style="text-align: center;border-top: 1px solid black;;" width="41">
</td><td style="text-align: center;border-top: 1px solid black;;" width="35">
</td><td style="text-align: center;border-top: 1px solid black;;" width="66">
</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="68">
</td><td style="font-weight: bold;text-align: center;;" width="68">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="69">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="41">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="35">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;" width="66">
</td></tr> <tr> <td style="color: #161120;text-align: center;">11</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="68">Team 1</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">
</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">
</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="68">Team 2</td> <td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;" width="68">THEN</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="69">Team 1</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="41">
</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="35">
</td> <td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;" width="66">Team 2</td> </tr> </tbody> </table>

 

[tigeravatar]

Assumptions for formula:

• 'Sheet 2' and 'Sheet 4' data have headers so data starts in row 2
• In 'Sheet 2' and 'Sheet 4':
  • Team 1 is in column A
  • Fixture 1 is in column B
  • Fixture 2 is in column C
  • Team 2 is in column D

In 'Sheet 4' cell B2, use the following formula:
=IF(AND('Sheet 2'!$B2<>"",'Sheet 2'!$C2<>""),'Sheet 2'!B2,IF(AND('Sheet 2'!$B2="",'Sheet 2'!$C2=""),"","P"))

Fill to column C and copy down

Hope that helps,
~tigeravatar

 

[hbomb]

tigeravata

Many thanks for your reply...that has solved my problem

thanks again.