Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a row listing based on a lookup value
I want to allow a user to enter a data value in a cell, then create a row
listing based on looking up the user entered value in a table array. I have the following sample data listed on the "Data" tab of a workbook. ASR TABLE AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN The user wants to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2. Thus, on a second tab, named "User Entry", the user will enter a SecIDKey value in a cell (A1), and then I want the formula to create a row listing of the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4). I've seen several posts that hint at how to do this, but haven't found one yet that explicitly defines how to accomplish this. Thanks in advance for assistance. -- Thanks, Jen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a row listing based on a lookup value
Take a look on Advanced Filter in Help menu
"JBush" wrote: I want to allow a user to enter a data value in a cell, then create a row listing based on looking up the user entered value in a table array. I have the following sample data listed on the "Data" tab of a workbook. ASR TABLE AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN The user wants to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2. Thus, on a second tab, named "User Entry", the user will enter a SecIDKey value in a cell (A1), and then I want the formula to create a row listing of the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4). I've seen several posts that hint at how to do this, but haven't found one yet that explicitly defines how to accomplish this. Thanks in advance for assistance. -- Thanks, Jen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a row listing based on a lookup value
Thanks for the tip, but I'm not looking to filter.
I specifically want to allow the user enter data (the SecIDKey = SID2) on the "User Entry" tab, and then have a formula that will lookup the corresponding rows on the "Data" tab where SecIDKey = SID2, and then populate those rows on the "user Entry" tab. Make sense?? -- Thanks, Jen "Teethless mama" wrote: Take a look on Advanced Filter in Help menu "JBush" wrote: I want to allow a user to enter a data value in a cell, then create a row listing based on looking up the user entered value in a table array. I have the following sample data listed on the "Data" tab of a workbook. ASR TABLE AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN The user wants to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2. Thus, on a second tab, named "User Entry", the user will enter a SecIDKey value in a cell (A1), and then I want the formula to create a row listing of the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4). I've seen several posts that hint at how to do this, but haven't found one yet that explicitly defines how to accomplish this. Thanks in advance for assistance. -- Thanks, Jen |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a row listing based on a lookup value
One way which delivers using non-array formulas ..
In sheet: Data, Assume data in cols A to G, from row3 down, where col A = AcctSecRefKey col C = SecIDKey In sheet: User Entry, Assume the input for SecIDKey will be made in B1 Put in A2; =IF(OR($B$1="",Data!C3=""),"",IF($B$1=Data!C3,ROW( ),"")) (Leave A1 blank) Put in B2: =IF(ROW(A1)COUNT(A:A),"",INDEX(Data!A:A,SMALL(A:A ,ROW(A1))+1)) Select A2:B2, copy down to cover the max expected extent of data in "Data". Hide away col A. Col B will return the required results for the input in B1, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JBush" wrote: Thanks for the tip, but I'm not looking to filter. I specifically want to allow the user enter data (the SecIDKey = SID2) on the "User Entry" tab, and then have a formula that will lookup the corresponding rows on the "Data" tab where SecIDKey = SID2, and then populate those rows on the "user Entry" tab. Make sense?? -- Thanks, Jen "Teethless mama" wrote: Take a look on Advanced Filter in Help menu "JBush" wrote: I want to allow a user to enter a data value in a cell, then create a row listing based on looking up the user entered value in a table array. I have the following sample data listed on the "Data" tab of a workbook. ASR TABLE AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN The user wants to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2. Thus, on a second tab, named "User Entry", the user will enter a SecIDKey value in a cell (A1), and then I want the formula to create a row listing of the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4). I've seen several posts that hint at how to do this, but haven't found one yet that explicitly defines how to accomplish this. Thanks in advance for assistance. -- Thanks, Jen |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a row listing based on a lookup value
Good to hear that, Jen !
Thanks for feeding back .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JBush" wrote: Thanks, Max!! That did exactly what I needed - plus, I got to learn some new Excel tricks...which I always like! I appreciate the help! -- Thanks, Jen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a formatted 'readable' document based on Excel document? | Excel Discussion (Misc queries) | |||
How to create one table based on filtered data from various worksh | Excel Discussion (Misc queries) | |||
Create Charts based on data in Table | Charts and Charting in Excel | |||
How dynamically create routing slip based on names in specific cel | Excel Discussion (Misc queries) | |||
Lookup based on 2 criteria | Excel Worksheet Functions |