Pulldowns From Pulldowns

kurai · May 23rd, 2002, 03:23 PM

Ok, this is what I have: roughly 20 different items with 3 different prices EACH
Pulldowns for those items and a formula that displays the retail price automatically
the pulldowns are Data Validation pulldowns and the INDIRECT formula method isnt working the way I need it to.
I need a way that will give me the 3 price options for the selected item in a pulldown. I posted this before but I dont know any vb code. if you decide that I can only do this in code, please be detailed. thank you thank you thank you.

the 3 different prices are on columns c, d and e. the Items are in on column A. is there a way to make a range of C:E (the column headers) and have the prices referenced from A? I have been trying everything I know (which is only a small amount) If you would like to email me, please go ahead and I will send you the file I am working on

[ This Message was edited by: kurai on 2002-05-23 15:37 ]

Al Chara · May 23rd, 2002, 03:51 PM

Ok, here is my idea:

Define a range named ITEMS that is the list of 20 items.

Define a range named PRICERNG with the following formula:
=CELL("ADDRESS",OFFSET(ITEMS,1,MATCH(Input!$A$1,ITEMS)))&":"&CELL("ADDRESS",OFFSET(ITEMS,3,MATCH(Input!$A$1,ITEMS)))

Define a range named PRICES with the following formula:
=INDIRECT(PRICERNG)

I have assumed that your data validation cell for the Items is in cell A1 (edit above if cell is different).

Make your Items data validation list in cell A1 equal to the following source:
=ITEMS

Make your prices data validation list in cell B1 equal to the following source:
=PRICES

This should all work if the above format is followed. Post with further questions. I will answer tomorrow or someone else on the board can answer. I assume the formula for PRICERNG can be simplified or changed, but that is what first popped into my head.

kurai · May 23rd, 2002, 04:22 PM

there are actually about 8 different pulldowns. this is a very basic price quotation system. I looked at it and thought "YES!!! IT will work!" but I looked closer and it didnt. I would have to make one for each of the pulldowns, and that would be a bit time consuming. not that all this posting isnt taking up lots of time. email me and I can send you the file I am talking about. my address is kurai@qatarmail.com

Ian Mac · May 23rd, 2002, 04:25 PM

Quote:

On 2002-05-23 15:23, kurai wrote:
Ok, this is what I have: roughly 20 different items with 3 different prices EACH
Pulldowns for those items and a formula that displays the retail price automatically
the pulldowns are Data Validation pulldowns and the INDIRECT formula method isnt working the way I need it to.
I need a way that will give me the 3 price options for the selected item in a pulldown. I posted this before but I dont know any vb code. if you decide that I can only do this in code, please be detailed. thank you thank you thank you.

the 3 different prices are on columns c, d and e. the Items are in on column A. is there a way to make a range of C:E (the column headers) and have the prices referenced from A? I have been trying everything I know (which is only a small amount) If you would like to email me, please go ahead and I will send you the file I am working on

[ This Message was edited by: kurai on 2002-05-23 15:37 ]

If you can send me the workbook or an axample I'll take a look.

Chris Davison · May 25th, 2002, 12:08 PM

I see you started another thread for this problem

Jack in the UK · May 25th, 2002, 02:55 PM

Chris mate if you see the new thread kill it refer it back to this one, I have not linked this up, if you can do before I can.

Cheers mate

Gets silly otherwise, two knocking about, always remember the guys might not know or realize..

Just a point, take care chat soon.

Al Chara · May 25th, 2002, 03:59 PM

This thread can be closed, the solution was just to create another named range dynamically with a selection change event procedure that would refer to the selected item. If anyone is interested in all the named range formulas and the little bit of code, I can post it.

May 23rd, 2002, 03:51 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Ok, here is my idea: Define a range named ITEMS that is the list of 20 items. Define a range named PRICERNG with the following formula: =CELL("ADDRESS",OFFSET(ITEMS,1,MATCH(Input!$A$1,ITEMS)))&":"&CELL("ADDRESS",OFFSET(ITEMS,3,MATCH(Input!$A$1,ITEMS))) Define a range named PRICES with the following formula: =INDIRECT(PRICERNG) I have assumed that your data validation cell for the Items is in cell A1 (edit above if cell is different). Make your Items data validation list in cell A1 equal to the following source: =ITEMS Make your prices data validation list in cell B1 equal to the following source: =PRICES This should all work if the above format is followed. Post with further questions. I will answer tomorrow or someone else on the board can answer. I assume the formula for PRICERNG can be simplified or changed, but that is what first popped into my head. __________________ Kind regards, Al Chara

May 25th, 2002, 02:55 PM	#6
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,068	Chris mate if you see the new thread kill it refer it back to this one, I have not linked this up, if you can do before I can. Cheers mate Gets silly otherwise, two knocking about, always remember the guys might not know or realize.. Just a point, take care chat soon. __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 23rd, 2002, 03:23 PM	#1
kurai New Member Join Date: May 2002 Posts: 19	Ok, this is what I have: roughly 20 different items with 3 different prices EACH Pulldowns for those items and a formula that displays the retail price automatically the pulldowns are Data Validation pulldowns and the INDIRECT formula method isnt working the way I need it to. I need a way that will give me the 3 price options for the selected item in a pulldown. I posted this before but I dont know any vb code. if you decide that I can only do this in code, please be detailed. thank you thank you thank you. the 3 different prices are on columns c, d and e. the Items are in on column A. is there a way to make a range of C:E (the column headers) and have the prices referenced from A? I have been trying everything I know (which is only a small amount) If you would like to email me, please go ahead and I will send you the file I am working on [ This Message was edited by: kurai on 2002-05-23 15:37 ]

May 23rd, 2002, 04:22 PM	#3
kurai New Member Join Date: May 2002 Posts: 19	there are actually about 8 different pulldowns. this is a very basic price quotation system. I looked at it and thought "YES!!! IT will work!" but I looked closer and it didnt. I would have to make one for each of the pulldowns, and that would be a bit time consuming. not that all this posting isnt taking up lots of time. email me and I can send you the file I am talking about. my address is kurai@qatarmail.com

May 25th, 2002, 12:08 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	I see you started another thread for this problem

May 25th, 2002, 03:59 PM	#7
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	This thread can be closed, the solution was just to create another named range dynamically with a selection change event procedure that would refer to the selected item. If anyone is interested in all the named range formulas and the little bit of code, I can post it.