Offset discontinuous range

[mrzywx]

Hello, here's the thing. I want to select a range of values but this ranges are not continuous. I tried

=OFFSET(I21;;;;2)&OFFSET(I21;;5;;3)

I would need this to select I21,I22,I26,I27,I28

I'm trying to define a Name Range with this, but as I test it, it won't be highlighted as a range. I don't know if its working. Can somebody help me?

Thanks!!!

 

[Domenic]

Try...

=N(OFFSET(Sheet1!$I$21,{0;1;5;6;7},0))

Alternatively...

1) Select/highlight I21.

2) Press down on the CONTROL key.

3) While the CONTROL key is pressed down, click on I22, then click on I26, etc.

4) Click in the 'Name Box', located to the left of the formula bar.

5) Enter the desired name and press ENTER.

 

[mrzywx]

Thank you Domenic, but still it wont highlight the range I need. I tried tehN(OFFSET... but it just converted the range to numbers. I'm still looking for the solution.

Greetings

Try...

=N(OFFSET(Sheet1!$I$21,{0;1;5;6;7},0))

Alternatively...

1) Select/highlight I21.

2) Press down on the CONTROL key.

3) While the CONTROL key is pressed down, click on I22, then click on I26, etc.

4) Click in the 'Name Box', located to the left of the formula bar.

5) Enter the desired name and press ENTER.

 

[mrzywx]

Thanks Domenic but still this is not what I need. You see I need to select two values say (I26:i27) and then (I30:I35). Can this be done on a single formula?

Greetings!

 

[Domenic]

... but it just converted the range to numbers.

If the range contains text values, you'll need to change the 'N' to 'T'...

=T(OFFSET(Sheet1!$I$21,{0;1;5;6;7},0))

Thanks Domenic but still this is not what I need. You see I need to select two values say (I26:i27) and then (I30:I35). Can this be done on a single formula?

Can you describe what it is you're trying to do? Some sample data, along with the expected results would help as well.