Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 : ) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 : ) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 : ) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 : ) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 : ) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 : ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a summary list using same fields from multiple sheets | Excel Discussion (Misc queries) | |||
Summary Sheet help with multiple sheets | Excel Discussion (Misc queries) | |||
Lookup info in support sheets and enter in summary sheet | Excel Discussion (Misc queries) | |||
How do I create a summary worksheet that references data in other sheets? | Excel Worksheet Functions | |||
How do I read info from different worksheets into a summary sheet? | Excel Worksheet Functions |