Auto populate data from a table into cells after a match is found

[CrashOz]

I have 2 worksheets one with a table that has 500 rows with 4 coloums of data
for each row as per below

Stock Code Stock Name Margin Required Shortable
AAC AUST AG CO FPO 35% Yes
AAB AUST FPO 40% No

on the other worksheet i would like to enter a Stock code, Say "ACC" into a
cell and then the cells beside it auto populate with the "Stock Name",
"Margin" and "Shortable" values that matches to the "Stock Code" entered.

Any help would be great.

Crash

 

[carlo]

I have 2 worksheets one with a table that has 500 rows with 4 coloums of data
for each row as per below

Stock Code  Stock Name             Margin Required  Shortable
AAC       AUST AG CO FPO        35%               Yes
AAB       AUST FPO      40%               No

on the other worksheet i would like to enter a Stock code, Say "ACC" into a
cell and then the cells beside it auto populate with the "Stock Name",
"Margin" and "Shortable" values that matches to the "Stock Code" entered.

Any help would be great.  

Crash

You could set it up like that

A1: Inputfield
B1: =indirect("Sheet1!B" & E1)
C1: =indirect("Sheet1!C" & E1)
D1: =indirect("Sheet1!D" & E1)
E1: =MATCH(A1,Sheet1!A:A,0)

hth

Carlo

 

[CrashOz]

Thanks Carlo, You have got me 95% of the way the E1: =MATCH(A1,Sheet1!A:A,0)
comes up trumps and gives me the row number that is right but i have been
unable to to get B1, C1, D1 to work, all come up with "#REF!"

For example the Formula in B1 is =INDIRECT("Margin List!B" & E1)

i cant get it to work, any idea's? What am i doing wrong?

Crash

 

[Pete_UK]

As you have a space in the sheet name, you have to put apostrophes
around it, like so:

=INDIRECT("'Margin List'!B" & E1)

An alternative approach would be to use INDEX, like this:

=INDEX('Margin List'!B:B,E1)

Hope this helps.

Pete

 

[CrashOz]

Thanks Pete & Carlo,
Great stuff

As you have a space in the sheet name, you have to put apostrophes
around it, like so:

=INDIRECT("'Margin List'!B" & E1)

An alternative approach would be to use INDEX, like this:

=INDEX('Margin List'!B:B,E1)

Hope this helps.

Pete

 

[Pete_UK]

You're welcome.

Pete

Thanks Pete & Carlo,
Great stuff









- Show quoted text -