Excel Magic Trick #171: MACRO Data Validation from 3 lists

Create a MACRO using Relative and Absolute cell references in order to create a Data Validation List from 3 separate lists on 3 separate (different) worksheets.
A good video to watch if you want to see the logic of when to use a Relative Macro and when to use a Absolute Macro.
Also see how to create a dynamic range using a named formula and the OFFSET function.
Also see how to add data Validation list Drop Down Arrow based on a dynamic range using a named formula and the OFFSET function.

Category:

Science & Technology

Tags:

• Excel
• MACRO
• Relative
• Absolute
• cell
• references
• Dynamic
• Range
• OFFSET
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[axy136]

Hi

Do you have any dedicated video series on basics of Visual Basics programming in excel

[ExcelIsFun]

I do not. But if you search YouTube, you can find many.

[6642633]

@ExcelIsFun

Thanks for video it was very helpful...

Regarding videos on VBA sure they are many on YouTube but unfortunately not consistent with teaching/coaching VBA for beginner , you may noticed a scattered topics that are not aimed at different tasks and not meant for beginner or new user of VBA

What Im aiming for and surly others readers is A dedicated series of videos for beginners in VBA but with proficient background in Excel like myself.

[ExcelIsFun]

I am no good with VBA and so I can not do it. The only video series like the one you want is for $49 from mrexcel[dot]com

[MSOplus]

How can we execute an existing macro through formula. For example if the cell value is true then execute macro xyz and if false then abc. please let me know any tutorial which is already posted otherwise help me to do this. Thank you

[ExcelIsFun]

Dear hapkman12,

For a slightly different approach, watch this video:

Excel Magic Trick # 259: Dynamic DV List Based On DV List

--excelisfun

[ExcelIsFun]

Dear hapkman12,

Yes, but when you use a 2007 Table with Data Validation, you would have to use the INDIRECT function and put the table name and field name in quotes, like this:

=INDIRECT("Table1[DataField]")

--excelisfun

[hapkman12]

well... I think that you created the dynamic range (with the offset function) to refresh the "All's sheet", so if I create a table, it can refresh the "All's sheet" too, but it would be easier.

Thanks for your attention

Sorry for my english, I'm from latinamerica and my english is not the best....

[ExcelIsFun]

Dear hapkman12,

What do you mean "same thing"? I am not sure what you are asking.

--excelisfun

[hapkman12]

Hi Excel is fun, thank you for upload this videos, I have learned a lot.

Can I do this same thing, with table, instead of dynamic range???.. thanks