how to change the sheet name in a formula by Macro

0

Hello Everyone

A small introduction: I have created a very basic Macro to get input from the user. I will use this input (it's a string) to select a specific worksheet in my workbook. 

This is my question: Will be possible with a Macro to change the worksheet name that is in a formula within a cell? I will give an example: 

This is the content of a cell right now:

=Variable_Bologna!B4*Variable_Bologna!B5+Variable_Bologna!B6*Variable_Bologna!B7

after the MACRO run I would that in specific cells in specific worksheets within workbook the formula can be seen like this:

=Variable_Dublin!B4*Variable_Dublin!B5+Variable_Dublin!B6*Variable_Dublin!B7

(the new name depends on the user input that I will map internally). 

I would avoid writing a script with all the cells where there is a formula to change it

Thanks

Regards 

Answer
Discuss

Answers

0
Selected Answer

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.

Discuss

Discussion

Hello Variatus

Thanks so much for your support. I have followed your suggestion and I have decided to avoid the Macro. I had no idea of this solution. 
Again, thanks 
santicuni (rep: 2) Mar 17, '18 at 4:48 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login