Custom validation with formula

  • Thread starter Thread starter Hardy
  • Start date Start date
H

Hardy

Hi,

The below is possible I know, because I have seen it done, but it i
doing my head in.

Column A is maturity dates for bonds, column B has custom validatio
for user to specify maturity sector for bond (4 choices from custo
list). Initially, date will be blank (because I don't know exac
maturity date of bond to be issued), but maturity sector in Col B mus
be chosen (2-3y, 4-6y, 7-10y, +11y). All well and good.

However, when date is entered into cell column Ax, I want formula t
appear in cell Bx (x is row number) that will calculate maturity secto
for date and over-write initial user input from list (as actual may b
different from initial guess). Formula will be multiple IF statemen
working on maturity date minus today.

How do I achieve this? I cannot have formula permanently residing i
Col B. I am guessing it is in Custom validation.

I have a spreadsheet where this actually happens, but I cannot fin
anything in custom validation for either columns. Neither is ther
UDF. I need to replicate and adjust this in new spreadsheet.

This is my first post, so apologies if I am not clear. Thanks i
advance..
 
Hardy,

You could use the worksheet's change event. Copy the following code,
right-click on your sheet tab, select "View Code" and paste into the
window that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
With Cells(Target.Row, 2)
.Validation.Delete
.FormulaR1C1 = _
"=IF(RC[-1]=""This"",""That"",""Other"")"
End With
End If
End Sub


You'll just need to change the IF formula to suit your needs. If you
need help with that, post the conditions and the results that you are
looking for. Note that this code will remove the custom validation
from the cell to allow the use of the formula.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top