need help on combining or merging worksheets or workbooks with dates

[Jay]

Hi, I'm a "one-man" shop, using only one PC, using Excel XP.

I want to develop lists of critical dates for various projects (the list for
each project would be on its own worksheet in a workbook). Each critical
date for each project would be in column A and the description would be in
column B, for each worksheet. For the sake of identification, I call this a
"date row" so A1 and B1 would be a date row for the project for the
worksheet in question.

I'd like to write a formula on a new worksheet in the workbook that would
take the date rows on certain worksheets I've specified in the formula, and
combine them, so they're all sorted in chronological order on the new page.
Any advice or ideas to get started are welcome. jay

 

[DDM]

Jay, you can do it this way:

1-Go to one of your "project" sheets and select a block of "date rows." Ex.:
A2:B20.
2-Edit (or right-click) > Copy.
3-Switch to your "summary" sheet and select a destination cell. Ex.: A2.
4-Edit (or right-click) > Paste Special > Paste Link. (Excel will paste the
whole block.)
5-Repeat Steps 1-4 for other projects and date rows.
6-When finished, switch to your summary sheet and sort ascending on Column
A. Rows will stay together.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[Jay]

DDM,

Thanks for a pretty good help outline. If I run into any snags, I'll post
back.

I checked out your website; very interesting stuff; I'll be studying a lot
of the Excel tips there in the near future. Jay

 

[CLR]

I would recommend just recording a macro (Tools > macro >recordmacro....)
while doing a regular "copy and paste" operation of the cells you want to
the new sheet and then continue and do a "sort".........then if the dates
change, you can just run the macro each time to get the current
data..........

Vaya con Dios,
Chuck, CABGx3

 

[Jay]

Chuck,

Great idea. I'll have to check to see if I can get the macro to work
dynamically, so if I add a row, it is included in what is copied and pasted.
In other words, if the macro I record is for A2:C4, and then I add a date
row between row 2 and 4, thus making the range I want the macro to copy and
paste become A2:C5, will the macro copy and paste the 5th row?

Maybe there's a formula or shortcut that copies all rows that contain data
until a blank row is encountered; this would let me add data rows and know
the macro would include them because they would be above the first blank row
the macro encounters. This is fun. Jay

 

[CLR]

I should imagine that if you used a RangeName, and then inserted rows within
the RangeName, that it would still copy ok................

Vaya con Dios,
Chuck, CABGx3