Dependant Data Validation and Relative Referrence in Macros

**DamienPalmer** · 02-05-2014, 12:33 AM

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

**:) Sixthsense :)** · 02-05-2014, 12:51 AM

Hi DamienPalmer,

Welcome to the forum

Can you please share your full code?

When you post code please Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**DamienPalmer** · 02-05-2014, 01:07 AM

Hi Sixthsense,

Thank you for the reply

The code for the data validation is as follows

Please Login or Register  to view this content.

The I82 is a reference to the following data validation

Please Login or Register  to view this content.

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

**:) Sixthsense :)** · 02-05-2014, 01:16 AM

Replace this

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

**DamienPalmer** · 02-05-2014, 12:57 PM

Hi Sixthsense,

This is what i have now

Please Login or Register  to view this content.

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

**:) Sixthsense :)** · 02-06-2014, 01:15 AM

Delete this....

Please Login or Register  to view this content.

**DamienPalmer** · 02-07-2014, 12:37 AM

Hi Sixthsense,

I tried deleting the bit of code you suggested and am left with this

Please Login or Register  to view this content.

When i try and run the macro it keeps giving me a Run-time error '1004'

Damien

**DamienPalmer** · 02-07-2014, 12:49 AM

Sorry, i thought i had put them in,

Please Login or Register  to view this content.

When i try and run the macro it keeps giving me a Run-time error '1004'

Damien

**:) Sixthsense :)** · 02-07-2014, 01:38 AM

Its working fine for me

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**FDibbins** · 02-07-2014, 01:45 AM

Damien, you actually did put them in, but you made a tiny error in syntax (no biggie, I have seen the same thing before)

You had [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] then your code, then ended with [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]

What you actually need is [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]
Theeasy way to do it is paste in your code, highlight it, then click the # button above (where the "bold/Italic/underline" etc is

**DamienPalmer** · 02-08-2014, 01:42 AM

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