Indirectly referencing a defined named range in a formula

I see multiple layers of problems in this approach. To give better advice it would be helpful to explain your overall problem--why do you have named ranges defined for each sheet, and why are you interested in ActiveSheet? The reason for these questions will be clear from my points below:

1) The Function will not automatically be reevaluated every time you change the active sheet. Functions are automatically evaluated if a range that is one its arguments changes. You can mitigate this somewhat by making the function Volatile, which will cause it to be reevaluated whenever any cell in the workbook changes, but I think it will still not reevaluate just by changing the active sheet.

2) Even if 1 were not true, I'm not sure how this function is useful, because any time you look at the sheet containing the formula, that sheet becomes the active sheet. So I don't know how you would ever expect a result that references a different sheet.

3) Even if 1 and 2 were not true, the function returns a String; it does not return a Range. So the result is not essentially the same as the formula you showed; it is the same as this:

Formula:

Please Login or Register  to view this content.

The correct VBA code for this would be

Please Login or Register  to view this content.

This assumes that you have defined a range name that matches each sheet name.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationicon.jpg below their name.