ExcelBanter - Drop-Down List Choice Affects Multiple Cells?

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Drop-Down List Choice Affects Multiple Cells? (https://www.excelbanter.com/excel-worksheet-functions/119895-drop-down-list-choice-affects-multiple-cells.html)

Drop-Down List Choice Affects Multiple Cells?

Okay. So.

I have this worksheet that has a bunch of data validation drop-down lists.
Each list has a certain type of computer part in it, so the first list is a
bunch of cases, the second list is a bunch of motherboards, and so on. Each
list has about three or four different makes, brands, models, etc, of that
part.

To the side, there's a couple columns, Price, S&H, Notes, Link, Number of
Reviews, and % of Reviews that are 5-Star. So, right now as it stands, I'm
using VLOOKUP so that when you select an item in the list, all of the data to
the right changes accordingly, referring to data on a different spreadsheet
called Database.

What I want to do right now is enable a list of preset configurations. So
at the top of the list of parts, there would be a drop-down menu with items
such as "Budget," "Mid," and "High-End," referring to the different computer
builds. I want it so when the user selects from the list, the rest of the
parts change accordingly. So if they clicked Budget, every computer part's
drop-down list would go to the cheapest item as determined by the price.

Really hard to explain with images...which I could make, upload, and link to
if that's okay.

As it stands, the Configuration list is in cell C7. The Case drop-down list
is C11, Motherboard C13, CPU C15, and so on. The price for the currently
selected Case is F11, Motherboard F13, CPU F15.

If there's not a way for Excel to determine which item on the list has the
lowest price, is there a way to make it so that I specify which items are
selected when the user selects Budget from the Configuration list?

Thanks a ton, I know my question is confusing!

Drop-Down List Choice Affects Multiple Cells?

So I've pretty much learned by myself what I need to do.

Now here's the current predicament, which may or may not be too different
than my original problem. I'm having a hard time remembering anything, as it
is three in the morning.

I have a drop-down menu. It's name is BAREBONE_SETUP_CHOICE. Here's the
formula in the data validation list bit whatever:

=OFFSET(barebone_setup_list,0,0,COUNTA(barebone_se tup_list+1),1)

So, what that does, is takes whatever the user selects and goes to
BAREBONE_SETUP_LIST, which is the list that BAREBONE_SETUP_CHOICE refers to,
and looks one to the right of whatever the user selected in the drop-menu.
Okay. If the user selects "Budget" then the reference cell contains this
formula:

=OFFSET(INDEX(Case_Table[Price],MATCH(MIN(Case_Table[Price]),Case_Table[Price],0)),0,-2)

Case_Table is the table of cases that the list CASE_CHOICE refers to. This
thing finds the cheapest case, and puts it in that cell. Neat.

So, just for kicks, the cell next to the drop menu, BAREBONE_SETUP_CHOICE,
contains this:

=IF(ISBLANK(BAREBONE_SETUP_CHOICE),"
",VLOOKUP(BAREBONE_SETUP_CHOICE,barebone_setup_tab le,2,FALSE))

So it looks up whatever the user selected, and puts in that cheapest case I
was talking about.

NOW. What I want is for the list, CASE_CHOICE, which has a list of the
cases, to flip to the cheapest case when BAREBONE_SETUP_CHOICE is set to
"Budget." I don't want a dynamic list or whatever, I've read about that all
night. Not what I'm talking about. The list is already in place, everything
with it is cool, I just want it to flip to the specified case.

If that can't be done, then is there a way I can change the CASE_CHOICE cell
from a list to a normal cell when Budget is selected?

If you've read this far and you haven't had an aneurysm from the sheer
amount of confusing syntax and nonsensical ranting, you're amazing and I
thank you.

"Peo Sjoblom" wrote:

There is an example here

http://www.contextures.com/xlDataVal02.html

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

"Patrick R" <Patrick wrote in message
...
Okay. So.

I have this worksheet that has a bunch of data validation drop-down lists.
Each list has a certain type of computer part in it, so the first list is
a
bunch of cases, the second list is a bunch of motherboards, and so on.
Each
list has about three or four different makes, brands, models, etc, of that
part.

To the side, there's a couple columns, Price, S&H, Notes, Link, Number of
Reviews, and % of Reviews that are 5-Star. So, right now as it stands,
I'm
using VLOOKUP so that when you select an item in the list, all of the data
to
the right changes accordingly, referring to data on a different
spreadsheet
called Database.

What I want to do right now is enable a list of preset configurations. So
at the top of the list of parts, there would be a drop-down menu with
items
such as "Budget," "Mid," and "High-End," referring to the different
computer
builds. I want it so when the user selects from the list, the rest of the
parts change accordingly. So if they clicked Budget, every computer
part's
drop-down list would go to the cheapest item as determined by the price.

Really hard to explain with images...which I could make, upload, and link
to
if that's okay.

As it stands, the Configuration list is in cell C7. The Case drop-down
list
is C11, Motherboard C13, CPU C15, and so on. The price for the currently
selected Case is F11, Motherboard F13, CPU F15.

If there's not a way for Excel to determine which item on the list has the
lowest price, is there a way to make it so that I specify which items are
selected when the user selects Budget from the Configuration list?

Thanks a ton, I know my question is confusing!

Drop-Down List Choice Affects Multiple Cells?

Hi Patrick

Assuming Budget, Mid and High_End are all defined ranges which contain
the pricing details for the different configurations.
I think what you are asking is to be able to get Case_Table(referred to
as CT from here on to save my fingers<g) to use the appropriate table,
dependant upon which choice the use has made in a cell (let's say A1)

Set up a named range CT as a formula,
=IF($A$1="High_End",High_End,IF($A$1="Mid",Mid,Bud get))

CT will default to Budget if anything other than Mid or High_end is
chosen.

Then use
=OFFSET(INDEX(CT[Price],MATCH(MIN(CT[Price]),CT[Price],0)),0,-2)
and the price will be pulled from the appropriate table.

But maybe I have misunderstood your requirement.
--
Regards

Roger Govier

"Patrick R" wrote in message
...
So I've pretty much learned by myself what I need to do.

Now here's the current predicament, which may or may not be too
different
than my original problem. I'm having a hard time remembering
anything, as it
is three in the morning.

I have a drop-down menu. It's name is BAREBONE_SETUP_CHOICE. Here's
the
formula in the data validation list bit whatever:

=OFFSET(barebone_setup_list,0,0,COUNTA(barebone_se tup_list+1),1)

So, what that does, is takes whatever the user selects and goes to
BAREBONE_SETUP_LIST, which is the list that BAREBONE_SETUP_CHOICE
refers to,
and looks one to the right of whatever the user selected in the
drop-menu.
Okay. If the user selects "Budget" then the reference cell contains
this
formula:

=OFFSET(INDEX(Case_Table[Price],MATCH(MIN(Case_Table[Price]),Case_Table[Price],0)),0,-2)

Case_Table is the table of cases that the list CASE_CHOICE refers to.
This
thing finds the cheapest case, and puts it in that cell. Neat.

So, just for kicks, the cell next to the drop menu,
BAREBONE_SETUP_CHOICE,
contains this:

=IF(ISBLANK(BAREBONE_SETUP_CHOICE),"
",VLOOKUP(BAREBONE_SETUP_CHOICE,barebone_setup_tab le,2,FALSE))

So it looks up whatever the user selected, and puts in that cheapest
case I
was talking about.

NOW. What I want is for the list, CASE_CHOICE, which has a list of
the
cases, to flip to the cheapest case when BAREBONE_SETUP_CHOICE is set
to
"Budget." I don't want a dynamic list or whatever, I've read about
that all
night. Not what I'm talking about. The list is already in place,
everything
with it is cool, I just want it to flip to the specified case.

If that can't be done, then is there a way I can change the
CASE_CHOICE cell
from a list to a normal cell when Budget is selected?

If you've read this far and you haven't had an aneurysm from the sheer
amount of confusing syntax and nonsensical ranting, you're amazing and
I
thank you.

"Peo Sjoblom" wrote:

There is an example here

http://www.contextures.com/xlDataVal02.html

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

"Patrick R" <Patrick wrote in message
...
Okay. So.

I have this worksheet that has a bunch of data validation drop-down
lists.
Each list has a certain type of computer part in it, so the first
list is
a
bunch of cases, the second list is a bunch of motherboards, and so
on.
Each
list has about three or four different makes, brands, models, etc,
of that
part.

To the side, there's a couple columns, Price, S&H, Notes, Link,
Number of
Reviews, and % of Reviews that are 5-Star. So, right now as it
stands,
I'm
using VLOOKUP so that when you select an item in the list, all of
the data
to
the right changes accordingly, referring to data on a different
spreadsheet
called Database.

What I want to do right now is enable a list of preset
configurations. So
at the top of the list of parts, there would be a drop-down menu
with
items
such as "Budget," "Mid," and "High-End," referring to the different
computer
builds. I want it so when the user selects from the list, the rest
of the
parts change accordingly. So if they clicked Budget, every
computer
part's
drop-down list would go to the cheapest item as determined by the
price.

Really hard to explain with images...which I could make, upload,
and link
to
if that's okay.

As it stands, the Configuration list is in cell C7. The Case
drop-down
list
is C11, Motherboard C13, CPU C15, and so on. The price for the
currently
selected Case is F11, Motherboard F13, CPU F15.

If there's not a way for Excel to determine which item on the list
has the
lowest price, is there a way to make it so that I specify which
items are
selected when the user selects Budget from the Configuration list?

Thanks a ton, I know my question is confusing!

Drop-Down List Choice Affects Multiple Cells?

Thanks for the advice, but I think I may be asking for something a bit more
complicated...

I found a thread kind of like what I want.
http://www.microsoft.com/office/comm...=en-us&m=1&p=1

That's pretty much it, except instead of cell locations (A1, A2...), I'd
like to use names (in this case BAREBONE_SETUP_CHOICE and CASE_CHOICE), and
I'd like the program to interpret which item in CASE_CHOICE is cheapest, as
determined by the adjacent column (Price) next to the reference column
(Case_List).

Yeeeeeah, does that make sense?

"Roger Govier" wrote:

Hi Patrick

Assuming Budget, Mid and High_End are all defined ranges which contain
the pricing details for the different configurations.
I think what you are asking is to be able to get Case_Table(referred to
as CT from here on to save my fingers<g) to use the appropriate table,
dependant upon which choice the use has made in a cell (let's say A1)

Set up a named range CT as a formula,
=IF($A$1="High_End",High_End,IF($A$1="Mid",Mid,Bud get))

CT will default to Budget if anything other than Mid or High_end is
chosen.

Then use
=OFFSET(INDEX(CT[Price],MATCH(MIN(CT[Price]),CT[Price],0)),0,-2)
and the price will be pulled from the appropriate table.

But maybe I have misunderstood your requirement.
--
Regards

Roger Govier

"Patrick R" wrote in message
...
So I've pretty much learned by myself what I need to do.

Now here's the current predicament, which may or may not be too
different
than my original problem. I'm having a hard time remembering
anything, as it
is three in the morning.

I have a drop-down menu. It's name is BAREBONE_SETUP_CHOICE. Here's
the
formula in the data validation list bit whatever:

=OFFSET(barebone_setup_list,0,0,COUNTA(barebone_se tup_list+1),1)

So, what that does, is takes whatever the user selects and goes to
BAREBONE_SETUP_LIST, which is the list that BAREBONE_SETUP_CHOICE
refers to,
and looks one to the right of whatever the user selected in the
drop-menu.
Okay. If the user selects "Budget" then the reference cell contains
this
formula:

=OFFSET(INDEX(Case_Table[Price],MATCH(MIN(Case_Table[Price]),Case_Table[Price],0)),0,-2)

Case_Table is the table of cases that the list CASE_CHOICE refers to.
This
thing finds the cheapest case, and puts it in that cell. Neat.

So, just for kicks, the cell next to the drop menu,
BAREBONE_SETUP_CHOICE,
contains this:

=IF(ISBLANK(BAREBONE_SETUP_CHOICE),"
",VLOOKUP(BAREBONE_SETUP_CHOICE,barebone_setup_tab le,2,FALSE))

So it looks up whatever the user selected, and puts in that cheapest
case I
was talking about.

NOW. What I want is for the list, CASE_CHOICE, which has a list of
the
cases, to flip to the cheapest case when BAREBONE_SETUP_CHOICE is set
to
"Budget." I don't want a dynamic list or whatever, I've read about
that all
night. Not what I'm talking about. The list is already in place,
everything
with it is cool, I just want it to flip to the specified case.

If that can't be done, then is there a way I can change the
CASE_CHOICE cell
from a list to a normal cell when Budget is selected?

If you've read this far and you haven't had an aneurysm from the sheer
amount of confusing syntax and nonsensical ranting, you're amazing and
I
thank you.

"Peo Sjoblom" wrote:

There is an example here

http://www.contextures.com/xlDataVal02.html

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

"Patrick R" <Patrick wrote in message
...
Okay. So.

I have this worksheet that has a bunch of data validation drop-down
lists.
Each list has a certain type of computer part in it, so the first
list is
a
bunch of cases, the second list is a bunch of motherboards, and so
on.
Each
list has about three or four different makes, brands, models, etc,
of that
part.

To the side, there's a couple columns, Price, S&H, Notes, Link,
Number of
Reviews, and % of Reviews that are 5-Star. So, right now as it
stands,
I'm
using VLOOKUP so that when you select an item in the list, all of
the data
to
the right changes accordingly, referring to data on a different
spreadsheet
called Database.

What I want to do right now is enable a list of preset
configurations. So
at the top of the list of parts, there would be a drop-down menu
with
items
such as "Budget," "Mid," and "High-End," referring to the different
computer
builds. I want it so when the user selects from the list, the rest
of the
parts change accordingly. So if they clicked Budget, every
computer
part's
drop-down list would go to the cheapest item as determined by the
price.

Really hard to explain with images...which I could make, upload,
and link
to
if that's okay.

As it stands, the Configuration list is in cell C7. The Case
drop-down
list
is C11, Motherboard C13, CPU C15, and so on. The price for the
currently
selected Case is F11, Motherboard F13, CPU F15.

If there's not a way for Excel to determine which item on the list
has the
lowest price, is there a way to make it so that I specify which
items are
selected when the user selects Budget from the Configuration list?

Thanks a ton, I know my question is confusing!