+ Reply to Thread
Results 1 to 19 of 19

Linking Partial Data From One Cell If Data In Another Cell Meets Requirements

  1. #1
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Linking Partial Data From One Cell If Data In Another Cell Meets Requirements

    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
    Last edited by swordswinger710; 01-13-2009 at 02:59 PM.

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Hi Juda.

    Is this going to need to work in conjunction with your other thread? Will this be on the same worksheet?

    Jason

  3. #3
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845
    Hello again, and yes, this is all part of the same worksheet.

  4. #4
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Hi Juda. The only part I was confused was in step 8. What is the logic of putting " & SEE DWG" at the end of the cell? Will this suffix always need to be added to the end of cell B16 when B6 changes?

    Here is what I have so far:

    Please Login or Register  to view this content.
    Jason

  5. #5
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845
    Good morning, and wow, that's a great start!

    First of all, to answer your question about Step 8:

    Cell B6:G6 reads 1234567890-123.
    Therefore, Cell B16:H16 reads 67890-123 & SEE DWG. The only reason the & SEE DWG extension is displayed is because that particular extension was added in Step 7. Had no extension been added in Step 7, there would be none in Step 8. Had a different extension been added in Step 7, such as & L1, then in Step 8 Cell B16:H16 would have read 67890-123 & L1.

    Something else I should clarify (I used Bold to show the important digit groups) - let's say Cell B6:G6 reads 1234567890-123, and Cell B16:H16 reads 67890-123 & L1. If I make any changes to the number after the dash, for example to 67890-4 & L1, I should get the same error message I get when I try to change the numbers in front of the dash. The whole group of numbers - the five digits with the dash and any digits after the dash - must correspond to the digits in Cell B6:G6 minus the first five digits.

    I also noticed that your macro seems to be looking at Cell B6:G6, subtracting the first five digits, and placing the rest into Cell B16:H16 if conditions apply. While that is cool as long as the number before the dash in Cell B6:G6 contains ten digits, I think that perhaps we should have the macro looking at the five digits preceding the dash (as well as the dash and the digits following it), just to avoid any conflicts with the value displayed in Cell B16:H16. It would also be cool if a message box would appear when more or less than ten digits before the dash were entered by mistake.

    Everything else in your macro seems to work swimmingly well. I hope I am clear enough - let me know if you have any further questions. And thanks so much - Juda

  6. #6
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Ok, Juda. Here is the revised code. This should also fix the other bug you just posted in the other related thread (http://www.excelforum.com/excel-prog...cell-data.html). Let me know if you find any other issues with it.

    Jason

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845
    Bless you man, you have no idea how much I appreciate all the time you've spent on me. That was truly amazing!

    There are three more issues I should mention:

    1.Cell B6:G6 should return to the value it contained after the message of an incorrect amount of digits appears. Currently, it just gives me the warning and then allows the mistake to be displayed.
    2. If Cell B6:G6 is erased, Cell B16:G16 should be too.
    3. My macro for Cell B15 allows the letter I with spaces. If Cell B15 has anything but one I, Cell B16:G16 should be empty. (Forgive me if I wasn't clearer on this earlier.)

    I think that should just about do it then. And thanks one more time! -Juda

  8. #8
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Juda, Juda, Juda... Specifics really help when stating your requests. Revision 145137682823:

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845
    Hi again, sorry for all the trouble - I feel rather bad now (but I'm glad you're still smiling).

    I dug right into all the options this time, and I am sad to say I have yet another request for the letter I in Cell B15. The macro should look at Cell B15, and count the number of I's therein. If there is only one I, then, regardless of any other digit included in Cell B15, the Cell B16:G16 will show it's data. This is necessary due to the fact that sometimes the one I in Cell B15 will have extensions as well, which should not prevent the data in Cell B16:G16 from showing. Any more than one I should then erase Cell B16:G16.

    Also, when I delete the contents of Cell B6:G6, whether Cell B16:G16 contains data or not, I get this error message: Run-time error '1004': Cannot change part of a merged cell. Not sure why.

    Is it possible to leave the incorrect number highlighted in Cell B6:G6 after displaying the message about ten digits, instead of erasing it? The reason I'm asking is because I am using some very nice validation macros that ask me if I want to Retry or Cancel, and if I say Cancel, then it functions like your error message; however, if I hit Retry, I remain at the cell with the contents highlighted, can just do a quick fix, and I'm all set.

    That, to the best of my knowledge, is the last of my problems (I really hope). Let me know what you think, and of course I am eternally indebted to you. -Juda

  10. #10
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Quote Originally Posted by Juda View Post
    Is it possible to leave the incorrect number highlighted in Cell B6:G6 after displaying the message about ten digits, instead of erasing it? The reason I'm asking is because I am using some very nice validation macros that ask me if I want to Retry or Cancel, and if I say Cancel, then it functions like your error message; however, if I hit Retry, I remain at the cell with the contents highlighted, can just do a quick fix, and I'm all set.
    Hi Juda. I am just giving you a hard time. Well, sort of...

    I have the other issues resolved, except for the above. However, here is what I would like to suggest.

    I will remove the validation for this cell from the procedure, and you can use data validation for this merged cell. To do this, you will select Custom from the drop-down box, then in the Formula box, use:

    =ISNUMBER(INT(LEFT(B6,10)))

    This will ensure at least a 10-digit number is entered. If you also want a dash entered, you can use:

    =AND(ISNUMBER(INT(LEFT(B6,10))),MID(B6,11,1)="-")

    Let me know if this is an option for you.

  11. #11
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    If the above option works, here is the revised code:

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845
    This is really, really great! I couldn't find any more bugs or anything... Just one more question before I put this into production - I noticed the following line of code is still in the macro:

    Please Login or Register  to view this content.
    When will this appear, since I am now using the Validation code to ensure the correct amount of digits? No big deal, I'm just curious.

    Again, thanks a million for all of your effort. If I was your boss I'd give you a raise. Keep up the great work! -Juda

  13. #13
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Hi Juda.

    Glad to hear it is working.

    I actually never removed the code to check for the 10-digit number. Since you are now checking it using data validation, that code will not come into play at all. To remove this (if you wish), you can delete all of the below code in red font (don't forget the last 'Else' in red).

    Jason

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845
    Hello again, I just found a bug in our macro - I'm not sure what exactly causes it, but when I paste information to Cell B15 and others at the same time, I get this error message - Run-time error '13': Type mismatch. Do you have any suggestions as to a solution?

    I also ran across another situation where I noticed we could add some more functionality to our beloved Cell B16:G16. When a six-digit number is entered, replacing the automatic five-digit one, it must match the entire number in merged Cell B9:G9 (with basically the same rules as our five-digit scenario). Do you think this could still be integrated? -Juda

  15. #15
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Hi Juda.

    I believe the code below will fix the first bug. As for your other request, I have incorporated the first part (if the number before the dash in B16 is changed to a 6-digit number, it must be the the same as what is in B9). Please be more specific with the other request ("basically the same rules as our five-digit scenario").

    If you encounter other issues, please also state the line that is highlighted when you select Debug.

    Please Login or Register  to view this content.

  16. #16
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845
    Deleted due to double-posting. My Internet is experiencing technical difficulties.
    Last edited by swordswinger710; 01-14-2009 at 12:51 PM.

  17. #17
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845
    OK, thanks so much, your macro fixed the error message.

    Before I can explain the Cell B9:G9 thing, I need to clarify something else:

    I am in a bit of a situation. Yesterday, I ran into an issue with Cell B15. Simply put, I discovered I was wrong about my 'I' theory. I told you that if Cell B15 has anything but one I, Cell B16:G16 should be empty. Apparently, that's not quite true. This is what I've discovered. I'll do my best to explain.

    These are the 'I' combination possibilities for Cell B15 (Notice the spaces between the digits):
    I
    I I
    I I I
    IV
    IA
    I IA
    I I IA
    IVA

    I think I need a validation formula for Cell B15 to keep us problem free. It would have to ensure the spacing thing - no space between an 'I' and an 'A', no space between a 'V' and an 'A', no space between an 'I' and a 'V'; spaces between 'I''s.

    Any two (possibly more?) of the groups above can be added with an '&' and displayed in Cell B15. Here's some examples:
    IA & I I I (This is the one that surprised me.)
    I I & I I IA
    I VA & I IA

    Now for the interpretation. One 'I' with or without the 'A' means Cell B16:G16 must display something after hitting Enter. It doesn't matter if there are some more 'I''s somewhere in Cell B15, such as the example that surprised me. As long as there is an 'I', or an 'IA', Cell B16:G16 should not be empty. If there is/are any of the other combination(s) entered without the 'I' or the 'IA', then Cell B16:G16 must be empty, and not allow any data to be entered in it.

    The default value displayed for the 'I' is the ending of the ten-digit number with the dash and the numbers after it (we already have this working well). The 'A' means that a '-XXX' extension must be added to the end of the value in Cell B16:G16. So, this means if we have the number 1234567890-123 in Cell B6:G6, and we enter 'IA' anywhere into Cell B15, then the default value of Cell B16:G16 would be 67890-123-XXX.

    If Cell B16:G16 is changed from five digits to six digits, then those six digits with the dash and the numbers after the dash must correspond to the exact value in Cell B9:G9. If the digits in Cell B9:G9 are changed, and Cell B16:G16 is displaying the value from it, then they must change in Cell B16:G16 as well.

    So here is what I should be able to do (I am starting from a blank sheet):

    1. I enter the number 1234567890-123 into Cell B6:G6 and hit Enter.
    2. I go to Cell B9:G9 and enter the number 123456-7 and hit Enter. (I should get a validation error message if more or less then a six-digit number before the dash is entered.)
    3. I go to Cell B15 and enter 'IA & I I I'. I then hit the Enter key.
    4. Cell B16:H16 now displays 67890-123-XXX.
    5. I change the contents of Cell B16:H16 to read 123456-7-XXX & SEE DWG. It lets me do this with no objections.
    6. I change the value of Cell B9:G9 to read 654321-001 and hit Enter.
    7. Cell B16:H16 now displays 654321-001-XXX & SEE DWG.
    8. I select Cell B16:H16 and hit Delete. I should still be able to do this. The -XXX's should be editable, just to allow for any other surprises down the road.
    9. I change Cell B15 to read 'I & IV' and Cell B16:H16 now displays 67890-123.

    My goodness. That took me about two hours to write. I hope you can understand this - please let me know if you still aren't clear on something. -Juda

    PS - I've attached my worksheet so you can have a better look at what I'm doing.
    Last edited by swordswinger710; 01-27-2009 at 04:49 PM.

  18. #18
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Sorry for the delay, Juda, but that last post was a little overwhelming. I think I have worked out the bugs, but I'm sure more will be discovered once you look at it. LMK

    Jason
    Attached Files Attached Files

  19. #19
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845
    Man, you've really been working hard! Here are the issues you prophesied I'd find

    1. I'll need your macros to be integrated into my workbook - I didn't realize we'd need more than one page of code! I attached it below.
    2. Cell B16:H16 doesn't erase when I erase Cell B15.
    3. When I change the five or six digit number before the dash and/or their numbers after the dash in Cell B16:H16 and they don't correspond with their respective cells (B9:G9 or B6:G6), I don't get an error message.
    4. When I change the last five digits before the dash or it's number(s) after the dash in Cell B6:G6, and I have a six-digit number in Cell B16:H16, the six digit number changes back to the five-digit number instead of remaining the same number as Cell B9:G9.
    5. If I don't enter at least a one-digit number after the dash in Cell B6:G6 or Cell B9:G9, I don't get an error message.
    6. I can erase Cell B16:G16 with an 'I' or an 'IA' in Cell B15 and get away with it. This is one I messed up on. I can't for the life of me figure out why I told you that Cell B16:H16 can be totally erased. (I proof-read it like five times, too.) As long as there is an 'I' or an 'IA' in Cell B15, Cell B16:G16 must not be empty.
    7. I'd really like to use validation on Cell B15 for the spacing issue so I can just edit the wrong entry, but I'm not sure if that's possible.

    That's all I found for now. I really hope I'm not asking too much. The folks in my workplace are all quite happy with the way this workbook is advancing, thanks to all your hard work! Give me a holler if you don't understand something, and thanks for not giving up on me. -Juda
    Last edited by swordswinger710; 01-27-2009 at 04:49 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1