Using the contents of a cell as a cell reference in a formula

This is going to seem really contrived so I really hope I can explain this well. I have thousands of nearly identical templates which I need to put into a single worksheet. When they're identical I've managed to work this out, and that's fine. There are a few hundred, however, which have an number of rows added at one point of the template (but the number of rows is different for each one). I worked out how to, for each template, work out how many rows have been added so now the only question is adding an amount to the cell reference of each subsequent cell by an amount equal to the number of rows added.

I managed to do this, and now the current situation I have is that on one sheet (Sheet19) I have cells filled with the cell references I need to refer to in a given template sheet (TEMPLATE SHEET). The sheet I'm filling in we will call Main Sheet

Sheet 19 right now looks like this:

NameofTEMPLATE SHEET D2 D4 D6 .... etc

So in Main Sheet I have a formula which reads the name of TEMPLATESHEET from Sheet19 then reads a given cell in that sheet. The formula I have used is: (as an example) =INDIRECT("'"&Sheet19!A2&"'!D40")

With the new rows, however, I need to change that D40 to the cell in Sheet19. I tried this but it did not work: =INDIRECT("'"&Sheet19!A2&"'!INDIRECT("'"&Sheet19!$A$1&"'!D2")") (where in A1 I have inserted "Sheet19" The idea of this formula is to look in Sheet 19, cell A2, which has the name of the template, then to search in Sheet19 for the name Sheet19 (convoluted), then within that search for the cell D2 in Sheet19 which contains a cell reference which I need).

Convoluted right? Absurdly poorly explained...

Honestly I would just upload the sheet so you guys can see but I'm not allowed unfortunately. Really appreciate any help though...

...correct in less than 5 minutes.

That was amazing. Thank you!

Quick follow up question. I want to be able to scroll downwards a good few thousand times so that the second formula is: =INDIRECT("'"&Sheet19!A3&"'!"&INDIRECT("'"&Sheet19!$A$1&"'!D3")) and the third formula is =INDIRECT("'"&Sheet19!A4&"'!"&INDIRECT("'"&Sheet19!$A$1&"'!D4")) etc.

When I'm scrolling down only the A is changing - any way to make both of them change?

Range? The first reference is in N6, and I'd like to pull it down to N1000 and something. I'm going to replicate the same formula (modified slightly) for each of O to AT...

I suspect that approach is very close to the right line, but it's not quite working. As far as I can tell with that it's taking A4 along with D2 and A5 with D3 etc.

If I remove the -2 would it keep the 4s with the 4s and 5s with the 5s etc?

Solved! You both are absolutely brilliant - thank you very much!