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
174
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top