Copy downwards but horizontal referencing

**Xx7** · 06-23-2011, 01:06 PM

Please see attached worksheet.

**NBVC** · 06-23-2011, 01:13 PM

A few ways... here are a 3...

1. =VLOOKUP(A20,$A$1:$C$6,3,FALSE) copied across

2.=SUMIF($A$1:$A$6,A20,C1:$C$6)

3. =INDEX($C$1:$C$6,MATCH(A20,$A$1:$A$6,0))

**Xx7** · 06-23-2011, 01:38 PM

Ok, those work. But, I'm looking for a simpler way to do this so that the formulas in row 21 are exactly like the ones in the workbook.

I would like to copy row 20 and paste it to row 21. but instead of the formulas in row 21 being A2, A3, A4...etc; I would like them to be C1, C2, C3..etc: Is there a way to copy and paste the formulas using transpose or something?

**NBVC** · 06-23-2011, 01:48 PM

This is as simple as I think it gets if you are copying down and expecting to skip columns as well as transposing...

in A20:

=INDEX($A$1:$C$6,COLUMNS($A$1:A$1),2*(ROWS($A$1:A1)-1)+1)

copied down to next row and across the columns.

You expand the A1:C6 range to include more columns where every other column is to be extracted in subsequent rows...