DaishunJ
Level 1 (4 points)
iWork

Q: numbers column recall

Hi,

 

I'm using numbers v3.6.1.

 

We have a database of all equipment we own with rows stating the make, model number, serial number, description price per day and so on.

 

What I want to be able to do is create an invoice on a separate sheet and type the model number into the corresponding column on the invoice then have numbers automatically fill the rest of the information in.

 

Is this possible?

 

hopefully this makes sense. 

MacBook Pro, OS X Yosemite (10.10.5)

Posted on Sep 5, 2016 12:09 PM

Close
DaishunJ

Q: numbers column recall

  • All replies
  • Helpful answers

  • by Wayne Contello,Helpful

    Wayne Contello Wayne Contello Sep 6, 2016 4:17 AM in response to DaishunJ
    Level 6 (18,960 points)
    iWork
    Sep 6, 2016 4:17 AM in response to DaishunJ

    This may work for you:

    Screen Shot 2016-09-05 at 3.36.44 PM.png

     

    The table on the left is your Database of equipment to rent AND is named "Equipment"

    The table on the right is an invoice example that takes information from the database.

     

    For the table on right...

    A2=IF(COUNTA(B2:C2)>1, ROW()−1, "")

     

    this is shorthand for... select cell A2, then type (or copy and paste from here) the formula:

    =IF(COUNTA(B2:C2)>1, ROW()−1, "")

     

    columns B and C are where you enter information (in this example the columns are shaded white where you enter information)

     

    D2=IF(A2≠"",OFFSET(Equipment::A$1,MATCH(C2,Equipment::B,matching-method)−1, 0,rows,columns), "")

    E2=IF(A2≠"",VLOOKUP($D2,Equipment::$A:$F,MATCH(E$1, Equipment::$1:$1,matching-method), 0) , "")

    select cell E2, copy

    select cells E2 thru H2, paste

    I2=IF(A2≠"",H2×B2, "")

     

    select cells A2 thru I2, copy

    select cells A2 thru the end of column I, paste

     

    You can add a footer row to sum the total for the invoice and compute tax and other stuff.  post back if you need help with that.

  • by DaishunJ,

    DaishunJ DaishunJ Sep 6, 2016 4:18 AM in response to Wayne Contello
    Level 1 (4 points)
    iWork
    Sep 6, 2016 4:18 AM in response to Wayne Contello

    Thanks for this Wayne.

     

    I believe I understand the equations however if I get stuck ill be sure to return.

     

    Thanks again.