ultimate99 Posted August 15, 2014 Share Posted August 15, 2014 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? Link to comment Share on other sites More sharing options...
0 Starbuck84 Posted August 15, 2014 Share Posted August 15, 2014 (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? Link to comment Share on other sites More sharing options...
0 Haggis Veteran Posted August 15, 2014 Veteran Share Posted August 15, 2014 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 Link to comment Share on other sites More sharing options...
0 ultimate99 Posted August 15, 2014 Author Share Posted August 15, 2014 Actually row 2 and sheet abc, so:? =OFFSET(Sheet4!$A$2,(ROW(A2)-1)*7,0) Link to comment Share on other sites More sharing options...
0 Haggis Veteran Posted August 15, 2014 Veteran Share Posted August 15, 2014 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 Link to comment Share on other sites More sharing options...
0 ultimate99 Posted August 15, 2014 Author Share Posted August 15, 2014 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? Link to comment Share on other sites More sharing options...
0 Haggis Veteran Posted August 15, 2014 Veteran Share Posted August 15, 2014 if the sheet has spaces use ' =OFFSET('Sheet 1'!$A$1,(ROW()-1)*7,0) Link to comment Share on other sites More sharing options...
0 ultimate99 Posted August 15, 2014 Author Share Posted August 15, 2014 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. Link to comment Share on other sites More sharing options...
0 notta Posted August 15, 2014 Share Posted August 15, 2014 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. Link to comment Share on other sites More sharing options...
0 ultimate99 Posted August 15, 2014 Author Share Posted August 15, 2014 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! Link to comment Share on other sites More sharing options...
0 Haggis Veteran Posted August 15, 2014 Veteran Share Posted August 15, 2014 yeah i exaplined above if they is a space in your worksheet name you need to use ' ' Link to comment Share on other sites More sharing options...
Question
ultimate99
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?
Link to comment
Share on other sites
10 answers to this question
Recommended Posts