Hello. I have a workbook where the master sheet holds summaries of investment portfolios. Support worksheets are a yearly summary of closing values by day, updated daily.

In the master sheet, I have columns that calculate one week and one month returns for each security. The one week return formula (for security GLD) is

=GLD!$D$6/GLD$D$10 - 1

I would like to create a formula that references the name of the security's sheet from column E, so that the same formula can be propagated down the master sheet without having to manually edit the formula each time.

the following returns :#REF!

=INDIRECT("'" & E7 & "'!$D$6" & "/" & "'" & E7 & "'!$D$10 - 1")

Any suggestions?