ExcelBanter - Making cell contents dependent on another cell

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

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Making cell contents dependent on another cell (https://www.excelbanter.com/excel-discussion-misc-queries/177569-making-cell-contents-dependent-another-cell.html)

Making cell contents dependent on another cell

I am relatively new to the more technical side of Excel and can't figure out
one particular issue. I have a large number of columns in my spreadsheet, but
some only need to be filled out if a particular value is chosen from a
drop-down menu. For example, if the user has the option of choosing "apple",
"orange", "pear" or "banana" from a list, I want them to then fill out some
more information based on that choice - if they pick "apple" then I may want
to know how red it was, whether it had a leaf on etc. But if they pick
"banana" then I don't need to know that information - I need to know other
things instead.

Is there a way to "grey out" or lock particular cells in Excel so that they
will only be unlocked and available for data entry if a certain value is
entered in a different cell? In other words, the ability to enter data in
certain columns is dependent on a previous choice?

I hope I managed to make sense there... Thanks!

Making cell contents dependent on another cell

This could be much easier if you used access. But if you are using excel you
have two options.

1) Create a massive amount of if statements in a formula
2) Create a select case code in a worksheet event

The first choice does not allow you to block or change a cells appearance
other then the text written in it. The second choice requires VBA knowledge.
Either way, unless you only have 3 options and could use custom formatting
this would be complicated if you do not know what you are doing.

"Jen" wrote:

I am relatively new to the more technical side of Excel and can't figure out
one particular issue. I have a large number of columns in my spreadsheet, but
some only need to be filled out if a particular value is chosen from a
drop-down menu. For example, if the user has the option of choosing "apple",
"orange", "pear" or "banana" from a list, I want them to then fill out some
more information based on that choice - if they pick "apple" then I may want
to know how red it was, whether it had a leaf on etc. But if they pick
"banana" then I don't need to know that information - I need to know other
things instead.

Is there a way to "grey out" or lock particular cells in Excel so that they
will only be unlocked and available for data entry if a certain value is
entered in a different cell? In other words, the ability to enter data in
certain columns is dependent on a previous choice?

I hope I managed to make sense there... Thanks!

Making cell contents dependent on another cell

I would love to use Access, but I am making the Excel sheet for someone
else's project and they don't know how to use Access... So this is meant to
be a "quick fix" for them to be able to get some data entered electronically
in the short term. And my VBA knowledge is nil.

If I was working with all drop-down menus instead of some free type it would
be better...

Can I use the IF command to tell Excel, "IF A1 has a particular option
chosen from the drop down menu, THEN let me type in B1 - otherwise don't let
me type in B1"? Or will the IF... THEN combination only work for what you're
actually typing in the cell, not whether you can enter anything into it or
not?

*Sigh* Thanks for the help.

"akphidelt" wrote:

This could be much easier if you used access. But if you are using excel you
have two options.

1) Create a massive amount of if statements in a formula
2) Create a select case code in a worksheet event

The first choice does not allow you to block or change a cells appearance
other then the text written in it. The second choice requires VBA knowledge.
Either way, unless you only have 3 options and could use custom formatting
this would be complicated if you do not know what you are doing.

"Jen" wrote:

I am relatively new to the more technical side of Excel and can't figure out
one particular issue. I have a large number of columns in my spreadsheet, but
some only need to be filled out if a particular value is chosen from a
drop-down menu. For example, if the user has the option of choosing "apple",
"orange", "pear" or "banana" from a list, I want them to then fill out some
more information based on that choice - if they pick "apple" then I may want
to know how red it was, whether it had a leaf on etc. But if they pick
"banana" then I don't need to know that information - I need to know other
things instead.

Is there a way to "grey out" or lock particular cells in Excel so that they
will only be unlocked and available for data entry if a certain value is
entered in a different cell? In other words, the ability to enter data in
certain columns is dependent on a previous choice?

I hope I managed to make sense there... Thanks!

Making cell contents dependent on another cell

If it's a quick fix there is a small work around for it... you can use
VLookups that was already suggested.

So create a list of your possible options, next to it write down what you
want the next cell to be... for example

Apple---What Color?---How Big?
Banana---How Big?---Nothing
Pear---Nothing

Then you can use conditional formatting to make anything that says nothing
to turn black. There is no way to make a cell protected based off a formula
without VBA.

So it would look like

---A--------B-------------------------------------C
Apple----=Vlookup(A1,YourVlookupRange,2,False)--If(B1="Nothing","Nothing",
Vlookup(B1,YourVlookupRange,3,False)

Hopefully you get the idea. This would be your best bet, so any field you
want to not be filled will be black or whatever conditional format you choose.

"Jen" wrote:

I would love to use Access, but I am making the Excel sheet for someone
else's project and they don't know how to use Access... So this is meant to
be a "quick fix" for them to be able to get some data entered electronically
in the short term. And my VBA knowledge is nil.

If I was working with all drop-down menus instead of some free type it would
be better...

Can I use the IF command to tell Excel, "IF A1 has a particular option
chosen from the drop down menu, THEN let me type in B1 - otherwise don't let
me type in B1"? Or will the IF... THEN combination only work for what you're
actually typing in the cell, not whether you can enter anything into it or
not?

*Sigh* Thanks for the help.

"akphidelt" wrote:

This could be much easier if you used access. But if you are using excel you
have two options.

1) Create a massive amount of if statements in a formula
2) Create a select case code in a worksheet event

The first choice does not allow you to block or change a cells appearance
other then the text written in it. The second choice requires VBA knowledge.
Either way, unless you only have 3 options and could use custom formatting
this would be complicated if you do not know what you are doing.

"Jen" wrote:

I am relatively new to the more technical side of Excel and can't figure out
one particular issue. I have a large number of columns in my spreadsheet, but
some only need to be filled out if a particular value is chosen from a
drop-down menu. For example, if the user has the option of choosing "apple",
"orange", "pear" or "banana" from a list, I want them to then fill out some
more information based on that choice - if they pick "apple" then I may want
to know how red it was, whether it had a leaf on etc. But if they pick
"banana" then I don't need to know that information - I need to know other
things instead.

Is there a way to "grey out" or lock particular cells in Excel so that they
will only be unlocked and available for data entry if a certain value is
entered in a different cell? In other words, the ability to enter data in
certain columns is dependent on a previous choice?

I hope I managed to make sense there... Thanks!