Offset and Indirect with Dynamic Named Ranges

Hi

I would be grateful for some help, I understand that OFFSET and INDIRECT do not work when using a dynamic named range, can you please with a solution.

Attached is a spreadsheet with the month-end prices for four stocks (worksheet 1), the second worksheet shows the monthly gain/loss for each stock and a table with the correlation of each.

When I use the CORREL function with a static named range (not dynamic), the INDIRECT works fine, although wont work with a dynamic range. i.e. =CORREL(INDIRECT(H$6),INDIRECT($G7))

Very frustrating!!!

Much appreciated

Nick