6 Replies Latest reply: May 8, 2013 1:53 PM by scottfrombennington
scottfrombennington Level 1 Level 1 (0 points)

I would like to create a pricing program of sorts with numbers.

 

First, I want to create a source document listing all my product codes in the first column followed by four columns containing the list prices and relevant information.

 

Then I want to create a second spreadsheet that will look like an order form in which I can type in a product code (matching the source page identically) and have numbers automatically fill in the remaining columns from the source document onto the 'order form' page.  Is there a way to get numbers to recognize a product code and fill in the corresponding info from a source document into another sheet?

 

I've been playing around with the LOOKUP and HLOOKUP function, but can't seem to get it figured out.

 

Thanks!


iMac, OS X Mountain Lion (10.8.2)
  • 1. Re: LOOKUP formula - I think
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Scott,

     

    VLOOKUP would be just the thing for this situation. It's not the only way, but it would be perfectly fine. If you want some help writing the expressions, post a screen shot of your tables so we can give specific advice.

     

    Jerry

  • 2. Re: LOOKUP formula - I think
    Barry Level 7 Level 7 (29,180 points)

    Hi Scott,

     

    LOOKUP will work. VLOOKUP may be a better choice, as you can specify an exact match. LOOKUP always accepts a 'close match', meaning 'The largest alue equal to or less than the search value.'

     

    Here's a sample:

    Screen Shot 2013-05-08 at 10.45.45 AM.png

     

    Data table contains enterd data; no formulas.

     

    Columns C, D and E of order contain formulas. Each is entered in row 2, and filed down the rest of the column.

     

    C2: =IFERROR(VLOOKUP($B,Data :: $A:$D,2,FALSE),"")

    D2: =IFERROR(VLOOKUP($B,Data :: $A:$D,4,FALSE),"")

     

    E2: =IF(LEN(D)>0,A2*D2,"")

     

    Functions used are described in the iWork Formulas and Functions User Guide. The guide is available for download via the Help menu in Numbers '09.

     

    Regards,

    Barry

  • 3. Re: LOOKUP formula - I think
    scottfrombennington Level 1 Level 1 (0 points)

    Jerry,

     

    Thank you so much for your help.  I will begin experimenting with VLOOKUP function and see if I can figure it out.

     

    Here is some more info about what I'm trying to accomplish and how it looks in the source document.  Basically.  I would like to be able to type in the product code onto the 'order form' page (for example 'B12') and have numbers automatically fill-in the info  from cells C4:I4 of the source document (sheet 2) to the 'Order Form' page (sheet 1).  I would like to keep the column spacing the same when it auto fills if that's possible as well.  I'm fairly new and this is really my first attempt at anything beyond the very basic functions so I greatly appreciate your help!

     

    Here is a screen shot of a portion of the source document:

     

    Screen Shot 2013-05-08 at 1.08.00 PM.png

     

    Thanks again,

    Scott

  • 4. Re: LOOKUP formula - I think
    scottfrombennington Level 1 Level 1 (0 points)

    Thank you Barry!

     

    I believe what you're describing is very similar to (if not exactly) what I'm trying to do.  I'm going to experiment with it and see if I can get it to work.  I really appreciate your help! 

     

    Thank you again for your advice!

    Scott

  • 5. Re: LOOKUP formula - I think
    Barry Level 7 Level 7 (29,180 points)

    Hi Scott,

     

    Spacing on the order form is set when you set up that form. If you want to match the spacing on the source document, then duplcate the source table, delete the data from the duplicate, and rpplace it with the formulas.

     

    If you are transferring data from consecutive columns, you can replace the 2 and 4 in my two VLOOKUP formulas with COLUMN()-n.  Adjust n to return the correct data from the first column to be transferred, then fill the formula right to pick up succeeding columns.

    Here's a revised sample with the "Notes" column added and the VLOOKUP formula revised:

    Screen Shot 2013-05-08 at 11.23.48 AM.png

     

    C2: =IFERROR(VLOOKUP($B,Data :: $A:$D,COLUMN()-1,FALSE),"")

    Fill right to E2. Fill down to end of table.

     

    Regards,

    Barry

  • 6. Re: LOOKUP formula - I think
    scottfrombennington Level 1 Level 1 (0 points)

    Barry,

     

    I just want to say thank you!  I've been messing around with it for a couple hours and have gotten it to work exactly as you described. 

     

    Thank you again for all your help!!

    Scott