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


--
jakar
------------------------------------------------------------------------
jakar's Profile: http://www.excelforum.com/member.php...o&userid=28844
View this thread: http://www.excelforum.com/showthread...hreadid=485957

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

say your dates are in column A then
highlight column A
Data FilterAutofilter
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

"jakar" wrote:


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


--
jakar
------------------------------------------------------------------------
jakar's Profile: http://www.excelforum.com/member.php...o&userid=28844
View this thread: http://www.excelforum.com/showthread...hreadid=485957

Jakar,

Use this formula

=IF(ROW()-ROW(G1:G20)+1ROWS(Sheet1!A1:A100)-COUNTBLANK(Sheet1!A1:A100),"",
INDIRECT("'Sheet1'!"&ADDRESS(SMALL((IF(Sheet1!A1:A 100<"",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" wrote in
message ...

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


--
jakar
------------------------------------------------------------------------
jakar's Profile:
http://www.excelforum.com/member.php...o&userid=28844
View this thread: http://www.excelforum.com/showthread...hreadid=485957

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


--
jakar
------------------------------------------------------------------------
jakar's Profile: http://www.excelforum.com/member.php...o&userid=28844
View this thread: http://www.excelforum.com/showthread...hreadid=485957