Marco to change formula according to what is written in the cell

[Harn88]

Hi

Please help me; I need help with a Marco.

I want to be able to change a certain word in the formula according to what
is written in the cell.

For example:

If a formula in Cell A3 is as follow:

='Brisbane Metro'!G12

However if I wrote ‘Gold Coast†in Cell A1 it will change the above formula
in Cell A3 from

='Brisbane Metro'!G12

To

='Gold Coast’!G12

Thank you very much for your help!!!

Please let me know if you need more information

Best regards,

Harn

 

[Jacob Skaria]

You dont need a macro instead you can use INDIRECT()

Cell A3
=INDIRECT(A1&"!G12")

If this post helps click Yes

 

[joel]

Why not use Indirect?

from
='Brisbane Metro'!G12
to
=INDIRECT("'" & A1 & "'!G12")

 

[Harn88]

Dear Jacob

Thank you for your reply

='Brisbane Metro'!G12 is referring to a worksheet Tab, so "Brisbane Metro"
is a tab in the workbook. Gold Coast is also another tab in a workbook.

I want to be able to replace ='Brisbane Metro'!G12 tab to ='Gold Coast'!G12
tab and return the number in that tab.

In another word I want a Marco to replace the word 'Brisbane Metro' to 'Gold
Coast' According to what input in a cell for example A1.

I hope I explain it well, please let me know if you need more information

Thank you very much!

Best regards,

Harn

 

[Harn88]

Dear Joel

Thank you for your reply

='Brisbane Metro'!G12 is referring to a worksheet Tab, so "Brisbane Metro"
is a tab in the workbook. Gold Coast is also another tab in a workbook.

I want to be able to replace ='Brisbane Metro'!G12 tab to ='Gold Coast'!G12
tab and return the number in that tab.

In another word I want a Marco to replace the word 'Brisbane Metro' to 'Gold
Coast'! According to what input in a cell for example A1.

I hope I explain it well, please let me know if you need more information

Thank you very much!

Best regards,

Harn

 

[Jacob Skaria]

Please try the below

A1 = "Brisbane Metro"
A3 = INDIRECT("'" & A1&"'!G12")
now A3 should return the value of G12 from Tab "Brisbane Metro"

Now change A1 to "Gold Coast". Now the below formula will return the value
of G12 from Tab 'Gold Coast'.
A3 = INDIRECT("'" & A1 &"'!G12")


If this post helps click Yes

 

[joel]

with activesheet
Myformula = .Range("G1").formula
NewSheet = .Range("A1")

'remove old sheet name
Mid(Myformula,instr(Myformula,"!"))
'Add New sheet name to formula
'Note the 1st string is an double quote, colon, single quote, and double quote
'the last string is a double quote, single quote, double quote
Myformula = "='" & NewSheet & "'" & Myformula
'return new formula to worksheet
..Range("G1").formula = Myformula



end with