Dependant Data Validation and Relative Referrence in Macros

Hi,
I am new to using Macros and data validation and have run into a problem i am hoping someone can help with.
I am trying to create a macro for estimating purposes that will contain a dependent data validation. The dependent validation will work if i use a direct reference in the second validation
=INDIRECT(SUBSTITUTE(I82," ","_"))
This allows my second selection based on the first.
The problem i have is i want this to run within a macro that can be inserted at any location in the sheet and so the I82 would need to be relative based on where i start. If i try
=INDIRECT(SUBSTITUTE(R[-15]C," ","_"))
I get an error as i am trying to run the macro.
How would be the best way to achieve this?
Thanks Damien

Hi Sixthsense,

Thank you for the reply

The code for the data validation is as follows

ActiveCell.FormulaR1C1 = "Pedestals"
    ActiveCell.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(SUBSTITUTE(I82,"" "",""_""))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Paver buildup"
        .ErrorTitle = ""
        .InputMessage = "Select build up type"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

The I82 is a reference to the following data validation

ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Pedestal Base"
    ActiveCell.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Pedestal Base,Aggregate Base"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Geotextiles"
        .ErrorTitle = ""
        .InputMessage = "Select textile type"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

The drop down list in the first code would change based on if aggregate base or pedestal base is selected from the menu set in the second code.
I would need to change the I82 to reference 15 rows up no matter where i start the macro.

Is this enough info, or do you need the code for the whole macro?

Thanks

Damien

Hi Sixthsense,

This is what i have now

ActiveCell.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(SUBSTITUTE(" & ActiveCell.Address = "R[-15]C" & ","" "",""_""))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Paver buildup"
        .ErrorTitle = ""
        .InputMessage = "Select build up type"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

The macro will run but the dropdown menu just displays FALSE. This is the same is i enter cell reference I82 in place of the R[-15]C

Damien

Hi Sixthsense,

I have attached the excel sheet with how it should look. It will work if i put an absolute reference in an run it from the same starting cell but i cannot run it from any where. There are some notes on the sheet to explain. Hope this helps!

Damien