How to pick every third cell?

[jakar]

Hi,
I have a spreadsheet with a column in which I keep dates. The date
appers in every third cell of this column and the other two cells are
blank. I have the another sheet in this workbook in which I want those
dates to appear in the column in consecutive order. I'm using cell
reference comand right now but have to do it manually every time I have
a new entries. So in the other sheet there is a column with cells
having references to every third cell in the first sheet. I trided to
high-lite the multiple bunches of cells and drag-copy it down - but
it's not working.
Any ideas???
Thanks,
jakar

 

[Guest]

with autofilter, sort by NonBlanks highlight whats left
or custom sort....

say your dates are in column A then>
highlight column A
Data> Filter>Autofilter
use the drop down button to navigate to > NonBlanks ( or use custom if other
cells are not blank)
then highlight the rows and turn autofilter back off

 

[Bob Phillips]

Jakar,

Use this formula

=IF(ROW()-ROW(G1:G20)+1>ROWS(Sheet1!A1:A100)-COUNTBLANK(Sheet1!A1:A100),"",
INDIRECT("'Sheet1'!"&ADDRESS(SMALL((IF(Sheet1!A1:A100<>"",ROW(Sheet1!A1:A100
),
ROW()+ROWS(Sheet1!A1:A100))),ROW()-ROW(G1:G20)+1),COLUMN(Sheet1!A1:A100),4))
)

and copy down. It is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[jakar]

hey, Bob - is this a joke? What is G column has to do with anything..?
jakar