ranges

[tricone]

Hi all,
assuming the colums are A,B,C respectively:
I am trying to gain the output in column C here based on column A and something like: =concatenate("$B$",startvalue,":$B$",endvalue)
so that I can use the indirect command to get a data validation that would call just 1 value for rows 1,6,7 and would call cells b2-b5 for rows 2-5.

<table border=1>
<tr>
<td>1</td>
<td>red</td>
<td>$B$1:$B$1</td>
</tr>
<tr>
<td>2</td>
<td>green</td>
<td>$B$2:$B$5</td>
</tr>
<tr>
<td>2</td>
<td>blue</td>
<td>$B$2:$B$5</td>
</tr>
<tr>
<td>2</td>
<td>yellow</td>
<td>$B$2:$B$5</td>
</tr>
<tr>
<td>2</td>
<td>black</td>
<td>$B$2:$B$5</td>
</tr>
<tr>
<td>3</td>
<td>purple</td>
<td>$B$6:$B$6</td>
</tr>
<tr>
<td>4</td>
<td>white</td>
<td>$B$7:$B$7</td>
</tr>
</table>

Ive been messing around with reference columns like:
=IF(OR(A1=A2,A2=A3),1,0)
=IF(A2=A1+1,row(B2),row(B1))

but cant quite get this to work out. any ideas?

 

[tricone]

nvm, got it using:

=IF(OR(L2=L3,L2=L1),J1+1,0)
start value:
=IF(J2>=1,I2-J1,I2)

message me if you want more details