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

[TMS]

Please post a sample workbook so that we don't have to create a structure and invent test data before trying to offer a solution.

Regards

[nemish]

Please post a sample workbook so that we don't have to create a structure and invent test data before trying to offer a solution.

Regards

Attached to OP

Thanks

[nemish]

ok got some help...

data is now coming over to next sheet ... just need to know how to stop making empty cells reference as 0's on the resulting sheet

=OFFSET(Sheet1!$A$2,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11))

[TMS]

Try:

=OFFSET(Sheet1!$A$2,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11)) &""

Note the ampersand double quote double quote at the end

Regards

[nemish]

Try:

=OFFSET(Sheet1!$A$2,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11)) &""

Note the ampersand double quote double quote at the end

Regards

I'm actually doing it without the ampersand at the end... what's the purpose of this?

Also because of blank cells my formula is this currently:


=IF(ISBLANK(OFFSET(Sheet1!$A$2,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11))),"",OFFSET(Sheet1!$A$2,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11)))

[nemish]

I'm actually doing it without the ampersand at the end... what's the purpose of this?

Also because of blank cells my formula is this currently:


=IF(ISBLANK(OFFSET(Sheet1!$A$2,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11))),"",OFFSET(Sheet1!$A$2,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11)))

I tried your , &"" and found it left justifies the data in the cells. Problem is last 3 columns are dates and on the resulting sheet I cannot change the formatting to Date now.

Is there something else it does that I missed? or reasoning it should be used?

[TMS]

Yes, I realised that. What it is doing is converting the values to text. Hence the issue with the dates in the last three columns. You could use that approach for the non-date values; that at least would be tidier.

Or you could get Excel to not display zero values. Or you could use Conditional Formatting.

Regards