Pull cell from formula

Hello,
I have a spreadsheet with two worksheets which refer to each other in formulas.
What I want to do is find the title of the column of which each formula refers.

For example, I might have sheet 1 and 2. In sheet 2 I have a formula that refers to cell A12 in sheet 1, which contains a table of data. I want to, in another cell in sheet 2, pull out the title at the top of column A.

The formulas are sporadically spread around the pages, not following any pattern and the data in sheet 1 is not unique, so lookups wouldnt necessarily work. I need to literally pull out the cell coordinates from the formula in sheet 2.

=F64*'Sheet1'!$G$11

Now, I can pull out the whole formula as a string using VBA, is it possible to just pull out the cell coordinates?

Hmm, quite a complicated explanation so sorry if its confusing, but I hope someone can help me.

Thanks!

Steven

Nice thanks, so that gives me the value $A$12 (for example) as text.
How can I put this into a formula for use with some kind of offset? for instance, now I have $A$12 as text, I want to pull the value from $A$8 because this is where the title of that column is. This means I'll need to pull the cell value as you have told me and put this is a formula with an offset in order to pull the column title.

Thanks for the help and quick reply!

Steven