AutoFill of Numbers

[ithinkican2]

We are designing a quotation sheet. The data is derived from a table above on
the same worksheet that has three columns: A - Catalog #, B - Manufacturing
Price and C - Install Price.

Example:
A B C
Catalog # Mfg Install
A1 2,390. 1,500.
A2 4,304. 2,600.
B1 2,100. 500.

We will be selecting from the Catalog list (with over 50 items in the list)
and need to essentially, AutoFill in the Mfg and Install prices for the item
into the Quotation Sheet. Problem is, Excel 2007 doesn't allow AutoFill of
numbers.

What formula could we use to accomplish this?

 

[Pete_UK]

Suppose your quotation table begins with cell A100, and in this cell
you put a catalogue number. Put this formula in B100:

=IF($A100="","",VLOOKUP($A100,$A$2:$C$80,COLUMN(B1),0))

Then copy this into C100, and then copy B100:C100 down for as many
rows as you think you might need. You can put other catalogue numbers
in A101, A102 etc and the appropriate data will show in B and C
columns.

Hope this helps.

Pete

 

[ithinkican2]

Thank you, Pete,

Your formula makes logical sense and yet, the result is "0".

We will continue working on the formula to get the results we need. My guess
is the problem is in the 'Column' figure - we also tried the Excel suggested
Column range of B:B but to no avail. Excel also suggests in 'Help' to use the
number of the column which in this case would be 3. Also, to no avail.

When I checked out the 'Help' section for VLOOKUP the beginning of the
formula is shown as an actual integer rather than the $A100 suggestion you
made. (Of course using an actual integer in the formula would defeat the
purpose of just typing in a value (Catalog #) in the data range and have the
quotation spit out the correct result (Mfg) from the data table.) We liked
your suggestion much better as it made more sense.

Anyway, thank you for the help, we'll keep slogging through it until we find
the answer.

Your kind response was truly appreciated.

Hope all is well in the U.K.!
-Respectfully,
Angie

 

[Pete_UK]

Hi Angie,

it's getting late in the UK !! <bg>

There are 4 parameters in the VLOOKUP function - the first parameter
($A100) represents a value you are trying to find a match for in the
data table. The second parameter ($A$2:$C$80) is the table where we
are trying to find a match - you said you had more than 50 items, so I
defined the table as up to row 80. So the VLOOKUP function will try to
find a match between the item which is in A100 and the data which is
in A2:A80 - the first column of the table. If it finds a match then it
will return data from the same row but in the column denoted by the
third parameter (COLUMN(B1)) - this returns the value 2, so the data
returned by VLOOKUP will be from the 2nd column of your table, or
column B (manufacturing price). The reason I have used COLUMN(B1)
rather than the number 2 is so that the formula can be copied into the
next column and will automatically adjust to return data from the 3rd
column of your table (i.e. Install price) The final parameter (0) just
tells the function to look for an exact match - if the function does
not find an exact match it will return the error #N/A.

So, if you are getting 0 returned then an exact match has been found,
but the corresponding cell in column B of your data table must either
contain 0 or be blank, and these are returned as a zero by the VLOOKUP
function.

Consequently, you should check your data table.

Hope this helps.

Pete