5 Replies Latest reply: Dec 3, 2012 6:49 PM by Barry
shineykid Level 1 Level 1 (0 points)

Hi, I'm revising the spreadsheet we use for monthly sales reports, and have two questions about how I can make Numbers recognize what item is selected in a pop-up menu, and then how I can get it to do certain things.

 

1. I have a pop-up menu with all of the items for sale in our store.  The next column is the price of each item.  After I select an item in the pop-up menu, I want the price to magically appear in the price column.  Is there a formula I can use to have Numbers read the pop-up menu and enter a certain value automatically?

 

2. I also have a separate table with total sales by employee.  In the table above, each line has a pop-up menu with a list of all the employees here at the store.  In this second table, I have a row for each employee, with one cell for their name and another for total sales.  In the sales cell, I want a total of all the values in the price column, but only the rows with that individual selected in the pop-up menu.  How do I formulate this?  Thanks!


iWork Numbers
  • 1. Re: Sales spreadsheet - amounts and formulas dependent on what is selected in drop-down menus
    Wayne Contello Level 6 Level 6 (13,620 points)

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

     

    Wayne

  • 2. Re: Sales spreadsheet - amounts and formulas dependent on what is selected in drop-down menus
    shineykid Level 1 Level 1 (0 points)

    Screen shot 2012-11-30 at 1.11.30 PM.png

    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!

  • 3. Re: Sales spreadsheet - amounts and formulas dependent on what is selected in drop-down menus
    Barry Level 7 Level 7 (29,180 points)

    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.

     

    Picture 26.png

    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

  • 4. Re: Sales spreadsheet - amounts and formulas dependent on what is selected in drop-down menus
    shineykid Level 1 Level 1 (0 points)

    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.

  • 5. Re: Sales spreadsheet - amounts and formulas dependent on what is selected in drop-down menus
    Barry Level 7 Level 7 (29,180 points)

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

     

    Regards,

    Barry