Create summary sheet of last row of info from other sheets

[Lisa]

I have a workbook where each sheet/tab is a project summary. Each row
represents updated info for that project (multiple columns). I am trying to
automate crating a sheet that summarizes all the other sheets' last rows of
data (most current entry). The summary is used for weekly status meetings.
Right now I am doing the copy/paste from all 51 sheets./tabs into the summary
sheet! PS: need response in simple terms since I am not a technowizard : )

 

[Don Guillett]

something like this idea (not tested)
for i = 2 to worksheets.count
cells(i,"a")=sheets(i).cells(rows.count,"a").end(xlup)
next i

 

[Lisa]

Don, not sure I follow your suggestion.... I am a basic excel user. One
more thing - I add tabs as new projects come in and delete as they complete
or we decline - so the number of sheets and their names may change from week
to week for my status summary report.

 

[Max]

Here's a relatively simple formulas set-up which should deliver it nicely for
you

Illustrated in this sample:
http://www.freefilehosting.net/download/NTAzOTc=
Summary of last data rows.xls

P/s: Do not click direct on the link above if you're reading this from MS'
webpage. Do a copy n paste of the entire link, inclusive of the trailing "=",
into your browser address bar.

In your summary sheet,
Enter the project sheetnames in C1 across, eg: Proj1, Proj2, etc
Enter the designated column letters in B2 down, eg: B, C, etc
(Col labels would be in A2 down)

Then place in C2:
=LOOKUP(2,1/(INDIRECT("'"&C$1&"'!"&$B2&"1:"&$B2&"65535")<>""),INDIRECT("'"&C$1&"'!"&$B2&"1:"&$B2&"65535"))
Copy C2 across/fill down to populate the summary table. That should return
the required results, ie all the last rows' data from the designated columns
(listed in B2 down) in each project sheet. To update it each week, you just
need to update the sheetnames in C1 across. Extend/adapt to suit.

 

[Lisa]

This works great. You have saved me a great deal of time! Thanks : )

 

[Max]

Good to hear that, Lisa. You're welcome.