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
877 Views

Similar Articles

[PageSpeed] 13

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:

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

Cannot Update Prices Online
I can no longer updated prices online. I use 2003 stnd. When on Portfolio page, if i click on Update Prices on the left, the only option I now have is Update Prices Manually. There is no longer any option for Update Prices Online. Also, on the home page if I do click the Update Online Quotes link, all it does is refresh my related investment charts on the home page. This has worked with no problem as long as I can remember. I cannot think of any changes or updates I have made to the software or options... Standard Edition? In use for a year now? Read the License Agreement (Hel...

Won't configure updates...
I am able to install updates, and actually get a "pending" message beside the updates in the Update Manager. But when I restart my computer, I get a 0% installed message next to the 3-3 updates etc. then a message that the updates weren't configured correctly. I'm about ready to punt, any help is appreciated. CrystalBall© sez... Free unlimited installation and compatibility support is available for Windows Vista, but only for Service Pack 2 (SP2). This support, originally scheduled to end 26 November 2009, has been extended until 26 February 2010. A...

Update MSN Services with Money 2007
How can I update the Portfoglio and Accounts on MSN with MS Money 2007 Deluxe version ??? Thank you. Bye. Paolo B. Italy In microsoft.public.money, Paolo B. wrote: >How can I update the Portfoglio and Accounts on MSN with MS Money 2007 >Deluxe version ??? Transactions that you enter yourself into Microsoft Money 2007 would not be forwarded out to MSN Money. This keeps that info on your own computer. For institutions with their own OFX servers, you would have to set up those accounts to download into MSN Money also. Accounts where the data comes in via "third party" ...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

How to write a new entry in a combo box to its underlying table
Dear Access 2007 VBA Gurus, I have a assets database (rather uncreatively named "Assets"). I use a form (named "Asset Acquisition Input Form New") to enter new assets. The "Manufacturer" field (combo box name "Manufacturers_ID) on this form is a lookup to a Manufacturers table. What I want to happen is when I enter an item that is not in the lookup list, I want a message box to prompt me to add the new entry to the underlying table, or to cancel and select an item from the list. I have no trouble with the MsgBox command itself. What I don&...

Can't install windows updates on SBS 2003
I have a SBS 2003 server that has not been updated in a couple years. It has SP2 installed but when I run Microsoft Update it needs to download and install 96 updates. When I try to get them installed it comes back with an error saying not enough disk space. I have tried to just install 1 update at a time and get the same error. The C:\ drive has over 14GB of free space and the other drives have plenty of space as well. I restarted the server and also ran Disk Cleanup, as the error message reccomends, but am still having the same problem. Anyone run across this problem befo...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

FP: Couldnt close table
We have been receiving this error on two separate PCs after a recent upgrade to 7.5. Our version is 7.50g43 (service pack 5). At first we thought it was isolated to one PC now a second PC is having the same error. On the first PC, I ran new network cable, installed a different network card with no help. This PC is Win98, 64MB RAM, 600Mhz. The second PC has 128MB RAM also Win98. I don't believe switching to XP is an option right now. This message has appeared in the payables module on both machines. But it has also appeared in receivables as well. Not in GL or Payroll. I...

After 12.2.4 update, cannot open some Word docs with Open passwords
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel After installing the 12.2.4 Office update, I am unable to open some Word files that I had password-protected with an Open File password. How can I open? Thanks Basically, "you can't". Sorry, the other passwords are crackable, but "Password to Open" is effectively the encryption key. The entire file is encrypted, and unless you have the correct password, you can't get it open. However, are you sure you are not attempting to open files that were password-=protected on the PC? ...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

Pivot tables and Macros
I was looking to be able to manipulate (ie change selections from the drop downs, not change the fields in the table or anything)and print from a pivot table using a macro. So how do I go about doing that? When I set up a macro by recording the actions that I want to do, I always get an error message when I try to run it as a macro. What specific steps are you recording, and what error message do you get? Can you click the Debug button, and see the line of code that is causing the problem? Dust For Eyes wrote: > I was looking to be able to manipulate (ie change selections from the >...

How to copy aQuery to a new Table?
I have a database in a Table, a report based on that same Table and a Query based on that Report. After two months or so I like, after some new data input, to save the Table into a new Object Table. What is the best way for the Report and Query to follow the new Table whitout recreating the original Report & Query? Thankyou for your comments. I use MS Office Access 2007. Joe T >>I have a database in a Table, a report based on that same Table and a Query based on that Report. Your phrasing is wrong when it comes to the elements of an Access database. A dat...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Pivot Table in Excel
hi, I have a problem using the pivot Table in excel 2000. Earlier the location of the pivot table in excel was pointing to say c:\sales.mdb. Now the location has changed to D:\Sales.mdb. someone please tell me the place to change in the excel to reflect the same. So that upon refreshing the document i can see the latest data. Currently i am getting a error message when i try to refresh, but its not prompting to change to alternative location. If i edit the excel file in a notepad...i can see the location pointing to c:\sales.mdb. Thanks in advance for any help regarding this..expect...

Windows Update Office 2007 Converter confusion
On my desktop I have Office 2003 (laptop has Office 2007). I recently received an attachment in .docx format (2007). I downloaded the converter to my desktop and opened the document. Now on my desktop (which has Office 2003) I receive update notices for Office 2007. Should I ignore these? Hide them? Install them? Will I still be notified re updates needed for Office 2003? Thank you for whatever insight you can provide. If you can give me some of the background explanation as to why this is happening (I presume related to the 2007 converter viewer install), I can understand bett...

pvt table field settings!
hi! i am receiving data every day from 10 different places as under.! for example: DATE 11.01.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.01.2009 PLACE "B" TOTAL NUMBER OF RECEIPTS 50 TOTAL OF RECEIPTS AMOUNT 1500 DATE 11.02.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 15 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.03.2009 PLACE "C" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 -likewise i'm receiving daily data from all the 10 places..! what i want is a pivot table report on a...

How to determine whether update rollup is installed
Hi, I've just installed the update rollup on test machines both for client and server. However there's no obvious change - even the build version numbers shown on the web interface remain the same. Is this to be expected? How can I tell that the update is installed successfully? Thanks! Tom Not sure why they didn't update the version number. On the server, go to add/remove programs and check the 'show updates' checkbox at the top. then, under the Microsoft CRM Server, you should see 'Update Rollup 1...' Dave Ireland "Tom" <thisisme_ithink@yahoo...

HowTo disable cell reference update
I want to disable cell reference updates when I am inserting a row. My chart has series data in A2..A7. After row insert with MoveDown at row 2 the references are updated to A3..A8 How do I preserve the original cell refernce? ...

HTML Table import
Hi, I have a HTML document which contains a large data of data. When this html file is imported into excel some of the columns get out of whack. eg. Some columns are removed completely. This html renders ok in IE but get corrupted or misread when imported into excel, mainly when the table gets big. eg. It generally happens after the 10000 row mark. Any suggestions? Do you import it through data>import external data>new web query? -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "Ryan Hafey" <Ryan Hafey@discussion...

Permissions after 12.1.2 update
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Question. Not sure, but I after running the 12.1.2 office 2008 updater, I noticed this when repairing permissions using disk utility Permissions differ on "Library", should be drwxrwxr-t , they are drwxrwxr-x . Now, I also noticed other disk utility repair permissions that take place after the weekly overnight sripts run. I am just trying to figure out if running the office 2008 updater 12.1.2 could generate the permissions fix reported above. I installed this on 2 exactly the same computers, and didn't g...

copying column width in tables
Hi, I created a table in Publisher with 6 columns. I would columns #1, 2, 4 and 5 to have the same width and columns 3 and 6 to have a different width. I'd like to set the width of column 1 and then paste that size to the other four so they all have exactly the same width. Presently, I am eyeballing it, but it takes too much time and is not exact. Any ideas on how to do this? The only way is to create guides. Arrange, ruler guides. You could try creating a table in Word, copy/paste. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsof...