Skip navigation

Sales spreadsheet - amounts and formulas dependent on what is selected in drop-down menus

279 Views 5 Replies Latest reply: Dec 3, 2012 6:49 PM by Barry RSS
shineykid Calculating status...
Currently Being Moderated
Nov 30, 2012 11:53 AM

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
  • Wayne Contello Level 6 Level 6 (12,660 points)

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

     

    Wayne

  • Barry Level 7 Level 7 (29,095 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

  • Barry Level 7 Level 7 (29,095 points)

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

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.