Copying a formula that requires a referenced cell in quotations across multiple cells

[bnbcat]

Hello,

I'm having trouble with a formula that references a cell, but it must be in quotes.
='PER1'!F28-INDIRECT(D16&"!F28")

It's kind of hard to explain in text, so bear with me!

I have 3 sheets with raw data, titled PER1, PER2, and PER3, with equivalent data for different time periods across the sheets.

I have a 4th sheet that will calculate the variance between a cell in PER1 and the same cell in whatever sheet is indicated in cell D16.

My issue is that the formula I have works great, but I have to apply this to literally thousands of cells, which I can't do easily because of the quotation marks.

Is there any workaround so I can apply this formula to mass cells?

Thanks

 

[mohadin]

Hi
Try something like this

='PER1'!F28-INDIRECT(D16&"!F"&ROW())

ROW() indicate the row number for the cell or range with the formula then you can add or subtract constant to achieve the desired value
So when you drag down row() will increments and you get what you want
Hope this helps