I have products listed on "products" page that are in column A2 through A800.
For each product I have a cost column in Column C. Columns G through L
contain a dollar amount showing an amount of selling price. Columns G
through L represent whether a product is sold at a 10,15,20,25,35 or a 42 %
margin.
I have a "Main Quote page 1" that has in column E18 through E58 drop down
boxes that allow me to lookup the list on the "products" page. I want to
create a drop down box on the "Main Quote page 1" in Column N a drop down
list that will give me all of the choices of the percentage amounts from the
products page.

ex: Products page

A C G H I J
K L
product Cost 10% 15% 20% 25% 35% 42%
15-0-15 12.95 14.39 15.23 16.19 17.27 19.92 21.58
16-4-8 8.75 9.72 10.29 10.94 11.67 13.46
14.68
28-0-12 12.25 13.89 14.71 15.62 16.67 19.23 20.83

Any suggestions? Thanks ahead of time.

Just give the cells G1:L1 a defined name (InsertNameDefine...), and create
a Data Validation on the cell with an Allow type of List, and use the name
as the reference.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Pete Elbert" wrote in message
...
I have products listed on "products" page that are in column A2 through
A800.
For each product I have a cost column in Column C. Columns G through L
contain a dollar amount showing an amount of selling price. Columns G
through L represent whether a product is sold at a 10,15,20,25,35 or a 42
%
margin.
I have a "Main Quote page 1" that has in column E18 through E58 drop down
boxes that allow me to lookup the list on the "products" page. I want to
create a drop down box on the "Main Quote page 1" in Column N a drop down
list that will give me all of the choices of the percentage amounts from
the
products page.

ex: Products page

A C G H I J
K L
product Cost 10% 15% 20% 25% 35% 42%
15-0-15 12.95 14.39 15.23 16.19 17.27 19.92
21.58
16-4-8 8.75 9.72 10.29 10.94 11.67 13.46
14.68
28-0-12 12.25 13.89 14.71 15.62 16.67 19.23
20.83

Any suggestions? Thanks ahead of time.

Thanks for your quick response on this. I have been working this as you
said but all I get is the following error message - "The list source must be
a delimited list or a reference to a single row or column. The reference
source is located in cells g2 through L849. Can you assist in any more help
with this? Thanks

"Bob Phillips" wrote:

Just give the cells G1:L1 a defined name (InsertNameDefine...), and create
a Data Validation on the cell with an Allow type of List, and use the name
as the reference.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Pete Elbert" wrote in message
...
I have products listed on "products" page that are in column A2 through
A800.
For each product I have a cost column in Column C. Columns G through L
contain a dollar amount showing an amount of selling price. Columns G
through L represent whether a product is sold at a 10,15,20,25,35 or a 42
%
margin.
I have a "Main Quote page 1" that has in column E18 through E58 drop down
boxes that allow me to lookup the list on the "products" page. I want to
create a drop down box on the "Main Quote page 1" in Column N a drop down
list that will give me all of the choices of the percentage amounts from
the
products page.

ex: Products page

A C G H I J
K L
product Cost 10% 15% 20% 25% 35% 42%
15-0-15 12.95 14.39 15.23 16.19 17.27 19.92
21.58
16-4-8 8.75 9.72 10.29 10.94 11.67 13.46
14.68
28-0-12 12.25 13.89 14.71 15.62 16.67 19.23
20.83

Any suggestions? Thanks ahead of time.

So what you want in column N is not the percentages but the actual
prices after markup... If so, assume that you are in row E18 of 'Main
Quote page 1'. In N18 you want to choose one of the prices. Do the
following:

In your target sheet, select N18. : InsertName...Define:
Names in workbook: VarList
Refers To:
=INDIRECT("Products!G"&MATCH($E18,Products!$A:$A,
0)&":L"&MATCH($E18,Sheet1!$A:$A,0))

Now, again in N18, DataValidation... Choose List and enter
=VarList

This can be copied down and to the right.

Does this help?
Kostis Vezerides

On Nov 22, 5:53 pm, Pete Elbert
wrote:
Thanks for your quick response on this. I have been working this as you
said but all I get is the following error message - "The list source must be
a delimited list or a reference to a single row or column. The reference
source is located in cells g2 through L849. Can you assist in any more help
with this? Thanks

"Bob Phillips" wrote:
Just give the cells G1:L1 a defined name (InsertNameDefine...), and create
a Data Validation on the cell with an Allow type of List, and use the name
as the reference.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Pete Elbert" wrote in message
...
I have products listed on "products" page that are in column A2 through
A800.
For each product I have a cost column in Column C. Columns G through L
contain a dollar amount showing an amount of selling price. Columns G
through L represent whether a product is sold at a 10,15,20,25,35 or a 42
%
margin.
I have a "Main Quote page 1" that has in column E18 through E58 drop down
boxes that allow me to lookup the list on the "products" page. I want to
create a drop down box on the "Main Quote page 1" in Column N a drop down
list that will give me all of the choices of the percentage amounts from
the
products page.

ex: Products page

A C G H I J
K L
product Cost 10% 15% 20% 25% 35% 42%
15-0-15 12.95 14.39 15.23 16.19 17.27 19.92
21.58
16-4-8 8.75 9.72 10.29 10.94 11.67 13.46
14.68
28-0-12 12.25 13.89 14.71 15.62 16.67 19.23
20.83

Any suggestions? Thanks ahead of time.

L849 as the Kostis,
When you said to go to the target sheet, N18, I assume you meant the "main
quotes page1" page? or were you refering to the "products" page?
None-the-less, I put the formula you gave me into the "main quotes page1"
page and the formula took. However, toward the end of the formula you have
listed"sheet1!" and I do not have a sheet1. I also went into the "products"
page and defined cells G2:L849 as "VarList". Now, when I go back to the Main
Quote Page1 and go to N18 and
validate this cell as list and =varlist, the error statement comes back to
me as "The list source must be a delimited list or a reference to a single
row or column still. Any help is so very appreciated. Pete


"vezerid" wrote:

So what you want in column N is not the percentages but the actual
prices after markup... If so, assume that you are in row E18 of 'Main
Quote page 1'. In N18 you want to choose one of the prices. Do the
following:

In your target sheet, select N18. : InsertName...Define:
Names in workbook: VarList
Refers To:
=INDIRECT("Products!G"&MATCH($E18,Products!$A:$A,
0)&":L"&MATCH($E18,Sheet1!$A:$A,0))

Now, again in N18, DataValidation... Choose List and enter
=VarList

This can be copied down and to the right.

Does this help?
Kostis Vezerides

On Nov 22, 5:53 pm, Pete Elbert
wrote:
Thanks for your quick response on this. I have been working this as you
said but all I get is the following error message - "The list source must be
a delimited list or a reference to a single row or column. The reference
source is located in cells g2 through L849. Can you assist in any more help
with this? Thanks

"Bob Phillips" wrote:
Just give the cells G1:L1 a defined name (InsertNameDefine...), and create
a Data Validation on the cell with an Allow type of List, and use the name
as the reference.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Pete Elbert" wrote in message
...
I have products listed on "products" page that are in column A2 through
A800.
For each product I have a cost column in Column C. Columns G through L
contain a dollar amount showing an amount of selling price. Columns G
through L represent whether a product is sold at a 10,15,20,25,35 or a 42
%
margin.
I have a "Main Quote page 1" that has in column E18 through E58 drop down
boxes that allow me to lookup the list on the "products" page. I want to
create a drop down box on the "Main Quote page 1" in Column N a drop down
list that will give me all of the choices of the percentage amounts from
the
products page.

ex: Products page

A C G H I J
K L
product Cost 10% 15% 20% 25% 35% 42%
15-0-15 12.95 14.39 15.23 16.19 17.27 19.92
21.58
16-4-8 8.75 9.72 10.29 10.94 11.67 13.46
14.68
28-0-12 12.25 13.89 14.71 15.62 16.67 19.23
20.83

Any suggestions? Thanks ahead of time.

Pete,

First, sorry for the typo. "Source" table is where you have the prices
and you want to take a single row to feed into DV in main quotes page
1. I assume that the source sheet name is Products. Target is where we
will put our formulas, namely Main Quotes page 1. The intended formula
is

=INDIRECT("Products!G"&MATCH($E18,Products!$A:$A,
0)&":L"&MATCH($E18,Products!$A:$A,0))

Go to InsertNamesDefine... and clear all names by successively
selecting them and clicking on Delete.

The Name VarList is designed to be effective when you are in N18 of
the target sheet, looking at E18 to get a value list for DV.

So, first go to Main Quotes page 1, select N18 and
InsertNameDefine... Name: VarList Refers To: the above formula.

Then, again with N18 selected go to Data Validation and select List,
formula =VarList.

Does this work for you?
Kostis

On Nov 24, 8:18 pm, Pete Elbert
wrote:
L849 as the Kostis,
When you said to go to the target sheet, N18, I assume you meant the "main
quotes page1" page? or were you refering to the "products" page?
None-the-less, I put the formula you gave me into the "main quotes page1"
page and the formula took. However, toward the end of the formula you have
listed"sheet1!" and I do not have a sheet1. I also went into the "products"
page and defined cells G2:L849 as "VarList". Now, when I go back to the Main
Quote Page1 and go to N18 and
validate this cell as list and =varlist, the error statement comes back to
me as "The list source must be a delimited list or a reference to a single
row or column still. Any help is so very appreciated. Pete

"vezerid" wrote:
So what you want in column N is not the percentages but the actual
prices after markup... If so, assume that you are in row E18 of 'Main
Quote page 1'. In N18 you want to choose one of the prices. Do the
following:

In your target sheet, select N18. : InsertName...Define:
Names in workbook: VarList
Refers To:
=INDIRECT("Products!G"&MATCH($E18,Products!$A:$A,
0)&":L"&MATCH($E18,Sheet1!$A:$A,0))

Now, again in N18, DataValidation... Choose List and enter
=VarList

This can be copied down and to the right.

Does this help?
Kostis Vezerides

On Nov 22, 5:53 pm, Pete Elbert
wrote:
Thanks for your quick response on this. I have been working this as you
said but all I get is the following error message - "The list source must be
a delimited list or a reference to a single row or column. The reference
source is located in cells g2 through L849. Can you assist in any more help
with this? Thanks

"Bob Phillips" wrote:
Just give the cells G1:L1 a defined name (InsertNameDefine...), and create
a Data Validation on the cell with an Allow type of List, and use the name
as the reference.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Pete Elbert" wrote in message
...
I have products listed on "products" page that are in column A2 through
A800.
For each product I have a cost column in Column C. Columns G through L
contain a dollar amount showing an amount of selling price. Columns G
through L represent whether a product is sold at a 10,15,20,25,35 or a 42
%
margin.
I have a "Main Quote page 1" that has in column E18 through E58 drop down
boxes that allow me to lookup the list on the "products" page. I want to
create a drop down box on the "Main Quote page 1" in Column N a drop down
list that will give me all of the choices of the percentage amounts from
the
products page.

ex: Products page

A C G H I J
K L
product Cost 10% 15% 20% 25% 35% 42%
15-0-15 12.95 14.39 15.23 16.19 17.27 19.92
21.58
16-4-8 8.75 9.72 10.29 10.94 11.67 13.46
14.68
28-0-12 12.25 13.89 14.71 15.62 16.67 19.23
20.83

Any suggestions? Thanks ahead of time.