Jump to Formula-source-cell

Hello,

I have spent today searching and have seen LOTS of messages about hyperlinks, but I have not been able to understand enough to adapt/create my own answers

I have a workbook with many sheets, an each sheet there are several/many formulae which dispaly values from other sheets within the same workbook.
eg =WeightHistory!B189
In a cell next to the formula I have manually added hyperlinks to the formula's-data-source.

The workbook is now cluttered with my hyperlinks; which also break down whenever I add a new row or column.
I have been reading about Dynamic-hyperlinking, but have not been able to get it to work;
Perhaps hyperlinking is a wrong approach?

I am looking for a way to:-
Click on the cell containing the formula, and then auto-jump to the source-data of the cell/formula?

or to:
select a cell; and run a macro shortcut which would check the formula within the active cell and then jump to the source-data.

or to:
create a hyperlink formula which would read the formula of the cell next to it, and provide a hyperlink to that formula's-data-source

can anyone help?

(Is is possible to add a macro-shortcut to the mouse's right-button-click menu?)

Thank
Mark

Re: Jump to Formula-source-cell

Quote from StephenR;662105

Have you considered Trace Precedents in the formula menu?

Thank you for your reply....
Yes I had considered Trace Prededents, but as the formulas refer to cells within other worksheets, all I get is a line-&-little-Grid symbol.

I can't seem to click onto the grid to jump to the source data ... am I supposed to be able to?

Also; I have scores of inter-worksheet-formulae that I want to jump between. After a few jumps Tracing precedents would clutter the display with blue lines

any other suggestions?

Regards
MArk

Re: Jump to Formula-source-cell

Hello Stephen

THANK you for your reply. I like very much the way it begins when I double click on a cell.

Unfortunately I have not been able to make it work properly as it crashes at line "goto sheets" with an "run time error 9"

I attach a cut-down version of my much bigger spreadsheet, as an example of what I am trying to achieve .... On the summary sheet I would like to be able to select a cell, then jump to the data source. Is there something in the way I have designed my data and worksheet names which is causing your suggested code to fail?

Thanks for your attention
Mark

Re: Jump to Formula-source-cell

Quote from cytop;662611

Try, in the DoubleClick event handler:

Code

.Goto Sheets(Mid(v(0), 3, Len(v(0)) - 3)).Range(v(1))

Thank you,

This works with my example sheet, and shows that what I am looking for can be done

However this particular code appears to only work with the particular worksheet names in my example workbook. I am hoping that there is a way to generalise the code, so that I can copy it from one worksheet to another and into other workbooks which have different sheet names.

It would be so clever to be able to double-click a formula and then jump to the data-source of the formula. Any further ideas welcomed.

meanwhile as a different way to achieve something similar:
I have looked at the =hyperlink function
and it seems to me that I should be able to write a hyperlink which referenced a formula in a different cell, and would jump to the data source of the different cell ....

eg
if cell A1 is ... ='. (DifferentSheet)'!M1
and cell B2 is something like ... =hyperlink(___A1___,"linkOfUp")

I would like to be able to
* click the hyperlink in B2, and jump to cell M1 on DifferentSheet
* copy & Paste the formula in cell B2 and for the pasted cell to always create a hyperlink to the data-source referenced in the cell above

what is the real formula that I need in cell B2?

Thank you for your attention
Regards
Mark

Re: Jump to Formula-source-cell

Quote from soulfire21;662740

You can do this:
=HYPERLINK("[Book1]Sheet2!M1","Go To M1")

The Book1 references the Workbook you'd like to go on, Sheet2 the sheet, and M1 the cell. The text "Go To M1" appears in the cell that contains the HYPERLINK formula.

You must include the entire reference within quotations, for example: "[Workbook1]Sheet1!A1" will jump to Workbook1, Sheet1, Cell A1 when clicked.

Thank you for your reply,

If I use this method I think I need to separately create each hyperlink, which is what I do at the moment.

Is there a way to generalise the formula?
I would like the hyperlink to goto a location based on the formula in the cell to the Left/Right/Up/Down of the hyperlink-cell

Regards