Currently Being ModeratedMay 8, 2013 10:39 AM (in response to scottfrombennington)
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.
Currently Being ModeratedMay 8, 2013 10:51 AM (in response to scottfrombennington)
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:
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),"")
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.
Currently Being ModeratedMay 8, 2013 11:09 AM (in response to Jerrold Green1)
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:
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!
Currently Being ModeratedMay 8, 2013 11:25 AM (in response to scottfrombennington)
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:
C2: =IFERROR(VLOOKUP($B,Data :: $A:$D,COLUMN()-1,FALSE),"")
Fill right to E2. Fill down to end of table.