REVERSE the data corresponding to value in the flag cell

[hsandeep]

My Data range is B2:F11 with a flag in corresponding row cell in A2:F11
This flag in A2:F11 is EITHER of 1 OR 0
Output required as in H2:L11 as:
If flag is 1, then corresponding row in H2:L11 SHOULD BE ‘SAME’
If flag is 0, then corresponding row in H2:L11 SHOULD BE ‘REVERSED’

How to accomplish?
Thanks in advance
I am using Excel 2007
Sheet1

	A	B	C	D	E	F	G	H	I	J	K	L
2	1	77	88	99	100	200		77	88	99	100	200
3	0	22	33	44	55	66		66	55	44	33	22
4
5
6
7
8
9
10
11	1	44	12	50	5	2		44	12	50	5	2

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 10px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"></colgroup><tbody>
</tbody>

 

[Fluff]

In h2 copied down & across
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($F2)-COLUMN(A2))))

 

[hsandeep]

In h2 copied down & across

Thanks Fluff. IT WORKS

 

[hsandeep]

In h2 copied down & across
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($F2)-COLUMN(A2))))

Fluff
Just now...while re-trying........I 'inserted' 3 columns so that Column A got shifted to Column D; BUT I am getting #REF ! in the output cell!!!

Sheet1

	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	0			77	88	99	100	200		#REF !	100	99	88	77
3	0			22	50	22	22	22		#REF !	22	22	50	22
4
5
6
7
8
9
10
11	0			2	2	2	2	2		#REF !	2	2	2	2

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 10px"><col style="WIDTH: 46px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula M2 =IF($D2="","",IF($D2=1,G2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(D2)))) N2 =IF($D2="","",IF($D2=1,H2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(G2)))) O2 =IF($D2="","",IF($D2=1,I2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(H2)))) P2 =IF($D2="","",IF($D2=1,J2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(I2)))) Q2 =IF($D2="","",IF($D2=1,K2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(J2)))) M3 =IF($D3="","",IF($D3=1,G3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(D3)))) N3 =IF($D3="","",IF($D3=1,H3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(G3)))) O3 =IF($D3="","",IF($D3=1,I3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(H3)))) P3 =IF($D3="","",IF($D3=1,J3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(I3)))) Q3 =IF($D3="","",IF($D3=1,K3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(J3)))) M11 =IF($D11="","",IF($D11=1,G11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(D11)))) N11 =IF($D11="","",IF($D11=1,H11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(G11)))) O11 =IF($D11="","",IF($D11=1,I11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(H11)))) P11 =IF($D11="","",IF($D11=1,J11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(I11)))) Q11 =IF($D11="","",IF($D11=1,K11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(J11)))) <tbody> </tbody>

<tbody>
</tbody>

Please check.

 

[Fluff]

The problem is that you have inserted two blank columns in E & G, not the blank columns in A:C.
What is you actual layout?

 

[Fluff]

With your original layout change the formula to
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($G2)-COLUMN(B2))))

 

[hsandeep]

With your original layout change the formula to
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($G2)-COLUMN(B2))))

Little miss-communication from my side

1. My data range for both INPUT (B2:F11) & OUTPUT (H2:L11) looks ‘similar’ to the ‘actual’ location in the Worksheet. Here I mean instead of B2:F11 it is ‘actually at’ CB2:CF11 & instead of H2:L11 it is ‘actually at’DM2:DQ11.
2. So there are lot of columns BETWEEN INPUT range & OUTPUT range.
3. Both these ranges MAY shift as required in future & the formula in the OUTPUT cells has to take care.

Comments please.

 

[Fluff]

Little miss-communication from my side

That (IMO) the understatement of the year.
If you give us incorrect info, then you will get something that doesn't work in return.

Whilst you have now said where some of the data is located you haven't said where the initial criteria is is located.

 

[hsandeep]

That (IMO) the understatement of the year.
If you give us incorrect info, then you will get something that doesn't work in return.

Whilst you have now said where some of the data is located you haven't said where the initial criteria is is located.

The initial criteria is located at U2:U11

 

[Peter_SSs]

This, copied across and down, should be readily adaptable to work with the 3 sections anywhere.

Excel 365
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1								1
2								0
9								0
10								1
11
12	77	88	99	100	200
13	22	33	44	55	66
20	5	6	8	9	4
21	44	12	50	5	2
22
23
24											77	88	99	100	200
25											66	55	44	33	22
32											4	9	8	6	5
33											44	12	50	5	2
Reverse Some

Cell Formulas
Range	Formula
N24	=IF($K1=1,D12,INDEX($D12:$H12,COLUMNS($D12:$H12)+1-COLUMNS($N24:N24)))