Picking out thr 5th value

[Fie]

Hi,

I have a spreadsheet that has headings, Name, Adress, Town, PostCode
for each town, I would like a formula that picks out every 5th address
as every 5th address is going to be used in a survey. Does anyone know
how to do this???

Fie x

 

[Roger Govier]

Hi

One way
Add an extra column to your list and enter this formula

=MOD(ROW(),5)=0
and copy down.
This will put a TRUE against every 5th row.
Data>Filter>Autofilter> use dropdown on new column to select TRUE and
you will have a filtered list of names to use.

 

[Bob Phillips]

Select A1 on another sheet, and enter

=IF(ISERROR(SMALL(IF(MOD(ROW(Sheet1!$A$1:$A$20),5)=0,ROW(Sheet1!$A$1:$A$20),""),ROW($A1))),"",
INDEX(Sheet1!A$1:A$20,SMALL(IF(MOD(ROW(Sheet1!$A$1:$A$20),5)=0,ROW(Sheet1!$A$1:$A$20),""),ROW($A1))))

which is an array formula, so commit with Ctrl-Shift-Enter, not just enter.

Copy A1 down and across.

Change A1:A20 to your range in all instances.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)