Referencing linked sources

You can retrieve the formula of a cell, like this:

http://www.mrexcel.com/forum/showthread.php?t=560414

... you could then manipulate that result as you wish.

Thank you so much, Glenn. Just one last question: what does the '6' represent in the formula? I keep receiving the "#NAME?" when I enter in the formula.

Thanks again!

GlennUK said:

You can retrieve the formula of a cell, like this:

http://www.mrexcel.com/forum/showthread.php?t=560414

... you could then manipulate that result as you wish.

Click to expand...

You must only use such formulas within defined names. The 6 is the reference number to return the formula of the referenced cell. See here for the full list of argument values:

http://www.mrexcel.com/forum/showthread.php?t=20611

Thank you, Glenn, but when I try to use that formula, I am given "FALSE" as my value in C1.

Is there an easier way to just get the location of another cell and then I could format it to be what I wanted in yet another cell?

In example, if the data I pull into cell A1 of Sheet 1 is "1234" which came from cell A1 of Sheet 2, would I be able to get cell A2 of Sheet 1 to display something like "Sheet2/A1" to tell me where I pulled my data in A1?

Then, would I also be able to format the data from this cell in A3 to be "S2/A1" or another abbreviated version of the actual file path?

Thank you~

GlennUK said:

You can retrieve the formula of a cell, like this:

http://www.mrexcel.com/forum/showthread.php?t=560414

... you could then manipulate that result as you wish.

Click to expand...

Have you written any VBA user defined functions before?

No, but I ended up finding a macro online:

Function CellFormula(c)
CellFormula = c.Formula
End Function

which allows me to reference where I am pulling my data from.

The only problem I have now is that instead of the reference reading "='2. Data'!B3," I would like the reference to simply be '2. B3'. Do you know of any way I could do this within the macro? Because in some cases I will be referencing more than one cell (i.e. 2.B3+2.B5), and cells with different digit lengths (B3 vs. B33), I cannot simply use a left/right formula.

Thank you!!

It depends on how you want to manipulate the references ... it looks like you want to remove the "=" and remove the "!" and remove the " Data" parts and remove single quotes. Does that cover what you want to do, exactly? If so, have a look at the Replace function in VBA, like: