Automating entry using validation
I have two columns e.g. - fruit, code
apples, 01 oranges 02 lemons 03 On my form the cell for apples is a validation list cell and I would like the cell for the code to appear in another cell automatically. I like validation rather than a drop-down combo box because the "box" isn't seen until the cursor is in that cell and doesn't print. Can both cells be validation with the proper function in the appropriate cell? |
Automating entry using validation
Dianna,
You could put a formula into the code cell. Either an If..then..else or a Vlookup (if there are a lot of "fruit") In both cases use a multi-level If statement. For example - if the fruit is in A1 and the code is in B1 and your table is in D1:E5 =If(A1, Vlookup(A1,D1:E5,2),"") The "" will leave B1 blank if no choice is made. Beware that no match will result in #NA. You can trap for this in your formula, but since you are using a Validation list, it shouldn't be an issue. -- sb "Dianna" wrote in message ... I have two columns e.g. - fruit, code apples, 01 oranges 02 lemons 03 On my form the cell for apples is a validation list cell and I would like the cell for the code to appear in another cell automatically. I like validation rather than a drop-down combo box because the "box" isn't seen until the cursor is in that cell and doesn't print. Can both cells be validation with the proper function in the appropriate cell? |
Automating entry using validation
Thanks Steve, I will give it a try when I get a chance...
-----Original Message----- Dianna, You could put a formula into the code cell. Either an If..then..else or a Vlookup (if there are a lot of "fruit") In both cases use a multi-level If statement. For example - if the fruit is in A1 and the code is in B1 and your table is in D1:E5 =If(A1, Vlookup(A1,D1:E5,2),"") The "" will leave B1 blank if no choice is made. Beware that no match will result in #NA. You can trap for this in your formula, but since you are using a Validation list, it shouldn't be an issue. -- sb "Dianna" wrote in message ... I have two columns e.g. - fruit, code apples, 01 oranges 02 lemons 03 On my form the cell for apples is a validation list cell and I would like the cell for the code to appear in another cell automatically. I like validation rather than a drop-down combo box because the "box" isn't seen until the cursor is in that cell and doesn't print. Can both cells be validation with the proper function in the appropriate cell? . |
All times are GMT +1. The time now is 04:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com