Code to write to a cell on another sheet

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