Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cell contents dependent on another cell
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation list dependent on contents of another cell | Excel Discussion (Misc queries) | |||
Jumping reference cell in dependent cell formula | Excel Worksheet Functions | |||
Locking Cell dependent on Another Cell Value | Excel Discussion (Misc queries) | |||
Need to lookup value in cell, dependent on value in another cell | Excel Worksheet Functions | |||
Cell locking dependent on Cell value | Excel Discussion (Misc queries) |