Conditioning one Calc cell on another [Solved/Closed]

Report
-
Posts
2700
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 25, 2021
-
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
2700
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 25, 2021
457
Hi Alan,

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


Best regards,
Trowa
Posts
1906
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
February 26, 2021
3,231
Thank you TrowaD for your always helpful excel knowledge!
Posts
2700
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 25, 2021
457
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
2700
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 25, 2021
457
Thanks David. Nice to be appreciated!
Posts
1906
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
February 26, 2021
3,231
You really are! I'm not too familiar with all of excels functions and its good having an expert on hand!
Posts
2700
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 25, 2021
457
Thanks :)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!