Excel/Help

Advertisement

Expert: Nathan Head - 9/29/2009


Question

Price database
I have a database which has the categories as follows:
A        B       C        D                                D
UPC   UPC   UOM   Description                     Cost
702316   50151   M   6"Unistrut Support Clamp        5
980100   23440   M   500 THHN  STR CU               222
980100   23445   M   600 THHN 37 STR CU BLK 500R WIRE   111
980100   23450   M   750 THHN STR CU BLK 500R WIRE          4
980100   23457   M   1000 THHN STR CU BLK 500R WIRE         3
980100   22300   M   #14 THHN, Sol CU                 6
__________________________________________________________________


My goal is to import a duplicate list of items from a distributor in the same format as I have above.

The new items will be pasted in cells H-I-J-K-L

_____________________________________________________________

I want to update the original prices with the newly imported prices by matching the UPC code( the B column)

_________________________________________________________________

The only way that I can figure out how to do this is with the following code

=INDEX($L$1:$L$8000,MATCH(B1,$I$1:$I$8000,0))

_________________________________________________________________

This seems to work fine when aplied to coulmn E

______________________________________________________________

Question #1 How can I apply this code (=INDEX($L$1:$L$8000,MATCH(B1,$I$1:$I$8000,0)) in cells E1:E10,000 with out dragging the code down all of the cells?


Question #2 is there a better way to acomplish this?
                
____________________________________________________________

I have tried to attach an example.

Thanks,
steve  

Answer
Steve:

The formula you have chosen is one of the best choices to use.  You've done the hard work already - now it is just a matter of copying the formula to all of the existing cells.

I'm not 100% certain, but it sounds like the problem is "you don't want to drag the formula" down through a series of 10,000 cells.  If that is the case, then yes, dragging would be very inefficient.  Your best solution is to simply use "copy" and "paste".  Copy one of the existing formulas, select cells E1:E10000, and then choose paste.  Excel will update the references automatically since for each row.

To select cells E1:E10000 it is probably easiest to select cell E10000 first, hold down the shift key, and then select cell E1 - this will select all of the cells in between.

Good luck!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Nathan Head

Expertise

Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (EXCEPT Visual Basic Coding/Programming and Macros, I don't have any expertise there).

Experience

I have been using spreadsheets since Lotus 1-2-3 was released. As a CPA, I use spreadsheets every day at work.

Education/Credentials
CPA, Texas

©2009 About.com, a part of The New York Times Company. All rights reserved.