Jump to content



Photo

excel 2007 - select cells according to specific condition/criteria


  • Please log in to reply
10 replies to this topic

#1 ultimate99

ultimate99

    Neowinian Senior

  • Tech Issues Solved: 1
  • Joined: 20-August 02
  • Location: ON, Canada
  • OS: Win 8.1 Pro
  • Phone: Nokia Lumia 920

Posted 15 August 2014 - 13:22

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?




#2 Starbuck84

Starbuck84

    Would You Kindly?

  • Joined: 26-June 07
  • Location: Breda, Netherlands

Posted 15 August 2014 - 14:06

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, 15 August 2014 - 14:08.


#3 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 12
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 15 August 2014 - 14:20

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



#4 OP ultimate99

ultimate99

    Neowinian Senior

  • Tech Issues Solved: 1
  • Joined: 20-August 02
  • Location: ON, Canada
  • OS: Win 8.1 Pro
  • Phone: Nokia Lumia 920

Posted 15 August 2014 - 14:33

Actually row 2 and sheet abc, so:?

 

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



#5 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 12
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 15 August 2014 - 14:46

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



#6 OP ultimate99

ultimate99

    Neowinian Senior

  • Tech Issues Solved: 1
  • Joined: 20-August 02
  • Location: ON, Canada
  • OS: Win 8.1 Pro
  • Phone: Nokia Lumia 920

Posted 15 August 2014 - 14:50

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?



#7 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 12
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 15 August 2014 - 15:14

if the sheet has spaces use '

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


#8 OP ultimate99

ultimate99

    Neowinian Senior

  • Tech Issues Solved: 1
  • Joined: 20-August 02
  • Location: ON, Canada
  • OS: Win 8.1 Pro
  • Phone: Nokia Lumia 920

Posted 15 August 2014 - 16:11

 

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.



#9 notta

notta

    Neowinian

  • Joined: 20-April 05

Posted 15 August 2014 - 16:23

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.



#10 OP ultimate99

ultimate99

    Neowinian Senior

  • Tech Issues Solved: 1
  • Joined: 20-August 02
  • Location: ON, Canada
  • OS: Win 8.1 Pro
  • Phone: Nokia Lumia 920

Posted 15 August 2014 - 16:38

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!



#11 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 12
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 15 August 2014 - 20:42

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