11 posts in this topic

Posted

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

Posted (edited)

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?

Edited by Starbuck84

Share this post


Link to post
Share on other sites

Posted

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

Posted

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

Posted

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

Posted

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

Posted

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

Posted

 

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

Posted

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

Posted

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

Posted

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  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.