Picking out thr 5th value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fie - 10 Jan 2007 12:13 GMT

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

Reply to this Message

Roger Govier - 10 Jan 2007 12:54 GMT

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.

Signature

Regards

Roger Govier

> Hi,
>
[quoted text clipped - 4 lines]
>
> Fie x

Reply to this Message

Bob Phillips - 10 Jan 2007 13:00 GMT

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.

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi,
>
[quoted text clipped - 4 lines]
>
> Fie x

Reply to this Message

Rate this thread: