Followers 0

# excel 2007 - select cells according to specific condition/criteria

## 11 posts in this topic

I have a long list and I want to select entries every 10 cells, how can I do that? Possible to extract them to a separate list?

##### Share on other sites

I thought you could use the OFFSET and INDEX function for this? It's been a time, so I won't be able to help much.

Google Search Example something like this?

##### Share on other sites

if your data you want to check is in Column A of sheet 1 then

In A1 of your new sheet, put this:

`=OFFSET(Sheet1!\$A\$1,(ROW()-1)*10,0)`

and copy it down :)

let me know and i will send you an example sheet if you need

##### Share on other sites

Actually row 2 and sheet abc, so:?

=OFFSET(Sheet4!\$A\$2,(ROW(A2)-1)*7,0)

##### Share on other sites

do it work ok for you?

`=OFFSET(Sheetabc!\$A\$2,(ROW()-1)*10,0)`

should work

basically

=OFFSET(Sheetabc!\$A\$2,(ROW()-1)*10,0)

Red bit is the sheet name with all your data

Blue bit is the starting cell on that sheet

##### Share on other sites

do it work ok for you?

`=OFFSET(Sheetabc!\$A\$2,(ROW()-1)*10,0)`

should work

basically

=OFFSET(Sheetabc!\$A\$2,(ROW()-1)*10,0)

Red bit is the sheet name with all your data

Blue bit is the starting cell on that sheet

Unfortunately, it didn't. I get an error and a #VLAUE! in the cell.

=OFFSET(SheetWHATEVER!\$A\$2,(ROW()-1)*10,0)

WHATEVER name if there are spaces?

Blue is the starting of the cell in the new sheet?

##### Share on other sites

if the sheet has spaces use '

`=OFFSET('Sheet 1'!\$A\$1,(ROW()-1)*7,0)`

##### Share on other sites

if the sheet has spaces use '

`=OFFSET('Sheet 1'!\$A\$1,(ROW()-1)*7,0)`

When I click enter a window pops open asking me to Update Values of the original sheet and I have to open a xls file.

##### Share on other sites

I tested this and it works fine for me. I do get the open new excel workbook dialog using your formula though and that's because you have 'Sheet 1' when it's usually named 'Sheet1' without a space.

##### Share on other sites

I tested this and it works fine for me. I do get the open new excel workbook dialog using your formula though and that's because you have 'Sheet 1' when it's usually named 'Sheet1' without a space.

Works now.

Thanks a lot guys!

##### Share on other sites

yeah i exaplined above if they is a space in your worksheet name you need to use ' '

## Create an account

Register a new account