How to show which worksheet a cell is linked to? Return Valu

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
I have a spreadsheet with a summary page with a list of values that are links to various values on different sheets. Is there an excel formula or macro that I can use to list out which sheets the list of values are linked to. I want it to look like this

Column A Column B
3.45 Sheet 1
6.54 Sheet 2
1.23 Sheet 4
8.96 Sheet 9
4.54 Sheet 2
3.64 Sheet 3
3.33 Sheet 1

So, is there an Excel formula or Macro that can accomplish this?

While on the subject, is there anyway to make a macro/formula to look up a specific cell on a specific sheet. E.g,

Column A Column B
Sheet 1 D4

Is there a formula or macro that I can use so that the value in D4 on Sheet 1 in my workbook is put into Column C? How about a link to ‘Sheet 1’D4? That would be helpful.

THANKS!!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Got this code from NateO:

Sub showRng()
Dim cl As Range
Set cl = ActiveCell
Application.ScreenUpdating = False
For Each cell In Selection
ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Formula, 2, 255)
ActiveCell.Offset(1, 0).Select
Next
cl.Select
Application.ScreenUpdating = True
End Sub

It copies the formula in Column A, copies everything but the = (i.e.,the first character) and pastes it into the next column. So while it does show which sheet it came from, it also shows which cell. This'll work for me and probable for someone else, but I was hoping to only have the sheet name.

Consolidating all the info from different sheet into one sheet and then having a pivot table would work elegantly but I need to use a macro for my case.
 
Upvote 0
Hi,

Instead of NateO's following line:

ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Formula, 2, 255)

You might want to try this:

ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Formula, 2, InStr(Range("K11").Formula, "!") - 2)
 
Upvote 0
Sorry again, should have check I am sending:

ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Formula, 2, InStr(activecell.Formula, "!") - 2)
 
Upvote 0
BabyTiger,

Once again you've come through! THANKS!

I had tried this,

ActiveCell.Offset(0, 1).Formula = "=MID("&ActiveCell&",1,FIND(" & """"&"!"&""""&","&ActiveCell&",1)-1)"

to follow an example from the Excel Help file on using the MID, but again I think I screwed up on using the cursed "s. Or maybe the FIND formula doesn't work in VBA. Oh, well.. so much for trying to learn by trial and error.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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