todays problem / listbox - max nested if's

keith · Mar 5th, 2002, 08:22 AM

I have a listbox with a source of 15 cells containing item numbers giving me 15 items in the box. When a certain item is selected in the box I need the other info about the item to appear in the cells to the right. So you pick item number "1" from the list box and the "pricing, cost, description, etc.." for item 1 show up in the cells to the right of the listbox. (the data for each item is listed on a seperate worksheet). Problem is I run out of nested if's at 7 so I can only make this happen with 7 of my 15 items!! My whole solution seems cumbersome with long if's in every cell that holds data. The if's determine the value in the "linked cell" of the listbox and show approiate item info based on the value. There has got to be a better way to do this! Please advise.. Thanks..

Aladin Akyurek · Mar 5th, 2002, 08:24 AM

Try tp apply VLOOKUP.

keith · Mar 5th, 2002, 08:46 AM

Can't seem to apply vlookup...

keith · Mar 5th, 2002, 09:51 AM

any way to do this with a macro maybe?

keith · Mar 5th, 2002, 12:14 PM

HELP!

Aladin Akyurek · Mar 5th, 2002, 12:55 PM

Quote:

On 2002-03-05 07:46, keith wrote:
Can't seem to apply vlookup...

Did you create the list box using Toolbars|Forms? If so, what is its Cell link? And, What is its Selection type: Single, ...?

keith · Mar 5th, 2002, 01:06 PM

Cell Link is K1
I think I found a workaround with the CHOOSE function. I created a column (A) called "autonum" and put 1-15 in that column. Then made the source of the listbox 1-15. From there I used CHOOSE(k1, b2,b3,b4,b5,b6,b7 etc.. to b16) for the cell to the right of the listbox. B2-B16 is the "price" colum for the item.. not quite the same effect I wanted. I wanted to have the actual item numbers populate the listbox, but the autonum effect will work I guess.. Thanks for your help though..!

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

Mar 5th, 2002, 08:22 AM	#1
keith Board Regular Join Date: Mar 2002 Posts: 88	I have a listbox with a source of 15 cells containing item numbers giving me 15 items in the box. When a certain item is selected in the box I need the other info about the item to appear in the cells to the right. So you pick item number "1" from the list box and the "pricing, cost, description, etc.." for item 1 show up in the cells to the right of the listbox. (the data for each item is listed on a seperate worksheet). Problem is I run out of nested if's at 7 so I can only make this happen with 7 of my 15 items!! My whole solution seems cumbersome with long if's in every cell that holds data. The if's determine the value in the "linked cell" of the listbox and show approiate item info based on the value. There has got to be a better way to do this! Please advise.. Thanks..

Mar 5th, 2002, 08:24 AM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,319	Try tp apply VLOOKUP.

Mar 5th, 2002, 08:46 AM	#3
keith Board Regular Join Date: Mar 2002 Posts: 88	Can't seem to apply vlookup...

Mar 5th, 2002, 09:51 AM	#4
keith Board Regular Join Date: Mar 2002 Posts: 88	any way to do this with a macro maybe?

Mar 5th, 2002, 12:14 PM	#5
keith Board Regular Join Date: Mar 2002 Posts: 88	HELP!

Mar 5th, 2002, 01:06 PM	#7
keith Board Regular Join Date: Mar 2002 Posts: 88	Cell Link is K1 I think I found a workaround with the CHOOSE function. I created a column (A) called "autonum" and put 1-15 in that column. Then made the source of the listbox 1-15. From there I used CHOOSE(k1, b2,b3,b4,b5,b6,b7 etc.. to b16) for the cell to the right of the listbox. B2-B16 is the "price" colum for the item.. not quite the same effect I wanted. I wanted to have the actual item numbers populate the listbox, but the autonum effect will work I guess.. Thanks for your help though..!