+ Reply to Thread
Results 1 to 35 of 35

Cell to contain Yes/No box dependent upon other cell value?

  1. #1
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Cell to contain Yes/No box dependent upon other cell value?

    I have a button on Sheet1 which filters Sheet2 depending upon various week commencing dates.
    One of which is filter to last week... These dates are housed in Column A, Sheet2
    Column S of Sheet2 is a Yes/No question that only applies to last weeks data.

    Is it possible to only have the Yes/No appear if Column A = last week?

    I don't want the functionality of a Yes/No to appear on rows of current or future weeks data.

    If any more info is required, please ask - thanks.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Column S has a validation that looks at range Z2:Z3 (AKA Ans_1) if the date in column A is within last week or at range AA1 (Blank Cell - AKA ANS_2) if not. Start of last week (Sunday) is in Cell AD2 and end of last week (Saturday) is in cell AD3. The validation is:
    =IF(AND(A2>=$AD$2,A2<=$AD$3),Ans_1,ANS_2)
    Attached Files Attached Files
    Last edited by dflak; 03-10-2017 at 02:38 PM.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Great thanks, I think this will work perfectly.

    Is it possible to modify so that the first day of last week is a Monday rather than a Sunday, and the last of last week is a Sunday?

    Could you also explain to me how AD1 is being used? This is a test date but it is used in the AD2 & AD3 formula and I'd like to understand why for my own understanding

    Many thanks

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I changed the formulas in AD2 and AD3 so they start the week on Monday and end it on Sunday. Cell AD1 was a test date so I could test the program. In this version, it is replaced with =TODAY(). So now the sheet is "live."
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Perfect - thanks for this.

    I may be asking too much but would it be possible for this functionality to be extended...?
    For example, if No is selected from the dropdown, a message box appears saying "Would you like to reschedule?"... If No is clicked nothing happens but if Yes is clicked, specific cells from the row are copied and pasted into the next available row where column A is blank.

    So an example would be: A2 has a date of 03/06/2017 i.e. last week. Therefore the user is able to select Yes/No from S2. No is selected. A message box appears asking if they wish to reschedule. They click Yes. Cells B:I and M:R of Row 2 are then copied and pasted into Row 26 (as Rows 3-25 all have data in Column A).

    Hope this makes sense, would be great if this was possible!

    Thanks

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    It is possible to do this, but it will take some VB code. It's not difficult code at all, it would take a couple of minutes.

    I'll need a bit more on the definition. What's so special about row 26? I suggest you attach a workbook with sample, non-sensitive data so I can see how the data is laid out. Another advantage of this would be that when I return the workbook to you, you can remove my data and paste in your real data.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

  7. #7
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Row 26 just happens to be the next free row as of today.
    Next week more demand could be added and the first free row could be row 35, for example.

    I have attached the file with sensitive info removed. I've removed a fair bit of info so I may have to paste your code into my master file rather than the other way around, unfortunately.

    Many thanks
    Attached Files Attached Files

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    OK, I got it. Seeing the workbook helps make more sense of the requirement. I'll explain how to install the code when I post.

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I see that you already have a lot of code in the workbook and on the sheets. I see a SelectionChange event that sets Cell F1 to the row value whenever something in the first 500 rows (minus row 1) is selected. This event is also going to be fired when you select the row for the dropdown box. It should not interfere with the code, but I was wondering what the purpose of this code is. I don't see an immediate use for it and was wondering if there might be a better way to accomplish this task as well. For one thing, you could probably make the range dynamic rather than fixing it at row 500.

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    It's difficult to work with this workbook. It has a time bomb that closes it after several minutes. Also, although I can see the VBA project, I don't seem to be able to edit it. I'm sorry, I can't do anything further until these two things are rectified.

  11. #11
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Quote Originally Posted by dflak View Post
    I see that you already have a lot of code in the workbook and on the sheets. I see a SelectionChange event that sets Cell F1 to the row value whenever something in the first 500 rows (minus row 1) is selected. This event is also going to be fired when you select the row for the dropdown box. It should not interfere with the code, but I was wondering what the purpose of this code is. I don't see an immediate use for it and was wondering if there might be a better way to accomplish this task as well. For one thing, you could probably make the range dynamic rather than fixing it at row 500.
    The purpose of this code is so that the user can select a row on the Raw Data tab, move to the capacity calendar tab, (F1 states the row that has been selected) double click a free slot in green which then pastes the date and contact back to the raw data tab... make sense? Please try and demo this for yourself as it may help your understanding as to why I have it in there.

    It was stopped at 500 as there shouldn't ever be that number of rows but if it could be made dynamic easily that would be great.

    Quote Originally Posted by dflak View Post
    It's difficult to work with this workbook. It has a time bomb that closes it after several minutes. Also, although I can see the VBA project, I don't seem to be able to edit it. I'm sorry, I can't do anything further until these two things are rectified.
    Apologies, I forgot to remove the autoclose. I have re-attached with this removed, hopefully this will allow you to edit the VBA now also? Please let me know if still facing further issues.
    Attached Files Attached Files

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I thought I had replied to this one. Here it is. I assumed that by "next free row" you mean the row after the last row for a date and not any other blank row that might occur above that.

    Be advised that by copying as you indicated, leaves the date column still blank. So this row remains the next free row and unless you fill it in, data previously copied will be overwritten.

    P.S. I had to add a EnableEvents = False and EnableEvents = True to your SelectChange event to keep events from cascading.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Many thanks, this works great, apart from the slight issue I am having below:

    I assumed that by "next free row" you mean the row after the last row for a date and not any other blank row that might occur above that.
    Correct. However, I am facing an issue whereby if I apply a filter such as the "Confirm visits that went ahead last week" on the homepage (as the user would), when then clicking No and re-scheduling the meeting, the data seems to paste in the row below the filter rather than the next available row?

    Please try in the attached and you will see what I mean, thank you.
    Attached Files Attached Files
    Last edited by JRC1; 03-16-2017 at 07:30 AM.

  14. #14
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Also, selecting "Yes" from the dropdown seems to paste the info into rows also? Whereas it should do nothing if Yes is selected?

  15. #15
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Quote Originally Posted by JRC1 View Post
    Also, selecting "Yes" from the dropdown seems to paste the info into rows also? Whereas it should do nothing if Yes is selected?
    Ok, I've managed to sort this part myself.
    I changed:
    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    I'm learning

    Only issue that remains is the one I mentioned above; with a filter applied, the rows paste incorrectly.
    Say the filter shows rows 1-6... the cells will paste into row 7, despite there being a date in A7...

    Any ideas? Many thanks

  16. #16
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I'll have to experiment with this a bit.

  17. #17
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Here is a version that should work provided that there are no gaps in the dates. If this isn't always the case, then I'll have to come up with something else.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    By gaps in dates do you mean if a row in between was blank in Column A? This shouldn't be the case.

    Seems to work ok apart from it appears to have lost the ability to copy columns G, H and I? All the others copy and paste fine but these columns seem to not be pasting for some reason?

    Thanks

  19. #19
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    After some further testing, I can confirm that the code now uses the next available blank row even with a filter applied which is great... however it seems to be inconsistent with the columns that it chooses to paste and appears to be pasting them in an incorrect order, not like for like from where they are being copied from.

  20. #20
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I don't seem to be able to duplicate the issue. I use row 4 as my test row for this very purpose. Please reconfirm what columns you want copied.

  21. #21
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I'd like B to I and M to R copied.
    They copy fine if no filter (from the home page) is applied. But if I apply the "Confirm visits that went ahead (last week)" filter, and then re-schedule Row 6 (for example), they seem to have pasted incorrectly when removing all filters?

    I have attached my test file, please try and replicate the above with Row 6 and hopefully you will see what I am experiencing.

    Thanks
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Hi dflak, did you manage to replicate the filter issue I was having above?

    Many thanks

  23. #23
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I cannot duplicate the error - it is written to row 26 whether the data is filtered or not.

  24. #24
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Mine also writes to row 26 but in the incorrect columns. Are you sure the data is going under the same column heading for you?

  25. #25
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    It is copying to the correct columns for me. Here it is, saved with the results.
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    It doesn't for me, please see attached.

    I clicked Home > "Confirm visits that went ahead last week" filter > entered "a b c d e 'Meeting Date' 'Time' 'No' g h" in the filtered columns, re-scheduled and then clicked reset filters and you will see in Row 21 which columns the data has copied to and it isn't the same ones...
    Attached Files Attached Files

  27. #27
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I was just filtering on the dates, and not filtering using the macro. When you use the macro, the information in column a is shifted to column e and the other columns are reshuffled as well. I don't know the purpose of this, but I was not aware of this behavior.

    So if you filter and then use the dropdown list, these columns will be copied as shifted. The only way around that is to rewrite the filter(s) so they "remember" what the original columns look like.

    When I reset the filters, row 6 became row 2. When I selected Cell S2 and retyped no, it copied the columns down as shifted.

  28. #28
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I see, thanks for the explanation. The problem is, the idea of the button macros are to make it easier for the users, so they will be using these rather than manually filtering.

    Do you know what amendments to the filter(s) code would be required in order for them to remember the positioning of the original columns and therefore paste correctly?

  29. #29
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I'll have to play with it to find out what it does and essentially go back to the drawing board. There may be something we can latch onto such as detecting that column B has become blank because it was shifted or maybe set a flag to say that a shift was put in place.

  30. #30
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Ok, thanks very much and please, only when you have some spare time.

  31. #31
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I'm finding that some of the filters are hiding certain columns and that is what apparently is causing the shift. Hidden columns should copy. I'll have to look at my code to see how I am doing the copying.

  32. #32
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I found a way to copy all cells including those that are hidden but it was 36 lines of code. So I opted for a loop instead.
    Attached Files Attached Files

  33. #33
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Brilliant, this seems to work successfully - thanks.

    Would it be possible to duplicate the same features to Column U?
    So currently the Reschedule message box appears when column S is set to "No"...
    I'd like the exact same functionality if possible for Column U also, so if Column U is set to No the same thing happens.
    I'm sure it's simple to do, but not sure which exact code would need to be duplicated.

    Many thanks for your 2 weeks worth of effort on this!

  34. #34
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Cell to contain Yes/No box dependent upon other cell value?

    I had to modify one line of code. I did not put in the data validation on Column U.
    Attached Files Attached Files

  35. #35
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Cell to contain Yes/No box dependent upon other cell value?

    Great, thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Applying number formatting to a cell, dependent upon contents of another cell.
    By IAPharmGirl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2016, 04:08 PM
  2. Replies: 0
    Last Post: 03-26-2015, 12:07 AM
  3. [SOLVED] insert formula on cell dependent on location of another cell
    By ghynes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2015, 05:55 AM
  4. Replies: 4
    Last Post: 09-23-2011, 06:57 AM
  5. Dynamic/dependent data validation lists - reset second cell if first cell changed
    By gazza365 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-16-2010, 06:59 PM
  6. Another cell formatting dependent on cell contents question / message box popup?
    By StargateFan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2006, 10:50 AM
  7. [SOLVED] Change Cell Color dependent on Cell Contents
    By Bill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-15-2005, 01:06 PM

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