Problems with formulas and sorting

[Guest]

OK, so I have an Excel file that has about 15 sheets. The first is a parts
list and price list (two columns). Each of the remaining pages is a product,
with all the components listed, along with quantities and extended pricing.
The price column is made up of references such as ='Parts List'!B1 then B2,
B3, etc.

Now, I need to reformat the names of the parts on the first list, and then
realphabetize them. The problem is, this throws off the calculations on the
other 15 pages. For example, if B3 becomes B5, I need the formula to update
to say B5 instead of B3. Instead, it still says B3 and has the new price for
B3 listed.

Can anyone tell me how to fix this??

 

[Dave Peterson]

This may not work for you, but I'd put the part number in a cell on those
secondary sheets (not the list sheet).

Then use =vlookup() or =index(match()) to retrieve the stuff associated with
those part numbers.

As you've seen, those formulas are never gonna work correctly once you sort.

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[Guest]

Thanks so much!! I had never used Index/Match before, so I learned something
new and it worked perfectly. The links to the articles explaining how to use
those functions were extremely helpful. Thanks!