summary table that updates.....

I need a spreadsheet that updates a master list I have in another sheet in 
the same workbook that updates itself once I have made a change to the master 
list. for example:

master list:

rcp     24"       300     lf
mh     5'          1        ea
inlet    type R   2        ea
rcp     24"        50      lf
inlet    type R    4       ea
mh      4'          2       ea
mh      5'           5       ea
rcp      30"        300    lf

summary list:
rcp       24"       350      lf
rcp       30"       300      lf
mh       5'          6         ea
mh       4'          2         ea
inlet    type R     6         ea

and if I make a change to the master the summary should be able to update 
the add or the material I deleted....any suggestions?....thanks.
0
Utf
2/9/2010 4:44:06 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
711 Views

Similar Articles

[PageSpeed] 57

My suggestion is to use a Pivot Table. They are very powerful and simple to 
use. More help is available here:
http://www.cpearson.com/excel/pivots.htm

Regards,
Fred

"jcheko" <jcheko@discussions.microsoft.com> wrote in message 
news:D0201F0D-C0E5-44C5-A5AB-233F83EBF0CF@microsoft.com...
>I need a spreadsheet that updates a master list I have in another sheet in
> the same workbook that updates itself once I have made a change to the 
> master
> list. for example:
>
> master list:
>
> rcp     24"       300     lf
> mh     5'          1        ea
> inlet    type R   2        ea
> rcp     24"        50      lf
> inlet    type R    4       ea
> mh      4'          2       ea
> mh      5'           5       ea
> rcp      30"        300    lf
>
> summary list:
> rcp       24"       350      lf
> rcp       30"       300      lf
> mh       5'          6         ea
> mh       4'          2         ea
> inlet    type R     6         ea
>
> and if I make a change to the master the summary should be able to update
> the add or the material I deleted....any suggestions?....thanks. 

0
Fred
2/9/2010 4:48:17 PM
Perhaps a PivotTable, with a summation data field?
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"jcheko" wrote:

> I need a spreadsheet that updates a master list I have in another sheet in 
> the same workbook that updates itself once I have made a change to the master 
> list. for example:
> 
> master list:
> 
> rcp     24"       300     lf
> mh     5'          1        ea
> inlet    type R   2        ea
> rcp     24"        50      lf
> inlet    type R    4       ea
> mh      4'          2       ea
> mh      5'           5       ea
> rcp      30"        300    lf
> 
> summary list:
> rcp       24"       350      lf
> rcp       30"       300      lf
> mh       5'          6         ea
> mh       4'          2         ea
> inlet    type R     6         ea
> 
> and if I make a change to the master the summary should be able to update 
> the add or the material I deleted....any suggestions?....thanks.
0
Utf
2/9/2010 5:05:02 PM
the pivot table is only applicable to the "master sheet" and I wont be able 
to update it unless I add fields to the pivot table, is that right?......I 
would like to have the summary table in a different sheet automatically 
updating.....any other ideas?

"Luke M" wrote:

> Perhaps a PivotTable, with a summation data field?
> -- 
> Best Regards,
> 
> Luke M
> *Remember to click "yes" if this post helped you!*
> 
> 
> "jcheko" wrote:
> 
> > I need a spreadsheet that updates a master list I have in another sheet in 
> > the same workbook that updates itself once I have made a change to the master 
> > list. for example:
> > 
> > master list:
> > 
> > rcp     24"       300     lf
> > mh     5'          1        ea
> > inlet    type R   2        ea
> > rcp     24"        50      lf
> > inlet    type R    4       ea
> > mh      4'          2       ea
> > mh      5'           5       ea
> > rcp      30"        300    lf
> > 
> > summary list:
> > rcp       24"       350      lf
> > rcp       30"       300      lf
> > mh       5'          6         ea
> > mh       4'          2         ea
> > inlet    type R     6         ea
> > 
> > and if I make a change to the master the summary should be able to update 
> > the add or the material I deleted....any suggestions?....thanks.
0
Utf
2/9/2010 6:01:01 PM
I don't see why you would have to "add fields to the pivot table". You might 
have to refresh it, but that's all. I can't think of any other solutions 
that come anywhere close to being as easy as Pivot Tables.

Regards,
Fred



"jcheko" <jcheko@discussions.microsoft.com> wrote in message 
news:E8CD9BE8-1C67-430F-B940-744AB4992E3E@microsoft.com...
> the pivot table is only applicable to the "master sheet" and I wont be 
> able
> to update it unless I add fields to the pivot table, is that right?......I
> would like to have the summary table in a different sheet automatically
> updating.....any other ideas?
>
> "Luke M" wrote:
>
>> Perhaps a PivotTable, with a summation data field?
>> -- 
>> Best Regards,
>>
>> Luke M
>> *Remember to click "yes" if this post helped you!*
>>
>>
>> "jcheko" wrote:
>>
>> > I need a spreadsheet that updates a master list I have in another sheet 
>> > in
>> > the same workbook that updates itself once I have made a change to the 
>> > master
>> > list. for example:
>> >
>> > master list:
>> >
>> > rcp     24"       300     lf
>> > mh     5'          1        ea
>> > inlet    type R   2        ea
>> > rcp     24"        50      lf
>> > inlet    type R    4       ea
>> > mh      4'          2       ea
>> > mh      5'           5       ea
>> > rcp      30"        300    lf
>> >
>> > summary list:
>> > rcp       24"       350      lf
>> > rcp       30"       300      lf
>> > mh       5'          6         ea
>> > mh       4'          2         ea
>> > inlet    type R     6         ea
>> >
>> > and if I make a change to the master the summary should be able to 
>> > update
>> > the add or the material I deleted....any suggestions?....thanks. 

0
Fred
2/9/2010 6:31:44 PM
WORKED GREAT...i JUST HAVE NEVER USED PIVOTS BEFORE...THANKS ALOT!

"Fred Smith" wrote:

> My suggestion is to use a Pivot Table. They are very powerful and simple to 
> use. More help is available here:
> http://www.cpearson.com/excel/pivots.htm
> 
> Regards,
> Fred
> 
> "jcheko" <jcheko@discussions.microsoft.com> wrote in message 
> news:D0201F0D-C0E5-44C5-A5AB-233F83EBF0CF@microsoft.com...
> >I need a spreadsheet that updates a master list I have in another sheet in
> > the same workbook that updates itself once I have made a change to the 
> > master
> > list. for example:
> >
> > master list:
> >
> > rcp     24"       300     lf
> > mh     5'          1        ea
> > inlet    type R   2        ea
> > rcp     24"        50      lf
> > inlet    type R    4       ea
> > mh      4'          2       ea
> > mh      5'           5       ea
> > rcp      30"        300    lf
> >
> > summary list:
> > rcp       24"       350      lf
> > rcp       30"       300      lf
> > mh       5'          6         ea
> > mh       4'          2         ea
> > inlet    type R     6         ea
> >
> > and if I make a change to the master the summary should be able to update
> > the add or the material I deleted....any suggestions?....thanks. 
> 
> .
> 
0
Utf
2/9/2010 7:21:01 PM
Glad to hear it. Pivot tables are like chocolate chip cookies. Once you 
start, you can't stop.

Fred

"jcheko" <jcheko@discussions.microsoft.com> wrote in message 
news:DAC40090-D6E5-4663-94EB-11F930B83BD2@microsoft.com...
> WORKED GREAT...i JUST HAVE NEVER USED PIVOTS BEFORE...THANKS ALOT!
>
> "Fred Smith" wrote:
>
>> My suggestion is to use a Pivot Table. They are very powerful and simple 
>> to
>> use. More help is available here:
>> http://www.cpearson.com/excel/pivots.htm
>>
>> Regards,
>> Fred
>>
>> "jcheko" <jcheko@discussions.microsoft.com> wrote in message
>> news:D0201F0D-C0E5-44C5-A5AB-233F83EBF0CF@microsoft.com...
>> >I need a spreadsheet that updates a master list I have in another sheet 
>> >in
>> > the same workbook that updates itself once I have made a change to the
>> > master
>> > list. for example:
>> >
>> > master list:
>> >
>> > rcp     24"       300     lf
>> > mh     5'          1        ea
>> > inlet    type R   2        ea
>> > rcp     24"        50      lf
>> > inlet    type R    4       ea
>> > mh      4'          2       ea
>> > mh      5'           5       ea
>> > rcp      30"        300    lf
>> >
>> > summary list:
>> > rcp       24"       350      lf
>> > rcp       30"       300      lf
>> > mh       5'          6         ea
>> > mh       4'          2         ea
>> > inlet    type R     6         ea
>> >
>> > and if I make a change to the master the summary should be able to 
>> > update
>> > the add or the material I deleted....any suggestions?....thanks.
>>
>> .
>> 

0
Fred
2/9/2010 8:02:15 PM
Reply:

Similar Artilces:

HELP : selectively retrieving a data from a table
Basically, what I want to implement is as following: ********************************************************************** Irms (current) is calculated from a formula and displayed in the following table. ----------------------------- | Irms | Requied Width | ----------------------------- | | | ----------------------------- Use this Ic value to run down a table and pick out 'Requied Width' that is sufficient to carry that much Irms value *********************************************************************** - Example 1 : if Irms = 4mA ...

pivot tables?
Each person in my database (of over 1000) has two rows of information. I want to combine two cells for each person so that there's just one entry per person. Someone suggested a pivot table but I don't know how to do that! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 By combine two cells, I mean mathematically add values of two cells. Patty wrote: >Each person in my database (of over 1000) has two rows of information. I want >to combine two cells for each person so that there's just one entry per >person. Someone suggest...

Publish Access Report to Microsoft Word in table with lines
I created a report in Access but the end user wants it in Word in table format with the lines between columns. This is preferred as it allows for editing without adjusting the spacing. Is it possible to achieve this? Not easily. You can try automating Word from Access, or you can check what Stephen Lebans has at http://www.lebans.com/ReportUtilities.htm -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "JE" <JE@discussions.microsoft.com> wrote in message news:6C4808F0-E53B-478E-A2A2-32F461C8BC06@microsoft.com... >I created a report...

Joining tables
Hi I am trying to create a custom CRM report using Visual Studio. I do not have much expierence with this and need a bit of assistance. I have created two reports. The first report displays details related to cases created for a customer and the second report shows specific service activities related to a customer. I would now like to join these reports and I am not quite sure how. Both reports show the details in a table. My first report query is: SELECT createdon customeridname new_typeofsupportname owneridname responsibleidcont...

PayPeriod table
I am working on a payroll system. One of the task is to create a payperiod table to capture the previous payroll currently store in the Excel file from year 2006 to present. The payperiod is bi-weekly and I want to populate this table with the pervios payperiod and up to year 2050. The columns in this table will have the following fields: ID Calendar Year - which is determined by the PayPeriod End Date PayPeriod Start Date PayPeriod End Date PayPeriod - PayPeriod Start Date - PayPeriod End Date How to write a sql query to populate this table? PayPeriod always start on Sund...

daily sales summary info
I have a small clothing boutique that recently moved locations. We would like to know if our traffic has increased, and if our average sale has increased. Is there any way to access this information in a report? Paging through every day of Z reports is pretty tedious. MP There's a custom report you can download called "average sales by store". It's just summary data that you can filter by date ranges. It includes your average transaction $ amount, avg # of units/transaction, profit marging % and avg # transactions/day. Good luck. "MP" wrote: > I ha...

Rebulid Inventory Summary from Period Summary
If you fail to close the Inventory module on the correct date, all Summary values for an inventory item are summed incorrectly. I would like to see a rebuild of the summary values in an item be rebuilt form the period summary that is correct. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree&...

Finding unique items in data field for pivot tables
I have a huge pivot table I'm trying to create and for a critical item I select I get the following message: A field in your data source has more unique items that can be used in a PivotTable. Microsoft Excel may not be able to create a PivotTable or may create the PivotTable without the data from this field. Any ideas how I can find the duplicates and create the PivotTable with this field? Hello- Based on the message, 'duplicates' is not your problem, *Unique* items is. Pivot tables are limited to 32,500 unique items per field. So if you're analyzing product sales, for...

Updating the screen
What is the best class for me to choose to subclass from: I want to create a 'graph' window which displays data derived elsewhere in my application. This window will be the only thing inside an instance of a CXTPDockingPane (a floatable / dockable window) and the majority of work in the class is in the OnPaint or OnDraw method. I have tried deriving from CDialog, CDialogBar and other classes, but none of them seem to update the screen properly when I resize the pane- the screen simply doesn't change... I have placed breakpoints in the code, they all hit the OnPaint/OnDraw m...

SOP Summary Breakdown report vs SOP Summary Breakdown Reprint repo
Hi, My client has just upgraded to vs10 and they noticed that the SOP Summary Breakdown report now prints at a document level instead of at an account level as the SOP Summary Breakdown Reprint Report does. In vs8 it apparantly worked the same as the SOP Summary Breakdown Report. I have had a look and the report uses the SOP Distribution temp table, whereas the SOP Summary Breakdown Reprint Report using the SOle.P Distribution Open and History table. I tried adding a relation to the Account Master table from the SOP Distribution temp, but this then just corrupts the report and caus...

Working in Tables 2007 Very Slow
I recently upgraded to Office 2007, and I'm finding that when I need to edit the cells in a table, it's extremely slow. I'm using Windows XP, SP3, fully updated. Is there a setting I need to change to fix this? Does this happen with all tables or just with large ones? Does it happen in all documents or just in some? With large tables (that is, tables that span multiple pages), the rule is that you should insert a new row at least once per page. See also http://word.mvps.org/faqs/tblsfldsfms/FastTables.htm. -- Stefan Blom Microsoft Word MVP "...

Updating database worksheet problem (Template Wizard)
Got my template for setting up client jobs info all made up & linked (via Template Wizard setup) to a database worksheet (Excel 97) for summaries of various stuff - start dates/employee responsible/etc. So - I open my template & fill in the starting info; then do a "Save As" ClientsName.xls - when I do this save, I know the database worksheet opens in the background (I tested this by trying a messagebox @ the "Workbook_Open" event) - in fact it seems to open twice - but the first time I save, and even when I close the book & am prompted to save again, there is ...

Update Records in a Table
I have a table with a list of people. Each person have its own Unique ID and is the primary key - no duplicate records should be created. The table is imported from a csv file coming in from a client. The csv file has the updates on them ranging from new address, telephone, effective date or term date. What I want to happen is that MS Access will REPLACE what is on the table if it find a match and CREATE a new one if it can not find a match. Is there a way to do this? Thank you in advance. Brig Check my November, 2003 "Access Answers" column in Pinnacle Publication'...

show date file updated?
Can I have a field that automatically shows the date when the file was last updated? You can use code like Sub LastSaveTime() With ThisWorkbook If .FullName <> vbNullString Then .Worksheets("Sheet1").Range("A1").Value = _ FileDateTime(.FullName) End If End With End Sub This will put the last saved date and time into cell A1 on Sheet1. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Chris Hughes" <Chris Hughes@discu...

Reading symbol table
Hi All, I am not sure whether this is the correct forum to post this question. I want to write my own tool to trace the code execution. But I cannot write the Trace macros to the source code. >From Programming group I got the idea to implement this as follows. "Idea is to extract entry point addresses from a debugger symbol table, and then write a utility to insert breakpoint instructions at each on the function entry points. Write the breakpoint routine to log entry of the function, execute/simulate the displaced instruction, then branch to continue execution of the called func...

Calculations in Tables
Hi I'm trying to sum three fields in a table in a fourth field, is this possible? I have figured out how to do it on a form i.e. calculating the nett, vat and gross but the data doesn't transfer to the table data and therefore I can not use it in a MS Word mail merge. Please can you help me in plain english as I really am not a techy!! Thanks everyone If you already have all of the data in the table for the first 3 fields, you could do it via an update query. something like UPDATE tablename SET [FourthField] = [FirstField]+[SecondField]+ [ThirdField] WHERE.... ...

graph won't update when data is changed
I've tried everything imaginable and can't get charts to update when I edit data. This was never a problem in Office 2003. The problem seems to be related to how Excel 2007 saves files, since when I first create the charts everything is fine until I save the file (in xltm format) and then reopen it. I just doesn't seem like Office 2007 is ready for prime time - on did I miss the memo: Is this version still in Alpha? ...

Linking more than one access table to a form
Hello. I have a form that we're planning on using, for work orders, for our maintenance crews. On this form, I'm trying to automatically populate about 6 fields w/records that are already in Database(A) - these records aren't intended to be edited, but are used as a reference for our crews (ie: Map #, Manhole #, etc.). I'd like to be able to use a command button to search for the UpStream MH# and then that will automatically populate the other 5 fields, based on the information in Database(A) My form's control source is Database(B) - these are different fields...

Tender Summary report
The tender summary report, by definition, should give a summary of all tenders taken for a given time period. The report in RMS, however, joins the TenderEntry table to the Transaction table, and consequently only shows tender information for sales transactions. Payments to Account and deposits are excluded from this report. The result is a partial tender summary report that doesn't give a clear picture of all the tenders taken in a given time period. Does anyone know of a way to create a comprehensive tender report that will provide the details of ALL cash, checks, credit cards...

Word table tinto excel with comments
Hi I have a Word table that I wish to put into Excel, but it has several comments in it. I wish the comments to stay with the cell they are linked to, but when I paste the table into Excel, the comments are all listed at the bottom of the data. Is there a way to keep the comments where they should be? Thanks ...

table of content type mismatch error
I've created a table of contents table with the article "How to Create..." Now I'm getting an error msg "The expression OnPrint entered as event property setting produced following error: Type mismatch. *Expression may not result in name of macro, function ro event procedure. *There may have been error evalutaing function, event or macro." As I'm new to code and am not quite sure where all should be placed, I'm hoping someone can help correct this error. Thanks in advance for any much needed help. Pam ...

Dynamic Table
I have a excel file with sales information. What I need to pick out is product, customer, net sales and quantity. I have put this information into a dynamic table; put net sales appea in rows underneath each other; although I would like them to be i columns next to each other. In english, the table would have th following columns: Product Customer Net Sales Quantity Thanks in advance -- AndyOn ----------------------------------------------------------------------- AndyOne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2021 View ...

bills summary #2
I keep showing old recurring entries on the home page of my money 2006 version. To be more specific, I am showing deposits from my old job as being "overdue" and "upcoming". How can I get rid of these entries? I already deleted the recurring deposit from the bills and deposits summary page. Please help! Don't know about M2006 but in M2002, which I use, deleting Bills/Deposits does not work. Its a bug in the program and I don't believe it has ever been fixed in later versions. Use the "This Series will end at some point in time" check box. ...

autofit columns in pivot table
I am using Excel 2002. Whenever I change the order of the row items in the layout of a pivot table, the column widths are inappropriate for the data that they contain, and I must readjust them. Is there not a way to ensure that this happens automatically? You can try the AutoFormat option -- Right-click on a cell in the pivot table Choose Table Options Add a check mark to AutoFormat table Click OK Pastel Hughes wrote: > I am using Excel 2002. > Whenever I change the order of the row items in the layout of a pivot table, > the column widths are inappropriate for the data that the...

Summary Sheet Totals
I have a workbook that tallys values from 30 or so separate sheets. I there an easy way to have a summary sheet that looks at specific cell on each sheet? Right now, I have to do a simple SUM function where manually click on each cell on each sheet I want in the total. I' thinking there is a way to do this with a formula/array. Thanks, Gar -- gwhit ----------------------------------------------------------------------- gwhite's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2507 View this thread: http://www.excelforum.com/showthread.php?threadid=38589 ...