+ Reply to Thread
Results 1 to 8 of 8

How Can I manually input data in Cell F2 in this File?

  1. #1
    Registered User
    Join Date
    10-22-2017
    Location
    Jacksonville, FL
    MS-Off Ver
    365
    Posts
    73

    How Can I manually input data in Cell F2 in this File?

    How can I update data by typing data directly into Cell F2? I still need the ability to update data by entering data in Column F as well and have it link to Cell F2 and visa versa. I need the other macros to continue working as designed. In this database, the only cell that is continually updated is cell F2. The easiest way is through direct input. Thank you for everyone who has helped on this project.
    Attached Files Attached Files

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

    Re: How Can I manually input data in Cell F2 in this File?

    How about this for a solution? When you select cell F2, it will pop up an input box so you can enter a date. What the box will do is enter the date into the date of visit column in the table below so the formula in cell F2 remains and looks up the new value. Would that work for you?
    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 Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How Can I manually input data in Cell F2 in this File?

    the only way to manually change a value in a formula dependent cell is tomanually change the values in the precedent cells from which the dependent cell draws its value. You cannot directly manually change the value in F2 without obliterating the existing formula.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How Can I manually input data in Cell F2 in this File?

    You cannot have an entry in a cell AND a formula - 1 will overwrite the other
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    10-22-2017
    Location
    Jacksonville, FL
    MS-Off Ver
    365
    Posts
    73

    Re: How Can I manually input data in Cell F2 in this File?

    yes that would work.

  6. #6
    Registered User
    Join Date
    10-22-2017
    Location
    Jacksonville, FL
    MS-Off Ver
    365
    Posts
    73

    Re: How Can I manually input data in Cell F2 in this File?

    Yes that would work

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

    Re: How Can I manually input data in Cell F2 in this File?

    OK, I'll need some help untangling the events you have coded for the sheet. Tell me what you want to happen when you select / change the various cells and ranges. I do not want to have to reverse engineer the code to figure out how to integrate the new functionality.

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

    Re: How Can I manually input data in Cell F2 in this File?

    I "broke" some of your code. It was throwing error messages so I commented it out. I'll need to know what you want to do when you activate the sheets and what you want to do with the spinner.

    I took a look at Sheet 2 (now called visits) and the code behind it. I didn't think the data would work well in the horizontal format. I want to give you a more flexible format.

    The initial data entry screen is unchanged. When you change something in Column A, the change event macro puts the value in Cell A3. You do have the capability of changing a person's name, but then you will lose their history. If you add a new name to Column A, it puts the name in cell A3 and, of course, it looks up data as you type it in the other columns.

    Several things happen when you change information in column F. Obviously the Date of Visit is looked up. The visit is added to the data table (Table_Visits) on the Visits sheet. The pivot table is refreshed and the name associated with the record is put in the filter in cell I1. The pivot table produces a list of visits sorted oldest to newest. This list is overlaid with a named dynamic range. I get a count of the number of visits in cell E1 and use Index to pick the next to last visit. This is the prior visit. If the person has only one visit, then that is also the previous visit.

    It does not matter if the person for which you are adding the date did not previously exist. The validation list for Cell A3 on Sheet 1 is now linked to the name column in the table on Sheet 1.

    When you select Cell F2 on sheet 1. It launches an input box prompting you for the date. Input boxes read things as strings, so I do some data validation to see if the information added can be interpreted as a date. If not, you get a warning and a choice to either try again, or to cancel. After entering a successful date, the date is entered in the appropriate record in the table, and the same logic applies as stated above. The record is added to the database, the pivot table is refreshed and the previous visit is picked out.

    Having the visits in an Excel Table in the format provided allows you to develop a number of pivot tables to report data any way you need.

    Once again, my apologies for breaking some of your code. I think I made some of it obsolete. I'll help you restore the functionality if you can explain what it is you want to do.
    Attached Files Attached Files

+ 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. Replies: 1
    Last Post: 05-24-2017, 04:56 AM
  2. Replies: 0
    Last Post: 05-24-2017, 04:02 AM
  3. Replies: 1
    Last Post: 05-31-2016, 05:24 AM
  4. [SOLVED] Macro to manually input which field (column) to autofilter on using an input prompt
    By rin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2012, 09:35 AM
  5. How to Catalogue Data without having to input manually
    By shez_raz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-13-2012, 03:07 AM
  6. Replies: 1
    Last Post: 06-09-2011, 03:39 AM
  7. Replies: 4
    Last Post: 08-09-2007, 09:00 AM

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