Sales spreadsheet  amounts and formulas dependent on what is selected in dropdown menus

Wayne Contello Austin, Texas
you can use the match() or vlookup() function. Can you post a representative example (remove private stuff)?
Wayne

Like (0)


Okay, the top table is where I'll enter individual sales  one row for each sale. Column A is a popup 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 popup 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 popup 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!

Like (0)


Hi 'kid,
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.
Formulas:
Table 1
F2: =IFERROR(VLOOKUP(D2,Table 3 :: $A:$B,2,FALSE),IF(LEN(E2)>0,"not found",""))
G2: =IF(LEN(E2)>0,E*F,"")
Table 2:
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.
Regards,
Barry
PS: You may eventually want to limit the totals to sales within a specific time period. For that, take a look at SUMIFS.
B

Like (0)


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.

Like (0)


No bother; come back when you have further questions or another question on a different topic.
Regards,
Barry

Like (0)
