Locking cells

[mills49]

I am working on a spread sheet for Baseball and I am wondering if someone can help with some formulas to achieve the outcome / results that I am after. I am new at all this and I will try to attach an attachment so that you can see what result I am looking for.

Thanks in Advance

Peter

[JBeaucaire]

I looked in your sheet and only found one question at the very bottom.

"How do I lock the cells only in the likes of Cell G4, I4, K4 and the like, so that I don't accidently erase them while putting data in cells alongside."

As such I corrected your thread title accordingly.


You can select the cells you DON'T want locked, press CTRL-1 to open the Format Cell wizard, go to the Protection tab and unlock them.

Then protect the sheet. Go to File > Permissions > Protect Workbook > Protect Current Sheet and that should do it. The unlocked cells will be accessible, the locked cells will not.

[mills49]

Hi JBeaucaire
Thanks for your reply, in fact there are several questions in amongst the list of explanations of how I am trying to get it to all work for me, but I must admit the questions are not really clear as I have put them on end of explanations.
I am working re wording them to help make it clearer as to what I am wanting to achieve.

Regards

Peter

[JBeaucaire]

Lots of white space here in the forum, post your questions here.

[sumesh56]

File > Permissions > Protect Workbook > Protect Current Sheet[/B] and that should do it. The unlocked cells will be accessible, the locked cells will not.

using excel 2007.

File > Permissions > Protect Workbook

where can i find the file tab in excel 2007? Only Home and Office buttons are available in excel 2007.

[JBeaucaire]

Press F1 and type 'Protect sheet' in the Help window, it will show you how to do things in your version of Excel.

[mills49]

Hi JBeaucaire
with reference to my attachment in an earlier post, one of my questions is :-

1. Cell P4 tells me that the first ball pitched is a HIT ~ Question is - can it be done so that, Firstly when I input a H into the Cell P4 can it change the input to show 'Hit' instead of just H . With only a 'H' changing the text all other inputs to have no affect on changing text.

2. Can the following be achieved :- Question is Cell H8 tells me that the batter was RO (Run Out) at 1st Base. Can it be set up so that if a batter is out by a RO (Run Out) C (Caught) K2 (Strike 3) can it send a message into Cell O8 for batter number 2 'Batter Out' and for Cell E7 insert the numeral '1' for the first batter out, numeral '2' if it's the 2nd batter, numeral '3' if it was the 3rd batter, NB Will require this feature for all batters.

That will do for now, more questions later, and I hope you can understand what I am after.

thanks

Peter

[JBeaucaire]

You can install a macro into that worksheet that watches for changes in certain cells and does specific things to the original entry cell. So to watch P4 and change what was entered into something else:

Please Login or Register  to view this content.

[JBeaucaire]

Part 2:

1) Put this formula in E4, then copy down to the other box cells in that column:

=IF(O5="Batter Out", COUNT($E$3:$E3)+1, "")

2) Unmerge O8:Z8

3) Select O8:Z8 and press CTR-1 to open the Cell Format wizard, go to the Alignment tab and select Horizontal > Center across selection

4) Put this array formula into cell O8:

=IF(OR(ISNUMBER(MATCH({"RO","K2","C"},$G5:$N5,0))),"Batter Out",
IF(ISNUMBER(MATCH("H",$G5:$N5,0)),"HOME SAFE", "OTHER"))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

5) Now highlight O8:Z8 and copy down to the other rows below.

[mills49]

Part 2:

1) Put this formula in E4, then copy down to the other box cells in that column:

=IF(O5="Batter Out", COUNT($E$3:$E3)+1, "")

2) Unmerge O8:Z8

3) Select O8:Z8 and press CTR-1 to open the Cell Format wizard, go to the Alignment tab and select Horizontal > Center across selection

4) Put this array formula into cell O8:

=IF(OR(ISNUMBER(MATCH({"RO","K2","C"},$G5:$N5,0))),"Batter Out",
IF(ISNUMBER(MATCH("H",$G5:$N5,0)),"HOME SAFE", "OTHER"))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

5) Now highlight O8:Z8 and copy down to the other rows below.

Hi JBeaucaire
I do not seem to be able to get it to accept item 1.

Regards

Peter

[JBeaucaire]

Format the cell as GENERAL first.

[mills49]

Format the cell as GENERAL first.

Thanks. that did the trick.

Peter

[mills49]

You can install a macro into that worksheet that watches for changes in certain cells and does specific things to the original entry cell. So to watch P4 and change what was entered into something else:

Please Login or Register  to view this content.

Hi JBeaucaire
I'm sorry but I have no idea how to activate / install / use a macro, I have never had anything to do with macros, can you give me a step by step procedure please.

Thanks

Peter

[mills49]

Part 2:

1) Put this formula in E4, then copy down to the other box cells in that column:

=IF(O5="Batter Out", COUNT($E$3:$E3)+1, "")

2) Unmerge O8:Z8

3) Select O8:Z8 and press CTR-1 to open the Cell Format wizard, go to the Alignment tab and select Horizontal > Center across selection

4) Put this array formula into cell O8:

=IF(OR(ISNUMBER(MATCH({"RO","K2","C"},$G5:$N5,0))),"Batter Out",
IF(ISNUMBER(MATCH("H",$G5:$N5,0)),"HOME SAFE", "OTHER"))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

5) Now highlight O8:Z8 and copy down to the other rows below.

Hi JBeaucaire
Is it possible to add to this formula please :- when in this case the batter in cell N5 has progress to home base safely. Cell N5 has data entered indicating a 'H' (for batter safely home) then can it be listed in Cell R3 as a run. Further to this can all 'H' (Home) be recorded in Cell P4 for this innings.

Regards

Peter

[JBeaucaire]

How/Where to install the macro:

1. Right-click the tab name and select VIEW CODE
2. Copy and Paste in your code (given above)
3. Get out of VBA (Press Alt+Q)
4. Save as a macro-enabled workbook (*.xlsm)

[JBeaucaire]

R3: =COUNTIF(G4:Z20, "H")

P4... no idea.

[mills49]

Hi JBeaucaire
I'm sorry but I have no idea how to activate / install / use a macro, I have never had anything to do with macros, can you give me a step by step procedure please.

Thanks

Peter

Hi Again
I think I have installed the macro, but it appears to be only working in Cell P4, is that right, I was wanting it to change all H entries in any cells in line 4 line 7 line 10 etc etc please

Regards

Peter

[mills49]

R3: =COUNTIF(G4:Z20, "H")

P4... no idea.

Hi JBeaucaire
just realized after setting up the formula for R3 that it needs to be modified so that is gains information from specific Cells, namely N5, N8, N11, N14, N17, N20, these cells only please

Regards

Peter

[mills49]

Hi Again
I think I have installed the macro, but it appears to be only working in Cell P4, is that right, I was wanting it to change all H entries in any cells in line 4 line 7 line 10 etc etc please

Regards

Peter

Has anyone any answers for the second part of my question above, can the formula be modified to change 'H' to 'Hit' if 'H' is inputed into any between cell G4 to Z4 and to include Cell G7 to Z7 and so on please.

I am very pleased with all the help received so far, thanks very much.

Peter

[mills49]

Hi JBeaucaire
just realized after setting up the formula for R3 that it needs to be modified so that is gains information from specific Cells, namely N5, N8, N11, N14, N17, N20, these cells only please

Regards

Peter

Hi All
After about 10 hours of trial and error l have managed to work this one out myself. I need help with the last one above though please.

Peter

[JBeaucaire]

If N5 and N8 (etc) have "H" in them, is there ever a possibility you would put "H" in N6 or N7?

[mills49]

If N5 and N8 (etc) have "H" in them, is there ever a possibility you would put "H" in N6 or N7?

Hi
Whoops Yes its possible that line N4 N7 N10 N13 etc could have a 'H' for a Hit. But I am trying to get it so that if I put in a 'H' for a 'Hit' it will change input to 'Hit'

Peter

[mills49]

You can install a macro into that worksheet that watches for changes in certain cells and does specific things to the original entry cell. So to watch P4 and change what was entered into something else:

Please Login or Register  to view this content.

Hi JBeaucaire
With reference to this macro can it be expanded to not only cell P4 but all cells on line 4 line 7 line 10 line 13 line 16 line 19 etc please.

Peter

[JBeaucaire]

One way:

Please Login or Register  to view this content.

[mills49]

One way:

Please Login or Register  to view this content.

Just a quick question or two before I install this macro - being very new to macros, this is my first, do I have to remove the original one you gave me, if so, how do l do that properly please.

Peter

[sumesh56]

Press F1 and type 'Protect sheet' in the Help window, it will show you how to do things in your version of Excel.

thanks for the response. have a nice day.

[mills49]

thanks for the response. have a nice day.

I'm not sure what it is your trying to say with your post, but if your telling me to use the Help section, because of the jargon language that is in the help that is written for highly experienced users, is the reason why l am asking for help on this forum, and its been a great help.

[JBeaucaire]

Yes, there can only be one Worksheet_Change event macro in a sheet module. Using the steps outlined in post #15 you would install the new macro the same way, being sure to replace the old worksheet_change macro completely.

[mills49]

Yes, there can only be one Worksheet_Change event macro in a sheet module. Using the steps outlined in post #15 you would install the new macro the same way, being sure to replace the old worksheet_change macro completely.

Good Morning JBeaucaire
Thank you very much for your help over the past few days, you have been a tremendous help and it's very much appreciated.

[mills49]

One way:

Please Login or Register  to view this content.

Hi JBeaucaire
I have struck a small problem, in that where you indicated that I can add all the rows and numbers that I want, how do I continue to the next line please as I have run out of length / room to continue on the one line to add in extra line numbers.

Peter

[JBeaucaire]

Maybe you're doing every 3rd row or something between a specific range of cells? Can you provide the full detail of the rows and the range, perhaps I can devise an alternate approach to get the same results without the full list.

[mills49]

Maybe you're doing every 3rd row or something between a specific range of cells? Can you provide the full detail of the rows and the range, perhaps I can devise an alternate approach to get the same results without the full list.

Hi JBeaucaire
I am using every 3rd row 9 times then the 10th has a space of 5 rows then back to 9 x 3 + 1 x 3 then back to 9 x 3 + 1 x 3 and so on. 1286, 1289, 1292, 1295, 1298, 1301, 1304, 1307, 1310, 1315, 1318, 1321, and so on.

Peter

[JBeaucaire]

Yeah, guess I won't be be getting a magical answer today on that.

Please Login or Register  to view this content.

[mills49]

Yeah, guess I won't be be getting a magical answer today on that.

Please Login or Register  to view this content.

Hi JBeaucaire
Sorry but l don't understand what your reply is telling me to do.

Peter

[JBeaucaire]

You said you were "running out of room on one line" for your CASE .... and I'm showing you how to add a spaceunderline to split a single line to multiple lines.

[mills49]

Hi JBeaucaire
Thanks for your help I have worked out what to do now to put the extra lines in.

Thanks again.

Peter