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

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

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

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