| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using auto-fill to populate a table using data from several tabs | Excel Worksheet Functions | |||
| find, match, and count to populate table | Excel Worksheet Functions | |||
| Auto Populate Cells | Setting up and Configuration of Excel | |||
| Auto populate cells | Excel Discussion (Misc queries) | |||
| Conditional format if cell match found in another range of cells | Excel Worksheet Functions | |||
Hybrid Mode
