Copying fomulae across columns but with gaps

**graeme27uk** · 02-23-2018, 07:28 PM

How do I copy a formula across columns A, C, E, G, etc but retain a refer going up A:A, B:B, C:C, etc...

At the moment if i drag the formula across the cells it jumps A:A, C:C, E:E, etc

I really dont want to manually have to enter each column ref as there are about 350 columns to do this on.

**jeffreybrown** · 02-23-2018, 07:30 PM

Did you use the absolute reference in your formula?

$A:$A

**graeme27uk** · 02-23-2018, 07:35 PM

I have this formula

=IFERROR(INDEX('lunch study'!$A:$A,SMALL(IF('lunch study'!D$2:D$31<>"",ROW('lunch study'!$B$2:$B$31)),ROWS(C$2:C3))),"")

I need the 'lunch study'!D$2:D$31 to go up sequentially. However because there is a gap between each column, the reference goes up in 2 columns rather than 1.

So the formula is in A1 for example, then it is copied into C1 but this means D become F not E...

**jeffreybrown** · 02-23-2018, 08:30 PM

Hi graeme27uk,

I'm still trying to find the correct solution, but I always struggle with this setup. If asked those who have a stronger formula knowledge than I to lend a hand.

**graeme27uk** · 02-24-2018, 03:33 AM

I have sort of solved this but not in an ideal way. Created a new sheet without the gaps and then used this as an array so that I can then use the INDEX function on the actual sheet itself. It works but is not that elegant and means I have another sheet for excel to churn through.

**AliGW** · 02-24-2018, 04:00 AM

Attach the workbook - someone may be able to help you to resolve this.

**Glenn Kennedy** · 02-24-2018, 11:05 AM

It would be easier to test with a sample workbook.... but do try:

=IFERROR(INDEX('Lunch study'!$A:$A,SMALL(IF(OFFSET('Lunch study'!D$2,,INT((COLUMNS($A:A)-1)/2),30,)<>"",ROW('Lunch study'!$B$2:$B$31)),ROWS(C$2:C3))),"")