Get user data from one sheet in another with Excel 2007

[infinitizon]

Hi all,

I know this is easy but I'm tired of looking into it.

I have an excel file with two sheets. Sheet one called 'customer' looks like so

|------------------------------------|
| A | B |
|------------------------------------|
| Code | Customer Name |
|------------------------------------|
| | |
|------------------------------------|
| | |
|------------------------------------|

A second sheet called 'use data' is like so:

|------------------------------------|
| A | B |
|------------------------------------|
| Code | Customer Name |
|------------------------------------|
| | |
|------------------------------------|
| | |
|------------------------------------|

The customer name column us uneditable in 'use data' sheet
This sheet will be will be sent to different countries.

The idea is this. Users enter customer code and name in the customer sheet like so:

|------------------------------------|
| A | B |
|------------------------------------|
| Code | Customer Name |
|------------------------------------|
| aaa | Authur Burke |
|------------------------------------|
| bbb | Buns Charles |
|------------------------------------|

In 'use data' sheet, when he enters the the code, I want the appropriate Customer Name from the 'Customer' sheet to automatically appear in the in the corresponding row.

I hope I explained myself properly. Any ideas please?


____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[SkipVought]

hi.

Use a lookup formula, like VLOOKUP(). I actually prefer INDEX() le[/b]& MATCH().

If you are using Excel 2007, i would suggest making your tables Struatured Data Tables, using Insert > Tables > Tab

You can NAME your table and it exposes the Field Header Names as well as other usable parts of a table, to be used in formula reference. That feature , along with an IntelaSense like feature, makes constructin formulas a lot more fun and simpler.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[infinitizon]

10x SkipVought,

I'm using excel 2007, but can you show me steps?

Thanks

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[SkipVought]

Let's assume that your Structured Table on usedata is named tUseData, then on the customer sheet...
[tt]
B2: [b=INDEX(tUseData[#Data],MATCH($A2,tUseData

,0),COLUMN())[/b]
[/tt]
Once entered in B2 on the customer sheet, COPY B2 abd PASTE thru all columns/rows of data.

Skip,
[sub]
[glasses]Just traded in my [b]old subtlety[/b]...
for a [b]NUANCE![/b][tongue][/sub]

 

[infinitizon]

Thanks, but I did this
=IF(ISNA(VLOOKUP($A2, Customer!A:B,2,FALSE)),"",VLOOKUP($A2, Customer!A:B,2,FALSE)),

But thanks for your help

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[SkipVought]

I see that you are not making full use of 2007 features:

1. IFERROR() makes a much simpler syntax

2. Structured Tables make your formula much easier to code and maintain.

in 2007...
[tt]
=[highlight]IFERROR[/highlight](VLOOKUP($A2, [highlight]tUseData[#Data][/highlight],2,FALSE),"")
[/tt]





Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

This sheet will be will be sent to different countries.

Then be certain to COPY the data and Edit > Paste Special -- VALUES in order to replace each formula with actual values before sending this sheet anywhere else.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[infinitizon]

Thanks SkipVought,
I never thought of that
Cool!