If a hyperlink is in cell a3, how do you make the same hyperlink go into d3 simply entering "=a3" into cell d3

[jeremypyle]

I have the following in cell a3:

=IF(MacroColumn!D1=0,"",HYPERLINK(MID(CELL("filename"),SEARCH("[",CELL("filename")),SUM(SEARCH("]",CELL("filename")),-SEARCH("[",CELL("filename")),1)) &"'"& MacroColumn!D1 & "'!A1", MacroColumn!D1))

Now I want to make the same result go into cell b3. However, if in b3, I place the formula "=a3", it does not result in a hyperlink in a3. It simply lists the word from a3 without the hyperlink.

Is there a way to have hyperlinks, colour, text font etc also appear in cell b3?

This could be very easy or may be difficult. I'm not even sure what to search in order to find this answer. Hoping someone can help with a simple formula.

Thanks so much

 

[AlphaFrog]

Can you put the same formula in B3 that is in A3 ?

 

[jeremypyle]

Hi Alpha. Thanks for your recommendation. However, that won't be sufficient for what I am after as cell a3 is actually on a template page. Cell b3 is on a different sheet altogether. And if I make changes to the template sheet where a3 is located, I want the changes to occur on b3 of the different sheet.

Is there any formula that says "Get the formula instead of the value from a cell"?

All the formulas that I know simply grab the values from the other cell.

Instead of:
=JobTemplate!A3

Can I have it say, get the formula from JobTemplate!A3?

If there is anyway to do this, it would be very very much appreciated

 

[AlphaFrog]

A formula alone cannot get formatting, font color, or the formula from another cell. It could be done with VBA if that would be acceptable.

• Right-click on the sheet tab that has the A3 cell
• Select View Code in the pop-up menu
• Paste the code below in the VBA edit window
• Change the "Sheet2" in the code to the sheet name that has the B3 cell

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A3" Then
        Sheets("Sheet2").Range("B3").Formula = Target.Formula
    End If
End Sub

 

[jeremypyle]

Ok then. Thanks a lot for your help. However, I don't think this will work as there are multiple sheets (about 60) that will gather information from the template sheets. And from time to time, I will be renaming the sheets. These probably wouldn't rename in VBA.

Thank you so much anyway. What I had hoped was possible in excel probably isn't. Is there no code that grabs formulas instead of values?

For example something like: =formula(a3)?

Is there no code that does anything like this?

 

[AlphaFrog]

A formula cannot do it because the original GETFOUMULA would be replacing itself and it would no longer be able to get any new formula. A cell cannot have two formulas; the GETFORMULA and the result formula.

You could have a macro copy all hyperlinks or formulas from the template to all the other sheets.

 

[jeremypyle]

Ok then that at least answers my question. Thank you so much for your help again