Code to write to a cell on another sheet

Hi
New to VBA coding, and hoping for some direction please!

I have a Workbook with 4 sheets, which is basically a score card, 2 sheets for 2 teams competing. (Sheets named I1 to I4)

Basically, each team has 2 innings, and the match could end on sheet 3 or 4 depending if one team fails to beat the others score from 2 innings
Examples:
Scenario 1:
Sheet I1 - Team A scores 500
Sheet I2 - Team B scores 250. There is a threshold that if they fail to get within 200 they must compete again, so:
Sheet I3 - Team B scores 200. So, they still failed to match 500 adding the 2 scores together.
Sheet I4 in this scenario is now redundant as Team A won.
What I want to do in this case, is have some code, that checks if cell N15 on sheet I3 states the letter 'Y' (based on formulas on the sheet that have worked out the game is over), and if 'Y' is present, it writes 'Y' (also to denote game over) to cell N15 on sheet I4 (If that cell states 'Y' then other things are written to the sheet)

In Scenario 2:
Sheet I1 - Team A scores 250
Sheet I2 - Team B scores 300
Sheet I3 - Team A scores 350
Sheet I4 - Team B scores 250 BUT the game ended as a draw (complicated rules - the game is Cricket!!) as Team B were still 'In' at the end of the time period so I need to manually type 'Y' to Sheet I4 Cell N15 to denote match over and trigger formulae referencing this cell N15

So, a long winded explanation to basically say, how can I write Y to cell N15 on Sheet I4 based on manually writing 'Y' to Cell N15 on Sheet I3 !!

Hope that makes sense to someone!!

Thanks
Neil

Richard

I can't have a formula in N15 on Sheet I4 as I also need to be able to manually type 'Y' in certain scenarios

In Cricket, test match (which this is for), 2 teams have 2 innings. Team A bats (sheet I1), then Team B bats (Sheet I2), but if they don't get within 200 of the Team A's score, they 'follow on' and bat again, so Team B will be on Sheet I3 in this scenario.
If they were all out in their 2nd Innings and still failed to match Team A's score with their combined scores from 2 innings, the match is won by Team A and I would write Y to Cell N15 on I3, which then I want to trigger this event to write Y to N15 on sheet I4 to trigger other formulae to write results etc.

If Team B had surpassed the score of Team A with their combined scores, then Team A would bat again (Sheet I4), and N15 on I3 is ignored (I don't write to the cell), and I would then want to write to Cell N15 on sheet I4 once the game is over!!

Originally Posted by Richard Buttrey

Hello Neil,

This explanation reminds me of the well known humorous explanation of the rules of cricket to a foreigner

Can you upload the workbook with some typical results and indicate which are the results cells and some notes as to how you've arrived at them with reference to the contributory cells.

Since you can't use a cell for both a formula and manually overwriting it with a constant you will need to use a Sheet Change event macro.

Richard
Here is a sample of the workbook, using the scenario where Team A bats first, then Team B, who fail to meet the follow on target, so bat again, and also fail to match Team A with the combined scores, ending the match.
Thus the sheets are as follows:
I1 = Team A
I2 = Team B
I3 = Team B
I4 = Team A (unused due to the above conditions)

A 'normal' match would be
I1 = Team A
I2 = Team B
I3 = Team A
I4 = Team B

So, when you look, it's basically a score card for 4 innings of a Cricket test match.
My main objective when I posted, was in this scenario, on sheet I3, if the match has finished as described above, 'Y' is entered to Cell N15 on sheet I3 (coloured red), and I want to then write that 'Y' to Cell N15 on sheet I4 (coloured yellow).
Eventually I will use this to write results.

Having seen the 'What happens if it rains' comment, I might also want to add a 'Y' on either Sheet I1 or I2 if the match is abandoned because of weather.
So! Can the 'code' be used to check for a 'Y' in N15 on either Sheet I1, 2 or 3 then write it to N15 on I4

Test Match.xlsx

Originally Posted by Richard Buttrey

Hi,

I've added a small macro to the Workbook Sheet Change event and also named the N15 cells 'Over'

When you enter a Y in any of the N15 cells Sheet I4 will also be updated. If you delete a Y so too will Sheet I4 have N15 cleared.

Richard,

Many thanks.
At first it wasn't working, the first N15 cell I tried on Sheet I1 came up with a debug error when writing Y or deleting it.
I noticed that cell didn't have the name 'Over' like the others, and once added it fixed it

Thanks again.
Neil

Originally Posted by Norie

Neil

What if it rains?

Well, I will deal with that using the cell that I write 'Y' in for 'Game Over'

Richard

Can you upload the workbook with some typical results and indicate which are the results cells and some notes as to how you've arrived at them with reference to the contributory cells.

Since you can't use a cell for both a formula and manually overwriting it with a constant you will need to use a Sheet Change event macro.

I'll do that later so you can see where I am at