Automatically Copy Last Cell (Most Up-to-Date) from Multiple Worksheets into a Summary Worksheet

[lindan8r]

I have a workbook in which I keep track of various documents and where they are in the completion process.

One column is for "Comments/Status," in which I state something like, "11/9/10 - Sent to Joe Q. Public for review."

I will have several rows with similar information; for example, the next row in that column might have another note saying, "11/10/10 - Received comments from J. Public. Sent to Jane Smith for approval."

What I would like to be able to do is to have a "Summary" worksheet that lists the most current comment. I can't seem to find a formula that will find the last cell with text in it and put it in my Summary sheet automatically. I'm not good with VB and I've considered a macro, but only as a last resort.

To clarify (I hope!), here is what I need:

Sheet 1: Summary Sheet looks like this:

--------Col A-------Col B-------Col C
Row 1: DocName---Doc1-------Status
Row 2: DocName---Doc2-------Status
Row 3: DocName---Doc3-------Status
etc.

Sheet 2: Document 1 Status
--------Col A-------Col B-------Col C
Row 1: Revision#---Date-------Status
Row 2: Revision#---Date-------Status
Row 3: Revision#---Date-------Status
etc.

Sheet 3: Document 2 Status
--------Col A-------Col B-------Col C
Row 1: Revision#---Date-------Status
Row 2: Revision#---Date-------Status
Row 3: Revision#---Date-------Status
etc.

Sheet 4: Document 3 Status
--------Col A-------Col B-------Col C
Row 1: Revision#---Date-------Status
Row 2: Revision#---Date-------Status
Row 3: Revision#---Date-------Status
etc.

I would like to be able to use a formula, macro, or VB script to pull the LAST (and therefore most up-to-date) comment from column C from EACH worksheet and insert each comment into a separate row in Sheet 1.

I've tried using formulas with "ISBLANK," "ISNONTEXT," etc. with an IF statement, but I can't get anything to work.

If I haven't been clear with what I need, please let me know and I'll try to re-word things. Thank you in advance for your time!
Linda

 

[VoG]

Try like this

=LOOKUP(REPT("z",255),Q:Q)

 

[lindan8r]

Hi Peter,
Thank you for your reply. I tried the formula, but it gave me an error of #NA. I don't actually want to repeat anything; I need to pull the information from the last non-blank cell in a column into a summary worksheet.

Here is a real life example:

Column A.......................................Column B
Review Draft 1................................11/5/10 - E-mailed to Joe.
Review Draft 2................................11/9/10 - Incorporated Joe's comments.
Review Draft 3................................11/9/10 - Signed by Eng., RSO; gave to Doug.
Review Draft 4................................11/10/10 - Changed page E-2. Out for signature.
So...Since the most up-to-date status is "Changed page E-2. Out for signature", I would like to have a formula find that last non-blank cell in Column B and put it in the summary worksheet automatically. This way, if I add another comment in Column B, such as "Document approved", the summary worksheet will automatically update to the latest information.

Hope that makes more sense. I do appreciate your help!

Linda

 

[VoG]

With your example

Excel Workbook
	A
1	11/10/10 - Changed page E-2
Sheet2

 

[lindan8r]

Hi Peter,
That worked!

The only problem is that the true result only shows when I hit the formula button in the formula bar, and I see the text as the Formula Result. The text in the cell where the formula is just shows the formula. I've had this happen with other Excel workbooks, and I'm not sure what the issue is. Do you have any idea? (I have Excel 2007.)

Thank you so much for your help!

Linda

 

[VoG]

Click in the cell, press CTRL + 1 and format the cell as General. Then re-enter the formula.

 

[lindan8r]

Peter, you are an Excel GENIUS! And a very nice man to have answered all of my questions. I did what you said and it works beautifully now. I never would have figured this out on my own, so thank you, thank you, thank you!!!

Linda