how to: if a value is entered in one row, then associated data is cummulatively added to a 'total' on another sheet ?

Excel Version : 12.2.3
OS X 10.6.2
Intel

Hi =97 I am very new to Excel, and am learning a lot, but have found it
difficult to work out a new problem. I am trying to nut out a formula
to do the following (I have searched all over, but realise I don't
really know what my search terms should be).

For arguments sake, I have two worksheets, A and B. Worksheet A is
used as a meta-summary of what will happen elsewhere in the Workbook.
Worksheet B is where the raw data is entered (as it will in C, D, E,
etc, in the future). I have created a drop down list of categories for
use in Worksheet B. What I wish to happen is:

If one of the terms in the dropdown list is selected in, say, cell A1
of Worksheet B, then an associated value in B1 is automatically and
*cummulatively* added *back to* a summary cell in Worksheet A.

Any help or advice would be very appreciated.

Thanks!
0
hermes
2/5/2010 1:07:57 PM
mac.office.excel 1146 articles. 0 followers. Follow

1 Replies
749 Views

Similar Articles

[PageSpeed] 53

You can't really add a "cumulative" value to a single cell, as a cell 
contains either a value or a fixed formula, not both.

How many drop-down cells do you intend to have? One per row, as you 
imply in your post, sounds very excessive.   
Perhaps you can describe in more detail exactly what you want summarized 
and we can point you to things like SUMIF and VLOOKUP to simplify your 
task.

Carl


In article 
<3981c7d0-36d6-40c8-8070-8bcf95b6ee3f@u19g2000prh.googlegroups.com>,
 hermes <peterminter@gmail.com> wrote:

> Excel Version : 12.2.3
> OS X 10.6.2
> Intel
> 
> Hi � I am very new to Excel, and am learning a lot, but have found it
> difficult to work out a new problem. I am trying to nut out a formula
> to do the following (I have searched all over, but realise I don't
> really know what my search terms should be).
> 
> For arguments sake, I have two worksheets, A and B. Worksheet A is
> used as a meta-summary of what will happen elsewhere in the Workbook.
> Worksheet B is where the raw data is entered (as it will in C, D, E,
> etc, in the future). I have created a drop down list of categories for
> use in Worksheet B. What I wish to happen is:
> 
> If one of the terms in the dropdown list is selected in, say, cell A1
> of Worksheet B, then an associated value in B1 is automatically and
> *cummulatively* added *back to* a summary cell in Worksheet A.
> 
> Any help or advice would be very appreciated.
> 
> Thanks!

-- 
Team EM to the rescue!    http://www.team-em.com
0
Carl
2/7/2010 9:50:18 PM
Reply:

Similar Artilces:

Slow Response time after server move to AD
I've recently moved my Windows 2003 Server running GP 8.0 from a stand alone server on a Novell network to a member server in Active Directory. The response time from client computers has slowed drastically. It also ocurred when the clients were part of Active Directory and the sevrer was still a stand alone (workgroup). Does anyone have any thoughts on what might be slowing the response time of GP, primarily on customer screens. Thanks! Ben DeGennaro ...

Another multiple criteria/column question
Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLO...

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

Reformat data to vertical format
Here is what I am trying to do. http://www.totalcontrolproducts.com/totalcontrolproducts_OLD/download/images/Untitled-1.gif I have about a thousand records that I need in a vertical format with normal shared field name. Any suggestions -- Psydwaz ----------------------------------------------------------------------- Psydwaze's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2437 View this thread: http://www.excelforum.com/showthread.php?threadid=37965 see Creating a Spreadsheet from Database data (#dbdata) http://www.mvps.org/dmcritchie/excel/snakecol.htm...

Users asked to enter Username / Password /Domain when opening outl
Just this morning, our users are being asked for their username / domain and password. When we eneter it in, the box reappearas after we eneter it again we get and error that the information store could not be opened. We are running Exchanage 5.5 with Windows 2000. Any help would be great. Thanks in advance =?Utf-8?B?SmVmZg==?= <Jeff@discussions.microsoft.com> wrote in news:DD28037B-B6C8-4575-B0CC-F80C14991716@microsoft.com: > Just this morning, our users are being asked for their username / > domain and password. When we eneter it in, the box reappearas after we > eneter...

Receive mails from one adr. to several computers
Hello. I have a problem receiving mails from one adr. to 5 computers in a network. One of the computers get's all the mails. They should all receive the mails. All the machines run's win Xp and office Xp. Outlook on all computers are set to store the mails on the server, even though they have been received on a computer. When I check my WebMail, the messages are still there! Please help. Nima ...

Developing a robust database at one co. then using it at another c
Are there any legal implications of using the type of database listed above to perform similar functions for another company? The 2 company's are in 2 totally different industries and the new database will have to be modified to fit the requirements for the industry it is in. The database was developed at the old company. there is no such thing as a robust Jet database. Move to SQL Server if you want to build a solution that will work for the next decade. Jet is and always has been depecrated On Apr 7, 12:14=A0pm, BoaMan10 <BoaMa...@discussions.microsoft.com> w...

numbering rows
Is there a way to number each row in Excel so that when it prints out the rows all have numbers? I couldn't find it in Msft online Help but I guess that's no surprise. thanks! Jane Two options. 1. In page setup>sheet select "row and column headers". Note this will print the column headsers also. 2. Insert a column to left of your original columns(will become column A) Enter in A1 =ROW() and drag/copy down the column as far as you wish. Gord Dibben Excel MVP On Fri, 23 Apr 2004 17:15:10 -0700, "jane" <jfnysf@hotmail.com> wrote: >Is there a ...

Line Graph with two different data points
Hello, I am trying to create a line chart with numbers from my product Vs a competitor. The problem i am having is how can i make a chart that will have four data points comparing to each other? For example Product A- 1.5 1.2 1.6 1.8 Product B- 2.0 1.1 1.2 1.3 Product A- 70% 20 % 30% 67% Product B- 65% 30% 65% 55% How do i get all of these numbers on one chart? Is a line chart correct. I want to see these numbers compared?? Thank you so much Hi, Since the number don't appear related to the percentages you might plot them on the same chart but two different axes. If they were relate...

How to shift address listings from row list to columns?
I have a mailing list with name, address, city, state & zip with each item in individual rows like a list of labels and a few empty rows of space between each listing. How do I create/transfer this list into columns accross so I can sort by city or zip? Thank you! If your data is nicely grouped, with each group in 5 lines, viz: name add city state zip then an earlier suggestion given which worked might be worth a try: See: http://tinyurl.com/wgcb -- hth Max ----------------------------------------- Please reply in newsgroup Use xdemechanik <at>yahoo<dot>com for email --...

retain only rows with condition that cells in column H containing "AU"
I have a large database containing columns with one column specifies the product name. In one instances, I need to retain only rows with condition that cells in column H containing "AU" of the entire string in the cell. Delete those rows without it. The problem is , that the AU of the string can appear in any position, not a fixed position. Is there any simple way of doing it? Many Thanks! Regards, Bora Hi, You can use AutoFilter. Choose the command Data, Filter, AutoFilter. Then open the autofilter drop down in the column H and choose Custom and from the first drop down, t...

Data changes when Analyzed with Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

Cannot enter an array formula
I'm trying to help a user on a toshiba laptop with array formulas. For some reason he cannot create an array formula. The formula evaluates on everyone else's laptop but his. He has Windows 98 and Office XP Professional. Has anyone encountered this? Ryan If he can't create it then you wouldn't know that it doesn't evaluate, so which is it? What's the formula? and can he actually create it but it doesn't appear to have the right answer. If you hit F9 does the right answer appear? -- Regards Ken....................... Microsoft MVP - Excel ...

Formula so that response is >value
The same cell has two formulas, IF(D3>0,LOG(C3/F3),IF(D3<1,LOG(C3/10)). For example, if C3 is 1.0E+06, D3 is 100, then F3 will be 1.0E+03 (due to another formula), and the response for the first part should read 3.0. If D3 is 0, F3 will be <10 (due to another formula), and the response should read >5.0, not simply 5.0. I'm not sure I follow your formulas as described, but if you're looking to have your results display the ">" character along with the value, then maybe this example will lead you in the right direction: Let's say cell A1 contains ...

copy rows to another file
Dear Experts I have following code,i need to copy desirde rows to new file (r.xls) on sheet 1. This code is able to copy desired rows from active sheet to sheet2(same file) based on values in column G. Would you please guide me? regards Sub Marine() Dim arrParts() As String Dim MyRange As Range, CopyRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = Range("G1:G" & LastRow) response = InputBox("Enter rows to copy in the format nnn,nnn,nn") arrParts = Split(response, ",") For Each ...

Estimates Invoices from a sheet
I have created a work book that I use to do Landscaping estimates. Th system combines sheets containing client info, material cost info labour info, sight measurements and pricing info to generate a estimate. The quote worksheet contains all of the potential component of a job wheather they are used in a given job or not. What I am no trying to do is produce a client ready estimate that picks up only th row information from the quote work sheet, that is active (contain values). In other words I don't want to preformat a estiamte form tha contains all potential quote items regardless of th...

Move a particular named sheet to the end.
Using macros, how can I move a sheet called TOTAL to become the very last sheet within a workbook? Your assistance will be appreciated. -- Thank U and Regards Ann Try this: Worksheets("Total").Move after:=Worksheets(Worksheets.Count) "Ann" wrote: > Using macros, how can I move a sheet called TOTAL to become the very last > sheet within a workbook? > > Your assistance will be appreciated. > > -- > Thank U and Regards > > Ann > Barb, Works a treat, thank you for your assiatance. -- Thank U and Regards Ann "Barb Reinhar...

How to transfer Custom Views to another machine
Outlook 2003 How do you transfer Custom Views to another machine (no exchange server)? Thank you for your help. They are contained within your pst-file so if you transfer that you'll transfer the views too. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more ----- "John" <no.email@no.email.com> wrote in message news:usDhjJ7hHHA.4132@TK2MSFTNGP05.phx.gbl... > Outlook 2003 > > How do you transfer Custom Views to another machine (no exchange server)? >...

Adding a Bank to Money 2002's database
Can I add an online bank to Money 2002's database or must it be added by Microsoft?? In microsoft.public.money, Rob wrote: >Can I add an online bank to Money 2002's database or must >it be added by Microsoft?? That must be done by Microsoft working together with your bank. However you can use any bank you choose for your own file. Just select the (Not Listed) choice when you create the account. Then in the Account Details, enter the address, web site URL, etc. ...

Hiding Column and Row Bars.
I know theres a way to costumize the way you view an excel page by hiding toolbars, but is there a way to hide the rows and column bars just so all you can see is the actual page. E.G. is there a way to Hide the top bar that defines the colums "A", "B", "C","D" and Rows 1,2,3,4,5. Go to Tools>Options>View, there are a number of options you can play with there. -- HTH RP (remove nothere from the email address if mailing direct) "tamato43" <tamato43@discussions.microsoft.com> wrote in message news:7D1E5DEB-D6F2-47C8-95E2-2...

I want to stop a colum total and start it from nought (Access2003
I want to total a column of values and when it gets to a signal value, start it from nought from the next result entered. On Tue, 1 Dec 2009 07:05:01 -0800, KevinS <KevinS @discussions.microsoft.com> wrote: >I want to total a column of values and when it gets to a signal value, start >it from nought from the next result entered. > You'll have to post some more description of your table structure and what you mean by a "signal value". You can see your database... nobody else can! -- John W. Vinson [MVP] ...

how to compare 2 values in a report (Invoice Total vs Payments)
while running a report how would I set a message "out of balance" if my invoice amount (Table 1 ) does not equal the value of my total payments (table 2). If the values were the same then no message would be printed. Thank You Create a new command button to check the report before you print it. You will have to enter code for the button along the lines of: If [Invoice Total] <> [Payments] Then MsgBox "This account is out of balance" Endif "Rita" wrote: > while running a report how would I set a message "out of balance" if > my invo...

Sorting Data Automatically
I am importing text into a new worksheet and would like to run a macro that can perform labour a labour intensive sort/deletion. Column A contains a mixture of text strings as follows: QR4567 QR4567/QT1223 QR4290 QR4290/QT1224 What I would like to do is determine how many QR's have associated QT's and how many QR's are remaining that dont have associated QT's. Any assistance would be greatly appreciated. Alan Bartley Sydney Australia Hi Alan not sure how the sorting comes into this - sounds more like needing a formula to count when a cell contains both QR & QT as op...

Mail is queued after adding 2nd server
I was hoping someone might have some insight into this issue. I've just completed adding a 2nd server to my first site. All mail between the two servers is getting queued; doesn't matter which server it originates from. Mail to the original server from the internet as well as auto generated emails is still functioning correctly. We're running Exchg 2003 SP2 and Win2k3 on both servers. I've also gone through the Best Practices and fixed the minor issues it listed. Thanks, Chris Can you telnet to port 25 from each server successfully? Are the servers located in the same su...