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

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Auto populate data from a table into cells after a match is found (https://www.excelbanter.com/excel-worksheet-functions/173045-auto-populate-data-table-into-cells-after-match-found.html)

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

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

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

On Jan 15, 5:21*pm, CrashOz wrote:
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

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

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

"carlo" wrote:

On Jan 15, 5:21 pm, CrashOz wrote:
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

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

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

On Jan 15, 10:36*am, CrashOz
wrote:
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

"carlo" wrote:
On Jan 15, 5:21 pm, CrashOz wrote:
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- Hide quoted text -

- Show quoted text -

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

Thanks Pete & Carlo,
Great stuff

"Pete_UK" wrote:

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

On Jan 15, 10:36 am, CrashOz
wrote:
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

"carlo" wrote:
On Jan 15, 5:21 pm, CrashOz wrote:
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- Hide quoted text -

- Show quoted text -

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

You're welcome.

Pete

On Jan 15, 11:37*am, CrashOz
wrote:
Thanks Pete & Carlo,
Great stuff

"Pete_UK" wrote:
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

On Jan 15, 10:36 am, CrashOz
wrote:
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

"carlo" wrote:
On Jan 15, 5:21 pm, CrashOz wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -