Excel Question

[Maxflyer]

I am working on an excel spreadsheet where I have a range of headings:

Order no: Date In: Type: Qty: Date Out: Price: Per unit: Total:

I have a seperate worksheet with a list of products by Type: Description: Price:

Is there an easy way to enter a type number into the first sheet and then Excel to automatically look at the other worksheet and pull out the description and price?

Hope this makes some sort of sense.

Thanks in advance.

MF

[ExGrunt]

Hi MF,

1. This answer depends on the type number being unique.

2. When you say separate Worksheet, I am assuming a different worksheet within the same workbook.

If so, then:

Sheet 1 has:

--A----------B-------------C
Type: Description: Price:

Select column A, then click Insert, Name, Define,

In the Names in workbook: box enter: Type

The Refers to: box should contain =Sheet1!$A:$A.

Click Add.

Click Close.

Select column B repeat the above entering Description in the Names in workbook box.
Select column C repeat the above entering Price in the Names in workbook box.

Populate the above table with your type description and price data.

Save the workbook!

Sheet 2 has:

A--------------B---------C-----D-------E----------F--------G--------H
Order no: Date In: Type: Qty: Date Out: Price: Per unit: Total:


Assuming Row 2 is the first row with data, enter this formula in cell F2:

=IF(A2="","", INDEX(Price,MATCH(C2,Type,0),1))

Then copy the formula down.

You do not have a column for the description in your example, but the formula for that column is:

=IF(A2="","", INDEX(Description,MATCH(C2,Type,0),1))

Hope this helps

EG

[R4+Z]

Wouldn't the Vlookup command be a simple way of doing this?

[Keef]

Sounds like a candidate for one of the LOOKUP functions, for sure. I've got a few spreadsheets here that do just that.

[ExGrunt]

@MF

I assume you received the example I sent to you OK.

@R4+Z

Yes, you can use VLOOKUP, However for business applications it is less robust than the above approach.

Try this example:

In cells A1:A4 enter: 1,2,3,4
In cells B1:B4 enter: a,b,c,d
In cells C1:C4 enter: e,f,g,h

In Cell A5 enter: Look up Value
In Cell A6 enter: 3

In Cell A7 enter: Result
In Cell A8 enter: =VLOOKUP(A6,A1:C4,3)

Cell A8 evalutes to: g

Circumstances change, so you need to add another column.
Select column B, then click Insert, Columns

You will see that cell A8 is now =VLOOKUP(A6,A1:D4,3) and evaluates to c

This occurs because the 3 in =VLOOKUP(A6,A1:D4,3) is an absolute reference to the columns in A1:D4 and does not get updated.

It is an insidious error and easily missed. Why risk it?

EG

[Maxflyer]

EG. Thanks for that. I am using the example you originally sent.

Thanks also to Keef and R4+Z (is that a formula I should use?)

Cheers

MF