Data Validation Problem With Formula

[lasc]

My intention is to have a cell that allows a telephone number to be typed into it, but also contains a formula that transfers a phone number from another cell when a check box is selected. I have set this up and it works correctly. You either transfer the number, or manually enter the number which overwrites the formula. No problem there.

I have also used the telephone number formatting in that cell along with data validation that requires a complete 10 digit number. This formatting also works correctly. However, the formula does not meet the telephone number or 10 digit data validation criteria, so it cannot be entered into the same cell. How do I work around this problem? Any suggestion would be greatly appreciated.

[janmorris]

the best way to start is to provide a sample workbook so we can see what you are talking about

just follow the instructions in the yellow box at the top.

[lasc]

I think I attached a file. I uploaded it in "manage attachments".

[lasc]

In the sample workbook, cells C14 through C17 are data entry fields for Customer info. Cells F14 through F17 are data entry fields for Delivery Location info. Since 95% of the time this info is the same, I put a drop down list in F11 that answers the question "Is the info the same?" If the answer is Yes, formulas in cells F14 through F16 copy the data from cells C14 through C16 to save time and work. If the answer is No, the user simply types the different info in cells F14 through F16, which over writes the formulas. That all works fine. The problem arises when trying to put the formula in cell F17 because it has the telephone number format from "Format Cells" and a 10 digit restriction in "Data Validation". Because of this formatting, excel will not allow the formula to be entered into cell F17.

[lasc]

Does anybody have an idea that I might try?

[JeteMc]

I don't feel that this is going to be solvable using formulas, but it might be using VBA.
My thought would be that the range A17:J17 would be duplicated in A19:J19, such that F17:H17 have the data validation rule applied and F19:H19 contain formulas.
When the spreadsheet is opened A19:J19 are hidden.
If Yes is selected in F11 then A19:J19 stay hidden, however if No is selected then A17:J17 become hidden and A19:J19 are unhidden.
I don't know enough about VBA to write the code, but if you think this is a possibility then you might ask a moderator to move the thread to the VBA forum and let those contributors take a look.
I hope that you have a blessed day.

[lasc]

Thank you very much for your response. I'm going to look at my spreadsheet with your suggestions and see if I can make it work.