Conditioning one Calc cell on another
Solved/Closed
AlanEDunne
-
Feb 2, 2020 at 04:16 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 16, 2020 at 11:59 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 16, 2020 at 11:59 AM
Related:
- Conditioning one Calc cell on another
- Mtu calc - Guide
- Excel formula to subtract one cell from another - Excel Forum
- Based on the cell values in cells b77 - Excel Forum
- If cell contains date then return value ✓ - Office Software Forum
- Count if cell contains number - Excel Forum
4 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Apr 14, 2020 at 11:22 AM
Apr 14, 2020 at 11:22 AM
Hi Alan,
Forgot to translate the D/ formula:
=AND($A1<>"",$B1="")
Best regards,
Trowa
Forgot to translate the D/ formula:
=AND($A1<>"",$B1="")
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Feb 3, 2020 at 11:50 AM
Feb 3, 2020 at 11:50 AM
Hi Alan,
Open Office, not sure what works compared to Excel.
VBA code:
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
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”.
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”.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Apr 16, 2020 at 11:21 AM
Apr 16, 2020 at 11:21 AM
Thanks David. Nice to be appreciated!
David Webb
Posts
3177
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
May 15, 2023
6,926
Apr 16, 2020 at 11:28 AM
Apr 16, 2020 at 11:28 AM
You really are! I'm not too familiar with all of excels functions and its good having an expert on hand!
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Apr 16, 2020 at 11:59 AM
Apr 16, 2020 at 11:59 AM
Thanks :)
Apr 14, 2020 at 11:42 AM