Here is my scenario:
The merged Cell B6:G6 will receive a ten-digit number followed by a dash and then one or more numbers. (For example: 1234567890-123)
Cell B15 will then receive data shortly afterwards. I already have a validation macro for this cell which allows either 'I' or 'I I I'.
Upon exiting Cell B15, merged Cell B16:H16 needs a macro which will check Cell B15 and if it contains 'I', Cell B16:H16 will display the data from the ten-digit number entered in Cell B6:G6 minus the first five digits. (For example: 67890-123)
Now the data in Cell B16:H16 can only be somewhat editable hereafter. It can be erased or replaced with numbers in smaller or greater digit combinations than five before the dash (i.e. 67890-123 can be replaced with 123456-7), and digits can be added after the whole group (i.e. 67890-123 & SEE DWG) without any error messages. But if any five-digit number with a dash and some numbers exist in Cell B16:H16, they must correspond with the number in Cell B6:G6 minus the first five digits.
However, if Cell B15 ever receives a 'I I I' afterwards, all data in Cell B16:H16 must be erased. Cell B16:H16 can never contain data if Cell B15 contains 'I I I'.
Also, if the data in Cell B6:G6 changes later on, the corresponding digits in Cell B16:H16 must change as well, even if there are digits after the whole group.
So here is an example of what a good macro would do for me:
1. I enter the number 1234567890-123 into Cell B6:G6.
2. I go to Cell B15 and enter the letter I. I then hit the Enter key.
3. Cell B16:H16 now displays 67890-123.
4. I change the contents of Cell B16:H16 to read 123456-7. It lets me do this with no objections.
5. I change the contents of Cell B16:H16 to read 1234-56. It lets me do this with no objections.
6. I change the contents of Cell B16:H16 to read 12345-6. Upon hitting Enter, I receive a message stating that this number is not allowed as it is not the same as the number in Cell B6:G6 minus the first five digits.
7. I change the contents of Cell B16:H16 to read 67890-123 & SEE DWG. It lets me do this with no objections.
8. I change the number in Cell B6:G6 to read 5000567891-2, and upon hitting Enter, Cell B16:H16 displays 67891-2 & SEE DWG.
9. I put I I I into Cell B15 and hit Enter. Cell B16:H16 is now completely empty.
I really hope that someone out there can understand what I am trying to achieve. Thanks in advance for any feedback I might get. -Juda
Bookmarks