Including objects based on a condition

[RBeau]

I have a dropdown box that allows the user to select a set of data. In many
instances, the data requires additional information to fully understand. I
would like to link the visibility of this additional data to what is set in
the dropdown box. Normally i would simply set this information to be visible
using conditional formatting. However, the explanation needs to be freeform
... sometimes a table, sometimes verbiage, etc. so that keeping it uniform and
consistent by making data visible in cells is difficult. It would be much
easier to be able to make an object (text object, table object, etc. - which
has this data pre built in what ever format is needed) visible rather than
trying to do this in multiple cells. Is it possible to set such a condition,
i.e, =IF(A1=1, "object1visible",IF(A1=2,"Object2visible"))?

THanks in advance for any suggestions.

[Ron Coderre]

Here's an approach you may be able to use:

Start by experimenting with this example:

STEP_1: On a Sheet2, create your list of dropdown list values
Example:
A1: myItem
A2: myTable
etc
Name those cells rngList

STEP_2: Select cell B1 and name it rngBlank
Hold down the Shift key and select Edit>Copy Picture (select Copy as shown
on screen)
Then, select Cell C2 on Sheet1 and press Edit>Paste.
(You should see a picture of cell B1 from Sheet2)

STEP_3: Create display ranges that relate to the items on the dropdown list.
Example:
Pertaining to MyItem:
D1:F10 might contain a description of an item.
name that range rngMyItem

Pertaining to MyTable
H1:L15 might be a table of information
name that range rngMyTable
etc
Note: the names you create MUST begin with "rng" and end with the exact text
from the dropdown list.

Then turn off the gridlines on Sheet2 by using Tools>Options>View, Uncheck
gridlines

STEP_4: On Sheet1, select cell A2 to contain the data validation.
Set the data validation to allow a list, Source: rngList.

STEP_5: Create the following range name:
Name: rng2View
Refers to: =INDIRECT(IF(ISBLANK(Sheet1!$A$2),"rngBlank","rng"&Sheet1!$A$2)

STEP_6: Select the image on C2. While the image is selected, enter this in
the formula bar: =rng2View
Then press Enter

Now to test what we've created:
While A1 is blank, C2 will display a picture of the rngBlank range.

When you select MyItem from the dropdown, the picture in C2 will
automatically resize and display a picture of the rngMyItem range.

Is that like what you were hoping to do?

***********
Regards,
Ron

XL2002, WinXP-Pro


"RBeau" wrote:

> I have a dropdown box that allows the user to select a set of data. In many
> instances, the data requires additional information to fully understand. I
> would like to link the visibility of this additional data to what is set in
> the dropdown box. Normally i would simply set this information to be visible
> using conditional formatting. However, the explanation needs to be freeform
> .. sometimes a table, sometimes verbiage, etc. so that keeping it uniform and
> consistent by making data visible in cells is difficult. It would be much
> easier to be able to make an object (text object, table object, etc. - which
> has this data pre built in what ever format is needed) visible rather than
> trying to do this in multiple cells. Is it possible to set such a condition,
> i.e, =IF(A1=1, "object1visible",IF(A1=2,"Object2visible"))?
>
> THanks in advance for any suggestions.

[RBeau]

Ron,

Yes and no (actually - due to the length of the list, evaluating the value
in the dropdown rather than trying to evaluate the "name" .. since it
changes based on other perameters) .. (bear with me ...I know this seems a
bit bizarre).

Let me try to explain better: I have one drop down box that contains a list
of names. When a name is selected, it automatically changes the contents in
two drop down boxes below. In the subsequent drop down boxes, there are up to
30 choices. When you choose one of these, it will automatically populate the
choice and some other data pertaining to that choice.

So far no issues .. have used multiple embedded if statements to figure this
out.

However, there is a need to provide additional "advice" based on choices in
the various drop down boxes - which is "freeform", i.e., since it could be
one sentence, a paragraph, a table, or a picture, it is difficult to arrange
easily by cell. So what I am trying to do is build objects (combination of
all of the above), that would appear on the page based on the selection made.
So for instance, assuming drop down box one has a value of 5 and the next has
a value of 10 and the next 11. I would like to evaluate this, and then make
an object appear (could be again a table/paragraph/picture) based on that
number. So, say I had built a table that contained a combination of
picture/data/text, is there a way to use a conditional statement to make that
visible / invisible? Thanks

"Ron Coderre" wrote:

> Here's an approach you may be able to use:
>
> Start by experimenting with this example:
>
> STEP_1: On a Sheet2, create your list of dropdown list values
> Example:
> A1: myItem
> A2: myTable
> etc
> Name those cells rngList
>
> STEP_2: Select cell B1 and name it rngBlank
> Hold down the Shift key and select Edit>Copy Picture (select Copy as shown
> on screen)
> Then, select Cell C2 on Sheet1 and press Edit>Paste.
> (You should see a picture of cell B1 from Sheet2)
>
> STEP_3: Create display ranges that relate to the items on the dropdown list.
> Example:
> Pertaining to MyItem:
> D1:F10 might contain a description of an item.
> name that range rngMyItem
>
> Pertaining to MyTable
> H1:L15 might be a table of information
> name that range rngMyTable
> etc
> Note: the names you create MUST begin with "rng" and end with the exact text
> from the dropdown list.
>
> Then turn off the gridlines on Sheet2 by using Tools>Options>View, Uncheck
> gridlines
>
> STEP_4: On Sheet1, select cell A2 to contain the data validation.
> Set the data validation to allow a list, Source: rngList.
>
> STEP_5: Create the following range name:
> Name: rng2View
> Refers to: =INDIRECT(IF(ISBLANK(Sheet1!$A$2),"rngBlank","rng"&Sheet1!$A$2)
>
> STEP_6: Select the image on C2. While the image is selected, enter this in
> the formula bar: =rng2View
> Then press Enter
>
> Now to test what we've created:
> While A1 is blank, C2 will display a picture of the rngBlank range.
>
> When you select MyItem from the dropdown, the picture in C2 will
> automatically resize and display a picture of the rngMyItem range.
>
> Is that like what you were hoping to do?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "RBeau" wrote:
>
> > I have a dropdown box that allows the user to select a set of data. In many
> > instances, the data requires additional information to fully understand. I
> > would like to link the visibility of this additional data to what is set in
> > the dropdown box. Normally i would simply set this information to be visible
> > using conditional formatting. However, the explanation needs to be freeform
> > .. sometimes a table, sometimes verbiage, etc. so that keeping it uniform and
> > consistent by making data visible in cells is difficult. It would be much
> > easier to be able to make an object (text object, table object, etc. - which
> > has this data pre built in what ever format is needed) visible rather than
> > trying to do this in multiple cells. Is it possible to set such a condition,
> > i.e, =IF(A1=1, "object1visible",IF(A1=2,"Object2visible"))?
> >
> > THanks in advance for any suggestions.