Obtaining data from a cell reference contained within a cell.

[Marvo]

Hi, in the shared workbook, I trying to complete a mini project (needed something to take my mind off the rugby).
Earlier in the week I was helped by several contributors which resulted in 4 cells of my workbook on sheet "All" resulting with a cell reference. I had mistakenly thought I could then use that cell to reproduce information from the same ROW either with XLOOPUP or maybe INDIRECT but I can't, the formulas just use the cell itself, not the information contained within.

So, in CELL H5571 I have a result of $B$5387 (I've realised that could just have been a ROW).

What I would like is a formula in I5571 that looks at the result in H5571 ($B$5387) and returns the value of AG5387. The correct result would be 8.
Then the same in J5571 that returns the value (date) of B5387. The correct result would be 02/03/2021.
The icing on the cake would then be to have a hyperlink in J5571 to take me to that cell.

I've linked a workbook to show what I have.

Cobblers.xlsx

Any help is as always appreciated.

 

[Peter_SSs]

What I would like is a formula in I5571 that looks at the result in H5571 ($B$5387) and returns the value of AG5387. The correct result would be 8.

Try

=INDIRECT("AG"&MID(H5571,FIND("$",H5571,2),7))

Then the same in J5571 that returns the value (date) of B5387. The correct result would be 02/03/2021.
The icing on the cake would then be to have a hyperlink in J5571 to take me to that cell.

Try

=HYPERLINK("#ALL!"&H5571,TEXT(INDIRECT(H5571),"d/mm/yyyy"))

 

[Marvo]

Wow. Thank you. That's absolutely spot on what I wanted. Brilliant.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.