Data Validation and Formula in Same Cell

[donaldh]

Hi,

I'm looking for a way to have a if formula and data validation in the same cell so if i have in A1 Peaches, Pears, Bananas, in a Data Validation is there a way that if none of them are selected i can have "Please Choose from the list of fruit in Drop Down" as a formula in the same cell. As i don't want to include it into the Data Validation.

Thanks

 

[jtakw]

Hi,

Highlight subject cell, select Data Validation, click Input Message tab, enter whatever you want.
When that cell is selected, the message will show.

 

[donaldh]

Hi,

I want the message to be in the cell itself and not a as pop message when the cell is selected.

 

[jtakw]

Then just enter the text/message you want directly into the cell Before you apply Data Validation.

 

[Peter_SSs]

Two other options to consider. In each case the DV cell of mine is coloured yellow to highlight it.

A. Insert a new row 1 so that your DV cell is now A2. Put the formula shown into A1 and then whenever the DV cell is empty, the message will appear.

Excel Workbook
	A
1	Please choose from the list of fruit in Drop Down below
2
DV A

#VALUE!

B. If you are able to use macros and can ensure that all users of the sheet will be enabling macros you could try this method. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim DVCell As Range
  
  Set DVCell = Range("A1")
  If Not Intersect(Target, DVCell) Is Nothing Then
    If IsEmpty(DVCell.Value) Then DVCell.Value = "Please choose from the list of fruit in Drop Down"
  End If
End Sub

For this scenario, you could initially populate the message in cell A1 before applying the DV (as suggested by jtakw, & as shown below) but then subsequently if a DV value is chosen but then cleared the message would re-appear (provided macros are enabled)

Excel Workbook
	A	B
1	Please choose from the list of fruit in Drop Down
2
DV B

#VALUE!