Sign in to follow this  

excel 2007 - select cells according to specific condition/criteria

Recommended Posts

ultimate99    51

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 this post


Link to post
Share on other sites
Starbuck84    34

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 this post


Link to post
Share on other sites
Haggis    983

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 this post


Link to post
Share on other sites
ultimate99    51

Actually row 2 and sheet abc, so:?

 

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

Share this post


Link to post
Share on other sites
Haggis    983

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 this post


Link to post
Share on other sites
ultimate99    51

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 this post


Link to post
Share on other sites
Haggis    983

if the sheet has spaces use '

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

Share this post


Link to post
Share on other sites
ultimate99    51

 

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 this post


Link to post
Share on other sites
notta    41

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 this post


Link to post
Share on other sites
ultimate99    51

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 this post


Link to post
Share on other sites
Haggis    983

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.