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

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
166
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
166
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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

Code:
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

Board Regular
Joined
May 30, 2011
Messages
166

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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

Board Regular
Joined
May 30, 2011
Messages
166
Ok then that at least answers my question. Thank you so much for your help again
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top