Transpose of Data

Hi All,

I was hoping somebody would be able to help with an issue i am having at the moment.I am trying to transpose data from one spreadsheet to another using an index formula however, i am having issues.

(Please see attached image of example spreadsheet)

What i am hoping to do is transpose the information on Figure 1 to Figure 2.

When i try to get (Figure 1) No.1 Ref A for example to Ref A No.1 on Figure 2 it works no problem. The problem that does occur however, is for No.2 - No7 as it doesnt recognise the A from figure 1 it sees it as a blank cell. I was originally going to copy them over indiviually however, there is a vast amount of information and i want to use this exact sheet for other information of a similar standard.

I would be extrmeley grateful for your help as i have tried everything i know (which is limited). If no one knows a way of solving this i have another possible solution that i also would need your help with but its very complex and a bit of a pain.