Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Royally Confused!!
Hello all...
here is my situation: On Sheet2 of my workbook I have a set of data that starts at B3:B9. Next to column "B" I have column "C-F" with data that corresponds to each row of "B". For example: B2=Stock C-F2=price,PE, etc... I have highlighted the stocks listed in column "B" and defined a name for them. On Sheet1 I have gone through the validation and set up the pull down menu where I can select the stock from Sheet2. But my question is how can I set it up so that when I choose the a stock from the pull down list in the adjacent columns it returns the values from columns C-F? I know this is a round about way to ask a question, I just want to provide as much information as possible. Thank you all so much, Confused Man |
#2
|
|||
|
|||
Hi
Define an additional named range, which includes all data in columns B:F. My advice is to use a dynamic named range. P.e. when your table doesn´t include any gaps (fully empty rows), in row 2 are column headers, and the cell B1 is always empty, then you can define a named range YourTable=OFFSET(Sheet2!$B$2,1,,COUNTIF(Sheet2!$B: $B)-1,5) , which always will contain all rows with data in your table - and only those. Now, when the cell with data validation list on Sheet1 is in A1, then to return the corresponding value from column C on Sheet2 you can use the formula: =VLOOKUP(A1,YourTable,2,0) To return the value from column D: =VLOOKUP(A1,YourTable,3,0) etc. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Confused Man" <Confused wrote in message ... Hello all... here is my situation: On Sheet2 of my workbook I have a set of data that starts at B3:B9. Next to column "B" I have column "C-F" with data that corresponds to each row of "B". For example: B2=Stock C-F2=price,PE, etc... I have highlighted the stocks listed in column "B" and defined a name for them. On Sheet1 I have gone through the validation and set up the pull down menu where I can select the stock from Sheet2. But my question is how can I set it up so that when I choose the a stock from the pull down list in the adjacent columns it returns the values from columns C-F? I know this is a round about way to ask a question, I just want to provide as much information as possible. Thank you all so much, Confused Man |
#3
|
|||
|
|||
Thank you that worked great... now a couple more questions if you don't
mind... right now if nothing is chosen in the pulldown list the Vlookup returns #N/A. How can I get these cells to remain blank unless something is chosen from the list? also, one of the Vlookup cells I want to multiply a manually entered value but if I don't enter a value I want it to ignore that operation and just return the value from the original dataset. For example. Vlookup(B2,datatable,columns) should return a value of 1. but it's looking at the cell I want to multiply and returning a 0, but I want it to ignore the cell if it's blank. I'm pretty sure I need to use an IF statement, but I'm not sure how to go about that. Thank you all so very much for all your excellent help, Confused Man "Arvi Laanemets" wrote: Hi Define an additional named range, which includes all data in columns B:F. My advice is to use a dynamic named range. P.e. when your table doesn´t include any gaps (fully empty rows), in row 2 are column headers, and the cell B1 is always empty, then you can define a named range YourTable=OFFSET(Sheet2!$B$2,1,,COUNTIF(Sheet2!$B: $B)-1,5) , which always will contain all rows with data in your table - and only those. Now, when the cell with data validation list on Sheet1 is in A1, then to return the corresponding value from column C on Sheet2 you can use the formula: =VLOOKUP(A1,YourTable,2,0) To return the value from column D: =VLOOKUP(A1,YourTable,3,0) etc. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Confused Man" <Confused wrote in message ... Hello all... here is my situation: On Sheet2 of my workbook I have a set of data that starts at B3:B9. Next to column "B" I have column "C-F" with data that corresponds to each row of "B". For example: B2=Stock C-F2=price,PE, etc... I have highlighted the stocks listed in column "B" and defined a name for them. On Sheet1 I have gone through the validation and set up the pull down menu where I can select the stock from Sheet2. But my question is how can I set it up so that when I choose the a stock from the pull down list in the adjacent columns it returns the values from columns C-F? I know this is a round about way to ask a question, I just want to provide as much information as possible. Thank you all so much, Confused Man |
#4
|
|||
|
|||
Hi
"Confused Man" wrote in message ... Thank you that worked great... now a couple more questions if you don't mind... right now if nothing is chosen in the pulldown list the Vlookup returns #N/A. How can I get these cells to remain blank unless something is chosen from the list? =IF(ISERROR(VLOOKUP(...)),"",VLOOKUP(...)) or =IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...)) also, one of the Vlookup cells I want to multiply a manually entered value but if I don't enter a value I want it to ignore that operation and just return the value from the original dataset. =IF(Multiplyer="",1,Multiplyer)*VLOKUP(..) For example. Vlookup(B2,datatable,columns) should return a value of 1. but it's looking at the cell I want to multiply and returning a 0, but I want it to ignore the cell if it's blank. I'm pretty sure I need to use an IF statement, but I'm not sure how to go about that. Arvi Laanemets |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
confused by COLUMN worksheet function | Excel Discussion (Misc queries) | |||
Sorry I am confused | Excel Discussion (Misc queries) | |||
check off, look up forms - VERY confused | Excel Discussion (Misc queries) | |||
Frank, Sumproduct, it works but I am confused | Excel Worksheet Functions |