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

Cosmos75 · Mar 13th, 2002, 01:53 PM

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

Cosmos75 · Mar 21st, 2002, 04:04 PM

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.

BabyTiger · Mar 21st, 2002, 04:21 PM

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)

BabyTiger · Mar 21st, 2002, 04:33 PM

Sorry again, should have check I am sending:

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

Cosmos75 · Mar 22nd, 2002, 09:49 AM

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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 01:53 PM	#1
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	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!!

Mar 21st, 2002, 04:04 PM	#2
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	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.

Mar 21st, 2002, 04:21 PM	#3
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	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)

Mar 21st, 2002, 04:33 PM	#4
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	Sorry again, should have check I am sending: ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Formula, 2, InStr(activecell.Formula, "!") - 2)

Mar 22nd, 2002, 09:49 AM	#5
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	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!