Excel problem

[Steve R.]

Problem: I have created two spreadsheets.
The first I'll call the Master Costing sheet with two columns. A =
Description and B=cost.

The second I'll call Product style #ABC.

Situation: On Product style #ABC sheet, I have to enter a cost into a cell
for a component part. To do so, I am added a "+" and then switching to the
Master Costing Sheet, highlighting a specific cell and hitting ENTER. This
of course places the value back into the first sheet.

PROBLEM - I have added some lines in the middle of the Master Costing Sheet
which has changed the information flowing back to the Product style #ABC
sheet.

QUESTION: How can I make sure that, if I add lines to the Master Costing
Sheet, it will logically assume that I want to keep the same values flowing
across so, for example, B23 which was $1.57 is now B24 due to the additional
line and it will know to use B24 and not B23.

 

[Mikeopolo]

Sounds like you need to reference the component cost using a vlooku
function, for which you need a range with the first column sorted, an
in your case a second column.

You probably have the required component code on the style sheet.

Then the formula becomes =vlookup(source value, target range,targe
column number,FALSE)
Source value is the cell with your component code
Target range is the range on the master cost sheet
Target column number will be 2 in your case (ie the result is returne
from the second column, your costs)
False means only exact match will be accepted

Works better if you give your target range (ie your component codes an
costs) a range name.

Regards
Mik