How can I copy indirect function down, while incrementing rows

Question

I am using INDIRECT in the next formula and want to copy it down to the row underneath, changing the row it refers to:

=INDIRECT("'"&$B$1&"'!D3")

Where D3 should become D4, D5, etc

It is refering to dynamic sheets, using a dropdown menu

I found a couple of answers online, mostly using CELL("address",D3)), but they result in errors

Thanks

Answer 1

Thanks mate. Did some more research myself and came up with:

=INDIRECT("'"&$B$1&"'!D"&ROW())

For now, this works for me

Answer 2

=IF(ROW()=ROW($A$1), INDIRECT("'"&$B$1&"'!D"&ROW()), INDIRECT("'"&$B$1&"'!D"&ROW()-1))

This code will first check if the current row is the same as the row that is specified in cell $A$1.

If it is, then the code will use the INDIRECT function to reference the cell in the specified sheet and row.

If it is not, then the code will use the INDIRECT function to reference the cell in the specified sheet and the row that is one less than the current row.

To use this code, you need to enter it into a cell in your Excel spreadsheet.

Then, you can copy the cell down to the rows that you want to update.