Within my workbook for all my worksheets whose name starts with "Series", I have a the same VLOOKUP formula in cell B2. What I would like to do is to create the formula in a VBA function, so if the range changes for any reason, I can just modify the formula in the VBA function module as opposed to having to modify it on each each.
I'm just not sure how to go about it. I know segments of code "Application.WorksheetFunc
tion.VLookup" and "Application.Volatile True" hold the key to making this happen. Here's a link to another forum that discusses this topic (http://www.ozgrid.com/forum/showthread.php?t=52372) and also this one (http://www.mrexcel.com/forum/showthread.php?t=59347), but I'm still not able to make heads or tails of how I apply it to my situation.
If you look at my attached worksheet and click on each "Series" worksheet, you'll see my VLOOPUP formula in cell B2. So instead of the VLOOKUP formula being there, it would be the function. Something like =VLKUPttls(). I'm just not sure how to make it happen.
Do any of you experts have any ideas?
SampleVlookup.xls
1) Rewrite the formula as =VLOOKUP(A2,Main!A:B,2,FAL
2) Create a named range, lookup_table, referring to =Main!$A$2:$B$5. If the range changes, all you need to do is modify the Name. The formula then becomes =VLOOKUP(A2,lookup_table,2
3) Make the Name itself dynamic by making lookup_tablae refer to:
=Main!$A$1:INDEX(Main!$1:$