|
Input cell information
I hope that I can explain this correctly. I have a cell A and cell B.
If cell B is empty I want cell A to be able to have someone type a date into it. If cell B already has a date, I want cell A to show an X in it so they know that cell A is not available to be used. I need to do this without having the formula located in cell A as it gets over written when someone types in the date. Is there anyway to accomplish this? |
First off, keep an open mind..
Problem seems to be you want to use A, unless cell B has a value. Add column C. In column C put a formula using col B if it exists, col A if not. I KNOW you probably really want to do it in 2 columns.. There's probably a way, BUT in thinking of maintaining it, if you can do without code, it's better. Also, try to avoid worksheets that are used for data entry AND reporting. Make the data entry sheet simple, and utilitarian. Then make another sheet that reports the data with all the fancy formatting. "RB Camp" wrote: I hope that I can explain this correctly. I have a cell A and cell B. If cell B is empty I want cell A to be able to have someone type a date into it. If cell B already has a date, I want cell A to show an X in it so they know that cell A is not available to be used. I need to do this without having the formula located in cell A as it gets over written when someone types in the date. Is there anyway to accomplish this? |
okay,
I understand the dilema. First off, if the user will be way too confused with one extra column, I think you probably have too much going on on that worksheet.. however. I think you can give interface feedback to the user, BUT maybe not in the form of an X. In column C, (or A, for that matter) use conditional formatting... If col C = col A, turn the cell (or just the border) a color, say green. If col C = col B, turn it a different color (say Green) If that's still not acceptable and you absolutely have to show the user a big black X, you'll have to write code.. go to the worksheet's code module.. in the change event write code. the parameter passed in [Target] represents what has changed. You'll have to do some if's to make sure they only changed one cell and it's one in question .... if Target.column 0 and target.column < 4 then ' your code here end if This will work, BUT will 'fire' on every change to the sheet, and have to be maintained.. (if you move the input columns, this macro will have to be updated) I'd go with the conditional formatting. Turn col A Bright red if there's no value in B, and white otherwise "RB Camp" wrote: Thank you for your responce. Unfortunately if I add the cell C it would get confusing for the person entering the information as they would have to compare it with cell A and there are many cells on this page already. If there is a way to put the formula in cell C that says if Cell B is not empty then put an X in cell A it would fix my problems. I can not figure out how to this part. "TomHinkle" wrote: First off, keep an open mind.. Problem seems to be you want to use A, unless cell B has a value. Add column C. In column C put a formula using col B if it exists, col A if not. I KNOW you probably really want to do it in 2 columns.. There's probably a way, BUT in thinking of maintaining it, if you can do without code, it's better. Also, try to avoid worksheets that are used for data entry AND reporting. Make the data entry sheet simple, and utilitarian. Then make another sheet that reports the data with all the fancy formatting. "RB Camp" wrote: I hope that I can explain this correctly. I have a cell A and cell B. If cell B is empty I want cell A to be able to have someone type a date into it. If cell B already has a date, I want cell A to show an X in it so they know that cell A is not available to be used. I need to do this without having the formula located in cell A as it gets over written when someone types in the date. Is there anyway to accomplish this? |
Thank you for your responce.
Unfortunately if I add the cell C it would get confusing for the person entering the information as they would have to compare it with cell A and there are many cells on this page already. If there is a way to put the formula in cell C that says if Cell B is not empty then put an X in cell A it would fix my problems. I can not figure out how to this part. "TomHinkle" wrote: First off, keep an open mind.. Problem seems to be you want to use A, unless cell B has a value. Add column C. In column C put a formula using col B if it exists, col A if not. I KNOW you probably really want to do it in 2 columns.. There's probably a way, BUT in thinking of maintaining it, if you can do without code, it's better. Also, try to avoid worksheets that are used for data entry AND reporting. Make the data entry sheet simple, and utilitarian. Then make another sheet that reports the data with all the fancy formatting. "RB Camp" wrote: I hope that I can explain this correctly. I have a cell A and cell B. If cell B is empty I want cell A to be able to have someone type a date into it. If cell B already has a date, I want cell A to show an X in it so they know that cell A is not available to be used. I need to do this without having the formula located in cell A as it gets over written when someone types in the date. Is there anyway to accomplish this? |
| All times are GMT +1. The time now is 03:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com