Direct and Indirect Referencing

Hi I have some formulas I need to build in Excel which are giving me a lot of headache....

Formula 1:
In B1
=Sheet($A$1)
In B2
=Sheet($B$1)
In B3
=Sheet($C$1)
In B4
=Sheet($A$2)
In B5
=Sheet($B$2)
In B6
=Sheet($C$2)

And so on. This I need to work in a quite big Sheet. Itīs about 30.000 rows so I don't want to do it manually.

Formula 2:
In B1
=Sheet($A$1)
In B2
=Sheet($A$1)
In B3
=Sheet($A$1)
In B4
=Sheet($A$2)
In B2
=Sheet($A$2)
In B3
=Sheet($A$2)

Almost the same as the above. Maybe an easy way to do but i cant figure it out.
Any help would be appreciated.

File describing the problem.

indirect.xlsx

Thanks a lot for the reply!

Although this is VBA which is ok. Can it be done in excel formulas though?
The reason why I'm asking is because the users of my template are not always
VBA experts and the solution has to be very simple to change and formulas done
in excel is simpler. It has to do with creating registers in an ERP which can vary
a bit depending on design of the data model.