hello Guys
Here is my dilema. I made a drop down list in numbers. so far so good. now the problem is that I need to be able (if possible) to associate a price for the list. I will gice an eample below. so column A is a drop down list containing multiple kinds of fish. and when I select a fish type, I would like column B to put the price automatically is that possible?
A B
blue crab 10.00
red crab 8.00
iPad 2, Mac OS X (10.7.2)
Hi Juan,
One of the LOOKUP functions will do the job for you. With all the possible column A entries existing on a pop-up menu, LOOKUP would be a good choice.
On the left is your table with pop-up mnu cells in column A. Note that in the last row, no choice has yet been made.
To have this cell display as 'blank' you should have a menu item that consists of a single space, and make this item the default one.
On the right is the price lookup table. note that this also has a pair of 'blank' cells. like the menu item, both of these cells contain a single space.
There is one formula used:
Main::B2 (and filled down): =LOOKUP(A2,Lookup :: A,Lookup :: B)
Regards,
Barry
barry
I appreciate your response. But I am now more confused than before. can you explain in laymans terms how this gets accomplished. do I need to create another sheet with the data for the LOOKUP tool to "look in". what I am tryin to create is an invoice where by selecting a value from the drop down list, it will give the unit price. then I did a formula on abother column where I multiply the result from the lookup tool (if I could get it to work) to I get the total value on the invoice per unit. as of now I am doing amnually, but would love to have automated. thanks.
Hi Juan,
LOOKUP is a function.
In the example above, the formula "=LOOKUP(A2,Lookup :: A,Lookup :: B)" is placed in cell B2 of the "Main" table, then copied into the rest of the cells in that column using the Fill control handle. Numbers automatically adjusts the cell reference ( A2) in the formula to point to the column A cell in the same row as that copy of the formula (so in cell B10, the formula will read "=LOOKUP(A10,Lookup :: A,Lookup :: B)"
What it does:
The syntax for LOOKUP is: LOOKUP(search-for, search-where, result-values)
The formula (in B2) is: =LOOKUP(A2,Lookup :: A,Lookup :: B)
search-for: A2 This tells LOOKUP to search for the value you have set using the pop-up menu cell in cell A2.
search-where: Lookup :: A This tells LOOKUP to search for that value in column A of the table named "Lookup". In the example, LOOKUP will find the value "red crab" in cell A4 of "Lookup"
result-values: Lookup :: B This tells LOOKUP to return the value from the cell in column B of the Table named "Lookup" that is on the same row as the cell in which it found the value "red crab". Lookup returns the value "3.00" from cell B4 of "Lookup", and shows that value in its cell (B2, on "Main", the cell that contains the formula.
How to use the result:
The result in B2 is a number, and may be used in further calculations.
You want to use that value (the unit price) to calculate the price of an order of several units.
You will need a column in which to record the number of units, and a column in which to calculate and display the result, often labeled "extended"
Here I've inserted column B (Qty) where the number of units of each item is entered as a number.
Column C contains the LOOKUP formula (same as before).
Column D contains the formula that calculates the extended price for the number of units specified in column B:
D2: =B*C
Fill down to the rest of the cells in the column.
The error triangle shown is due to the 'price' of a 'rosy dawn' being specified as a text string. For actual use, all prices need to be specified as numbers.
Regards,
Barry
PS: I'd highly recommend downloading the Numbers '09 User Guide, reading all of the first and third chapters, and looking through chapters 4 and 6 for a better understanding of Numbers's vocabulary and of how Numbers works.
You might also want to download the iWork Formulas and Functions User guide, a reference to the Functions available in Numbers, with examples and descriptions for each.
Both guides are available through the Help menu in Numbers.
B
Barry,
You are THE MEN!. thanks. I got it working with your help. Best regards.
Thanks again.
Numbers in Max Lion
