Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clicking on linked cells
Hi,
Does anyone know if there is a function or macro for Excel 2003 that will allow you to double click in a cell, which pulls data from another cell within the workbook (either on the same worksheet or a different one), and have it take you to the cell which is linked to the cell you are clicking on? does that make any sense? for example, if I am clicking on cell A1, which is showing the value $100, but the $100 is being pulled from cell A1 on another worksheet within the workbook, I want to be able to go to the other worksheet, and have my cursor be in cell A1 in that worksheet. I want to be able to do this for any cell within the workbook that is linked. At my previous job, the Excel software they provided for use had this functionality, but I can't do this on the current version of Excel I have at my new job, and it's driving me nuts. Please help. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clicking on linked cells
I suggest you insert the following code into the sheet's (the one
where you doubleclick) vba window: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim C2S As String If InStr(1, Target.Formula, "1") 0 Then Sheets(Replace(Left(Target.Formula, InStr(1, Target.Formula, "!") - 1), "=", "")).Select C2S = Right(Target.Formula, Len(Target.Formula) - InStr(1, Target.Formula, "!")) ActiveSheet.Range("A1").Select Cancel = True End If End Sub When you double click on a cell of this sheet, the above code searches the cell's formula, finds the refering sheet and refering cell and move the cursor there. It will work ONLY if all references are inside the same workbook, and ONLY if the cell you double click has plain reference to another cell, not calculations. For example it works for a formula reference like "=Sheet1!A3" and does not work if the formula is like "=SUM(sheet1!A1:A10), or like "=Sheet1!A3*10". Hope this helps! http://www.exciter.gr Custom Excel Applications and Functions On Nov 16, 10:51 pm, PT wrote: Hi, Does anyone know if there is a function or macro for Excel 2003 that will allow you to double click in a cell, which pulls data from another cell within the workbook (either on the same worksheet or a different one), and have it take you to the cell which is linked to the cell you are clicking on? does that make any sense? for example, if I am clicking on cell A1, which is showing the value $100, but the $100 is being pulled from cell A1 on another worksheet within the workbook, I want to be able to go to the other worksheet, and have my cursor be in cell A1 in that worksheet. I want to be able to do this for any cell within the workbook that is linked. At my previous job, the Excel software they provided for use had this functionality, but I can't do this on the current version of Excel I have at my new job, and it's driving me nuts. Please help. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clicking on linked cells
Another play to try ..
1. Install the sub ClearArrows() below into a regular module Press Alt+F11 to go to VBE Click Insert Module Copy n paste the code below into the whitespace Press Alt+Q to get back to Excel Sub ClearArrows() Dim s As Worksheet For Each s In ActiveWorkbook.Worksheets s.ClearArrows Next End Sub 2. Install the sub below into the sheet Right-click on the sheet tab View Code Copy n paste the code below into the whitespace Press Alt+Q to get back to Excel Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) Selection.ShowPrecedents ActiveCell.NavigateArrow TowardPrecedent:=True, _ ArrowNumber:=1, LinkNumber:=1 ClearArrows End Sub Repeat step 2 above to install into each sheet as required Test it out .. Eg: If installed into Sheet3, when you double-click on a cell with a link formula, say in A1: =Sheet2!B3, it will jump you to that precedent ie to Sheet2's B3 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "PT" wrote: Hi, Does anyone know if there is a function or macro for Excel 2003 that will allow you to double click in a cell, which pulls data from another cell within the workbook (either on the same worksheet or a different one), and have it take you to the cell which is linked to the cell you are clicking on? does that make any sense? for example, if I am clicking on cell A1, which is showing the value $100, but the $100 is being pulled from cell A1 on another worksheet within the workbook, I want to be able to go to the other worksheet, and have my cursor be in cell A1 in that worksheet. I want to be able to do this for any cell within the workbook that is linked. At my previous job, the Excel software they provided for use had this functionality, but I can't do this on the current version of Excel I have at my new job, and it's driving me nuts. Please help. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel linked values turn to #REF! after clicking don't update | Excel Worksheet Functions | |||
Linked Cells Staying With Cells Once Linked Workbook Update. | Excel Worksheet Functions | |||
Problem with clicking on cells | Excel Discussion (Misc queries) | |||
Adding contents of cells by clicking in Excel 2002 | Excel Discussion (Misc queries) | |||
Linked values turn to #REF! after clicking don't update | Excel Worksheet Functions |