Increment only 1 column reference in sheet1 while pasting formula 9 cells apart in sheet2

[lesliewheeler]

Hi,

I have a little problem I have attached the excel sheet FYR please help me out with the formula.

All i need to do is

sheet2 A1 = sheet1 A1
Sheet2 J1 = sheet1 A2
sheet2 S1 = sheet1 A3
Sheet2 AB1= sheet1 A4

so on a so forth, sheet 2 every 9 cell the formula is pasted and value is taken from sheet1 every adjacent cell.


Appreciate the help.

[e.isayev]

Hi,
The array formula below is only for "A", Paste the formula to a3 cell and drag it
=IFERROR(INDEX('Sheet 1'!$A$2:$BC$7,ROW('sheet 2'!$A1),MATCH(COUNTIF('sheet 2'!$A$2:A$2,'sheet 2'!A$2),(A$2='Sheet 1'!$A$1:$BC$1)*COLUMN('Sheet 1'!$A$1:$BC$1),0)),"")

if you want for others too, let us know

[lesliewheeler]

Hi,
The array formula below is only for "A", Paste the formula to a3 cell and drag it
=IFERROR(INDEX('Sheet 1'!$A$2:$BC$7,ROW('sheet 2'!$A1),MATCH(COUNTIF('sheet 2'!$A$2:A$2,'sheet 2'!A$2),(A$2='Sheet 1'!$A$1:$BC$1)*COLUMN('Sheet 1'!$A$1:$BC$1),0)),"")

if you want for others too, let us know

HI eisayew

Thank you so much

I will appreciate it if you can help with all values of b,c,d,e,f,g,h,i.

I am not able to understand how to do it.....


THANKS A MILLION FOR YOUR HELP.....

[e.isayev]

here is array formula

=IFERROR(INDEX('Sheet 1'!$A$2:$BC$7,ROW('sheet 2'!$A1),MATCH(COUNTIF('sheet 2'!$A$2:A$2,'sheet 2'!A$2),((A$2='Sheet 1'!$A$1:$BC$1)*COLUMN('Sheet 1'!$A$1:$BC$1)-MATCH(A$2,'Sheet 1'!$A$1:$BC$1,0)+1),0)),"") CSE


if its what you want do not forget to Star

[lesliewheeler]

here is array formula

=IFERROR(INDEX('Sheet 1'!$A$2:$BC$7,ROW('sheet 2'!$A1),MATCH(COUNTIF('sheet 2'!$A$2:A$2,'sheet 2'!A$2),((A$2='Sheet 1'!$A$1:$BC$1)*COLUMN('Sheet 1'!$A$1:$BC$1)-MATCH(A$2,'Sheet 1'!$A$1:$BC$1,0)+1),0)),"") CSE


if its what you want do not forget to Star

Thanks you are the best..... saved my day.....man i cant tell you how glad i am...thank you so much...god bless