Conditioning one Calc cell on another [Solved]

Report
-
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
-
Hello, does anyone know a way to make it so that a cell in Open Office Calc cannot be left empty, or at least that there will be an error message if it is, if the cell to the left has data entered?



System Configuration: Windows / Firefox 52.0

4 replies

Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
Hi Alan,

Forgot to translate the D/ formula:
=AND($A1<>"",$B1="")


Best regards,
Trowa
3
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2882 users have said thank you to us this month

Posts
1811
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
September 20, 2020
2,551
Thank you TrowaD for your always helpful excel knowledge!
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
Hi Alan,

Open Office, not sure what works compared to Excel.

VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
If Target.Value <> vbNullString And Target.Offset(0, 1).Value = vbNullString Then _
MsgBox ("Don't forget to enter the cell to the right.")
End Sub

When cell A1 is changed and contains data and cell B1 doesn't, then a message is displayed.



Data Validation:
Enter a Input Message (2nd tab) to show when cell is selected.



Insert Comment:
Right-click cell to add comment to cell.



Conditional Formatting:
Select B1 and choose to use a formula: =EN($A1<>"";$B1="")


This is what I could think of. Hopefully there is something you like and can use.

Best regards,
Trowa
Thank you for your suggestions

A/ The only reference to VBA in the Open Office Help is to “special basic modules” for code imported from Microsoft Office
B/ I have done experimentally and it works
C/ I have done experimentally and it sometimes works
but both B/and C/ give the message earlier than I want, on entering rather than leaving the first cell
D/ I also could not make D/ work in Calc

I tried A/ and D/ in an experimental Excel 2007 spreadsheet but could not make either of them work either. There seems to be no function called “EN”.
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
Thanks David. Nice to be appreciated!
Posts
1811
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
September 20, 2020
2,551
You really are! I'm not too familiar with all of excels functions and its good having an expert on hand!
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
Thanks :)