Llibreoffice Calc, using INDIRECT to reference a sheet based on cell contents

Question

I'm trying to pull a number from another cell that is a component in the name of the sheet I'd like to reference.

    A                  B
1  001     =INDIRECT("$DH"&A1&".$C$3")
2  002     =INDIRECT("$DH"&A2&".$C$3")

I get #REF each time. What am I doing wrong?

The cell reference should look like this, if it's not clear...

$DH001.$C$3
$DH002.$C$3

I've read that ' are required in situations where the sheet name comes in its entirety from the other cell, perhaps if there are spaces in the sheet name. With that in mind, I also tried this, which also did not work...

=INDIRECT("'$DH"&A1&"'.$C$3")

I've also tried this...

=INDIRECT("$DH"&A1&"!$C$3")

And this...

=INDIRECT(CONCAT("$DH",A1,".$C$3"))

Answer 1

Well, it doesn't like the absolute reference to the sheet, which makes sense because the sheet reference is being built dynamically in the formula. An absolute reference makes no sense in this situation.

So getting rid of the $ in the sheet reference fixed it.

=INDIRECT("DH"&A1&"!$C$3")