Create summary sheet of last row of info from other sheets
 

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 : )

Create summary sheet of last row of info from other sheets
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lisa" wrote in message
...
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
: )

Create summary sheet of last row of info from other sheets
 

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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lisa" wrote in message
...
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
: )

Create summary sheet of last row of info from other sheets
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lisa" wrote in message
...
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.

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 : )

Create summary sheet of last row of info from other sheets
 

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

"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lisa" wrote in message
...
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.

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 : )

Create summary sheet of last row of info from other sheets
 

Good to hear that, Lisa. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lisa" wrote in message
...
This works great. You have saved me a great deal of time! Thanks : )