Royally Confused!!

[Confused Man]

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

 

[Arvi Laanemets]

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.

 

[Confused Man]

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]

Hi

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