Formula To Copy Info in Column to Rows

**Limits** · 03-20-2014, 11:08 AM

Hi Guys;

Trying to figure this out and I can't find the answer anywhere...

In column A I have Product Codes, They start at A3 and go down to A169.

I need to have a formula which i can drag across from CQ3 to DH3 which corresponds in the following manner.

CQ3 = A4
CR3 = A5
CS3 = A6

etc etc.

I then want to be able to copy the formula into another place, eg CQ20 = A21, CR20 = A22 etc

I have attached a screen shot of what I hope will help.

Untitled-1000.jpg

Cheers in advance

Chris

**Sam Capricci** · 03-20-2014, 01:03 PM

you could try this in CQ3 =INDEX($A$2:$A$169,(ROWS($CQ$3:CQ3)-1)*3+CEILING(COLUMNS($CQ$3:CQ3)/1,1),MOD(COLUMNS($CQ$3:CQ3)-0,1)+1)&"" then drag right from there.
Then move the formula to where else you want it and adjust the cells.

**Limits** · 03-20-2014, 01:27 PM

Hi Sambo;

Right, I just tried that and it doesn't like this part for some reason

=INDEX($A$2:$A$169,(ROWS($CQ$3:CQ3)-1)*3+CEILING(COLUMNS($CQ$3:CQ3)/1,1),MOD(COLUMNS($CQ$3:CQ3)-0,1)+1)&""

**Sam Capricci** · 03-20-2014, 01:34 PM

maybe it should be $A$3:$A$169 but i'm not sure otherwise what would be wrong with it.
I tested it out on info beginning in A165 and ending in A179 putting the results in B166 and going to the right from there and this is what I wrote: =INDEX($A$165:$A$179,(ROWS($B$166:B166)-1)*3+CEILING(COLUMNS($B$166:B166)/1,1),MOD(COLUMNS($B$166:B166)-0,1)+1)&""
and it worked fine all the way to the end then when the rows run out in col A it will return #REF.