Autofill help accessing random cells on separate sheet reference

[nemish]

I'm looking to take a exported database that outputs in the format

A5 Customer Number
B5 Name
A6 Address1
A7 Address2
H6 Home #
H7 Work #
L6 First Date
L7 Last Date
H8 E-Mail
L8 Birthday

and repeats
A9 Customer Number
...

I want to reformat this on Sheet2
A1 Customer Number
B1 Name
C1 Address1
D1 Address2
E1 Home#
F1 Work#
G1 Email
H1 Birthday
I1 First Date
J1 Last Date

and repeat
A2 Customer Number

Since the formatting is all over the place I haven't been able to figure out a formula on Sheet 2 that will allow me to autofill and grab the customer information.
I've tried INDIRECT and INDEX with no luck.
I'm sure this has to be possible

Thanks

 

[nemish]

Yes, the way the formula is written makes sorting practically impossible, personal preference would be to use VBA but writing such a procedure is not one of my strong points.

If your data is static (i.e. the source isn't likely to change) you could use the method of copy > pastespecial > values.

Note that this method removes the underlying formula from the cells so any changes to the source wouldn't be reflected in the results, but it would allow you to sort the list.

Thanks. Figured the sorting was a limitation of the formula.
Nah cutting and pasting won't work... too much data and as clients get updated getting export of new data would be a pain to make sure all is up to date.

This method works just fine and most likely I will take the data formatted in the output sheet with the formulas and export as CSV... then import into database making the ClientID/ClientNumber the unique key value so importing the same list again will not result in duplicates

Thanks for all the help