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

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

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

If this post helps click Yes
---------------
Jacob Skaria


"Harn88" wrote:

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

Why not use Indirect?

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

"Harn88" wrote:

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

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

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


"joel" wrote:

Why not use Indirect?

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

"Harn88" wrote:

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

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
---------------
Jacob Skaria


"Harn88" wrote:

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

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

"Harn88" wrote:

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


"joel" wrote:

Why not use Indirect?

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

"Harn88" wrote:

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