Drop-down to populate text

[jhowardc]

Hey.. new to this and hope there is a easy answer. I'd love to be able to have a drop-down in an excel tab that based on whatever selection is picked... populates text at a specific spot on another excel tab within the same document. Is this it possible/easy? Or does it necessitate using macros?

Thanks for any help.

 

[jeffreybrown]

Hello & Welcome to the Board,

I suppose it really depends on how many things you would like to accomplish.

If it was as easy as, drop down box equals night, D10 equals moon, then in D10 a formula =IF(A1="night","moon","")

 

[jhowardc]

It could be as easy as that... can you do that across different tabs in the same excel doc or are you just limited to the tab you are in?

 

[jeffreybrown]

No you are not limited to the one tab, the formula can go in any tab in the workbook.

You would just need to update the formula to include the sheet name

=IF(Sheet1!A1="night","moon","")

If the sheet name has a space in it then it would have to be wrapped with 'sheetname'

=IF('Sheet 1'!A1="night","moon","")

 

[jhowardc]

This is really great.. working well... if you could help me with one more thing, what would it look like to do multiple if/thens in the same formula?

=IF(A1="night","moon","") =IF(A1="day","sun","") =IF(A1="clouds","rain","")

 

[jeffreybrown]

Actually you might want to consider using a Lookup instead.

=LOOKUP(A1,{"clouds","day","night"},{"rain","sun","moon"})

You could even turn this into a range to lookup instead of imbedding the lookup values.

If you want to stick with the if formula it would be:

=IF(A1="night","moon",IF(A1="day","sun",IF(A1="clouds","rain","")))

 

[jhowardc]

Actually you might want to consider using a Lookup instead.

=LOOKUP(A1,{"clouds","day","night"},{"rain","sun","moon"})

You could even turn this into a range to lookup instead of imbedding the lookup values.

If you want to stick with the if formula it would be:

=IF(A1="night","moon",IF(A1="day","sun",IF(A1="clouds","rain","")))

Great, thanks for the help, really appreciate it.

 

[jeffreybrown]

You are very welcome...thanks for the follow up

 

[jhowardc]

You are very welcome...thanks for the follow up

Surprised how easy this is and that it actually works..

couple things I'm running into that I can look up online but thought I'd post here as well...

Is there a limit on number of characters? For instance... =LOOKUP(A1,,{"clouds"},{"I'm adding a lot of text here with bullet points and some words with quotes etc."})

... I'm listing a bunch of bullets with a good amount of info. I get an error if I input a large amount, so just messing around and cut the info down and the error goes away... seems a little silly as it's just text but guess that it really is the issue? Secondly, some of the things I'm using has quotes, but that messes with the equation, anyway to negate quotes in some way so that don't impact the equation but allow you to include them in the field that shows up?

 

[jeffreybrown]

In that case you may want go with a LOOKUP in an Array form vice a Vector setup. By doing this you can avoid using all the quotes

=LOOKUP(A1,$H$1:$I$3)

Look at Excel help for Lookup and one very important point.

The values in array must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

The table below is H1:I3

<TABLE style="WIDTH: 257pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=343><COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 203pt; mso-width-source: userset; mso-width-alt: 8672" width=271><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2716786 class=xl63 height=21 width=72>clouds</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 203pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=271>rain</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>day</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>This is the value I want returned for day</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>night</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>moon</TD></TR></TBODY></TABLE>

As far as a cell limitation, Excel help says:

Total number of characters that a cell can contain - 32,767 characters