Currently Being ModeratedNov 30, 2012 12:16 PM (in response to shineykid)
you can use the match() or vlookup() function. Can you post a representative example (remove private stuff)?
Currently Being ModeratedNov 30, 2012 1:19 PM (in response to Wayne Contello)
Okay, the top table is where I'll enter individual sales - one row for each sale. Column A is a pop-up menu with all the employees' names (for now, it ony has Brad and Tony [me], but we'll ultimately add 6 or 7 more people). Column D is a pop-up menu with all the items we sell in the store.
First, I want an amount to be entered automatically in column F (Price) after I select an item from the pop-up menu in Column D.
Second, as we enter each sale, I want the second table to update with a sum of all the values in Column F separated by employee - the sum of everything in Column F on all the rows where Brad is selected in Column A, the sum of all the values in column F with my (Tony) name selected in Column A, and then a total. Later, we're going to add more people, but once I have the inital formula, I shouldn't have any trouble with updating it. But for now, I don't even know where to begin - thanks for your help!
Currently Being ModeratedNov 30, 2012 7:11 PM (in response to shineykid)
From your screen shot, I'm assuming
-- In Table 1 (top)
-- The sales person's name for each sale is listed in column A, starting in A2,
-- the item sold is in the same row, in column D,
-- the number of items sold is entered into column E,
-- the price of that item is to be retrieved from a separate table (Table 3), and inserted in column F
-- the formula =E*F is used in column G to calculate the total price for the sale of that item.
In Table 2
-- Brad's name is in A2 and his total is to be reported in B2.
-- Tony's name is in A3 and his total is to be reported in B3.
-- Other names will be added below Tony, and their totals reported in the same row, in column B.
Table 3 will be a lookup table containing the item names and the price for one of that item, listed in columns A and B respectively.
F2: =IFERROR(VLOOKUP(D2,Table 3 :: $A:$B,2,FALSE),IF(LEN(E2)>0,"not found",""))
B2: =SUMIF(Table 1 :: $A,A2,Table 1 :: $G)
The functions used are defined and discussed in the iWork Formulas and Functions User Guide, available through the Help menu in Numbers '09.
IF(LEN(x)>0...is used to allow the calculation only if there is an entry in the cell referenced by 'x'.
IFERROR traps the 'not found' error that may occur and presents either the appropriate message or a null string.
PS: You may eventually want to limit the totals to sales within a specific time period. For that, take a look at SUMIFS.
Currently Being ModeratedDec 3, 2012 12:23 PM (in response to Barry)
Brilliant, that did it! Thanks so much. Now I need to go back into the formula to figure out how it works so I don't have to bother you again.
Currently Being ModeratedDec 3, 2012 6:49 PM (in response to shineykid)
No bother; come back when you have further questions or another question on a different topic.