On Wednesday, 6 June 2018 11:31:32 UTC+1, Claus Busch wrote:
Hi Andy,

Am Wed, 6 Jun 2018 01:55:55 -0700 (PDT) schrieb Andy McNicol:

How it looks...

A B C D E F G H I J K L M..........
1 5 9 7

Using Indirect I get...

A B C D E F G..........
1 5 9 7

Which works as it only gives me the cells I need, however want it as follows;

A
1
5
9
7

in A1:
=INDEX(Sheet1!$1:$1,,ROW()*2-1)
and copy down.


Regards
Claus B.
--
Windows10
Office 2016

Thanks Claus,

Your help is much appreciated, now have it all in the same row which is good, however not quite there as getting the following results;

A
0
21
0
23
0
43
0
34

Also should have probably been clearer but want to retain the original data as well so no overwrite in A1. So for testing purposes added the formula into Cell A4, but for some reason it ignores the first 2 values in the row and still returns a 0 between the values.

Sorry to be a pain

Andy