Cross Workbooks Referencing Formula

[britzer]

Hi I have some formulas I need to build in Excel which are giving me a lot of headache....
The solution has to be in Excel formula and NOT in VBA.

Formula 1:
In B1
=Workbook.Sheet($A$1)
In B2
=Workbook.Sheet($B$1)
In B3
=Workbook.Sheet($C$1)
In B4
=Workbook.Sheet($A$2)
In B5
=Workbook.Sheet($B$2)
In B6
=Workbook.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 copy paste it manually
and when using a Table in Excel it doesn't give the right formulas if I let it populate the column by itself.

Formula 2:
In B1
=Workbook.Sheet($A$1)
In B2
=Workbook.Sheet($A$1)
In B3
=Workbook.Sheet($A$1)
In B4
=Workbook.Sheet($A$2)
In B2
=Workbook.Sheet($A$2)
In B3
=Workbook.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. Example File is attached.

[ufopilot]

I have attached a solution to your request, as I understand it.
AT least it will give you a head start on what you may be trying to accomplish