Formula on every second row

Question

I have a spreadsheet Sheet 1 called Client Tracking with data on each row but I need to put a formula on sheet 2 but I want the rows on sheet 1 to go continuously on sheet 2 but leaving every second row blank e.g row 1 goes to row 1 but row 2 on sheet 1 goes to row 3 on sheet 2.  I have it done for a good few rows but cannot copy it and have 500 rows on sheet 1 which means 1000 on sheet 2 and is too much to change all formula manually. example below

When I try highlighting the two rows on sheet 2 and use fill handle it skips a row from sheet 1; I get D9 then D11 then D13 etc in formula but it leaves the blank row and its driving me mad trying to solve it HELP PLEASE. I need D9, D10 D11 etc. as below.

Sheet 1 Named Client Tracking

	<br>A	<br>B	<br>C
9	Bloggs	Joe
10	Hand	Esther
11	Kelly	Brendan

Sheet 2 with linked cells

	<br>A	<br>B	<br>C
9	Bloggs	Joe
10
11	Hand	Esther
12
13	Kelly	Brendan
14

Correct Formula on sheet 2 with blank row between each entry but every second row linking to

continuous row on sheet 1

	<br>A	<br>B
9	=IF(ISTEXT('Client Tracking'!D9),'Client Tracking'!D9,"")	=IF(ISTEXT('Client Tracking'!E9),'Client Tracking'!E9,"")
10
11	=IF(ISTEXT('Client Tracking'!D10),'Client Tracking'!D10,"")	=IF(ISTEXT('Client Tracking'!E10),'Client Tracking'!E10,"")
12
13	=IF(ISTEXT('Client Tracking'!D11),'Client Tracking'!D11,"")	=IF(ISTEXT('Client Tracking'!E11),'Client Tracking'!E11,"")
14

Answer 1

Hi,

Try this

1. In cell A9 of sheet2, enter ='Client Tracking'!A9
2. Copy this formula down and to the right (column B)
3. In cell C9, enter 1
4. In cell C10, enter =C9+1 and copy down till where data is
5. Copy range C9 till last filled up cell in column C and Edit > Paste Special > values
6. Copy range C9 till last filled up cell in column C and paste it one cell below the last filled up cell in column C.  Essentially, all numbers in column C will appear twice
7. Select A9:C100 (Assuming 100 is the last filled up cell in column C)
8. Go to Data > Sort.  Ensure that "My data has headers" is unchecked
9. Sort the third column in ascending order
10. You may now delete column C

Hope this helps.

Answer 2

Hi,

This is a bit confusing. The sample data you have posted seems to be in columns A, B & C but your formula refer to Column D. However, this is what I thing you want.

Enter this formula in a cell and returns D9 from the Client Tracking sheet. Drag down and it returns a blank row, drag down again and returns D10

=IF(ISERROR(INDIRECT("'Client Tracking'!D" & ROW(A18)/2)),"",INDIRECT("'Client Tracking'!D" & ROW(A18)/2))

Change the D in the formula (2 places) to an E for column E

Answer 3

Try this in A9:

=IF(ISTEXT(INDIRECT("'Client Tracking'!D"&(0.5*ROW())+4.5)),INDIRECT("'Client Tracking'!D"&(0.5*ROW())+4.5),"")

Then select A9:A10 and drag down.

Cheers

Rich

[EDIT: As Mike says below, should it be referencing col A or D?  The above is to return from col D, change the two Ds as required.]

[EDIT": Mike's use of Row(18)/2 instead of my (0.5*Row())+4.5 may be more robust, for example if you decided to delete or add rows above A9 in sheet 2, (0.5*Row())+4.5 would no longer return row 9...]