how to change the sheet name in a formula by Macro

I am reluctant to use a macro for this. A macro should be made to give the result, not play supplicant to a worksheet formula. Please consider the solution demonstrated in the attached workbook.

You will see a worksheet each for a number of clubs, each with values in cells B4:B7. They are all listed by name in the worksheet "Lists". I used a named range there. I wrote about named ranges only the other day.

On the worksheet "Main" there is a data validation dropdown in A3: On the ribbon's Data tab select Data Validation. In the Allow field select List and in the Source field enter =Cubs. "Clubs" is the name of the named range set up on the List tab. You can enter the address of the range here if you don't want to name it. =List!$A$3:$A$7.

Finally, in Main!B3 there is a formula.

=INDIRECT(ADDRESS(4,2,1,1,A3))*INDIRECT(ADDRESS(5,2,1,1,A3))*INDIRECT(ADDRESS(6,2,1,1,A3))*INDIRECT(ADDRESS(7,2,1,1,A3))

=ADDRESS(4,2,1,1,A3) returns something like Dublin!$B$4 where Dublin is the value selected in A3. The numbers 4 and 2 represent row and column for B4. The two 1's determine the nature of the reference, in this case absolute. INDIRECT(ADDRESS(4,2,1,1,A3)) turns out to mean INDIRECT("Dublin!$B$4") then, which is identical in meaning to writing = Dublin!$B$4. In the attached workbook it's a number which can be multiplied with other numbers, and that is what the whole formula does. Change the selection in A3 to demonstrate how B3 changes.