+ Reply to Thread
Results 1 to 11 of 11

Auto filling a cell with data from a different sheet based on Today()

  1. #1
    Registered User
    Join Date
    12-09-2014
    Location
    idaho, us
    MS-Off Ver
    2010
    Posts
    6

    Auto filling a cell with data from a different sheet based on Today()

    Ok, I'll try to explain this a little better.

    I have two worksheets; "December" and "Backlog". In cell Q6 of "December" there is a value that changes throughout the day based on information I insert/remove from a table. On the "Backlog" worksheet, I have another table that is simply two columns: "date" and "backlog hours".

    I am wanting to auto populate the "backlog hours" cells by date. For example, say "date" is column A and "backlog hours" is column B. A1=12/10/14 A2=12/11/14 etc. I would like B1 to keep the last value entered in cell Q6 of that day. Then it would move down to the next date and change throughout that day and save at the end.

    I tried an IF formula, but can't get around the value changing back to 0/FALSE when the "date" cell no longer matches today().

    Any help would be greatly appreciated.

    Thanks

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto filling a cell with data from a different sheet based on Today()

    For this type of calculation, you will require an intentional circular reference.

    The enable iterative calculations, go to Options-> Formulas, and check the box for Enable Iterative Calculation.

    I'd recommend attaching a desensitized work sample for us examine, but until then here's an example of how it might work:

    Let's make A1:A5 1, 2, 3, 4, 5
    B1: =IF(B1=0,IF(A1=$E$1,$F$1,B1),B1) and copy down

    B1 looks at itself. If it's blank, it looks at A1. Does A1 match E1? If so, copy F1 into B1. If not, do nothing.

    As the value of E1 changes, the value of B will update. If B has a value, it will not evaluate a second time.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    12-09-2014
    Location
    idaho, us
    MS-Off Ver
    2010
    Posts
    6

    Re: Auto filling a cell with data from a different sheet based on Today()

    I tried what I believe your code should read and it seems close. I keep getting a #VALUE error though. I've attached a file that should contain all of the info needed. Let me know what you think.

    The way you worded it makes sense, it just seems like the loop is confusing the formula?!

    Many thanks,

    test backlog.xlsm

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto filling a cell with data from a different sheet based on Today()

    I can't see the target for your cells in B, but the formula shows:

    =IF(B2=0,IF(A2=$C$1,~filepathDecember'!Q6:R11,B2),B2)

    Why would cell B2 = Q6:R11? Shouldn't this be a sum? Or just a single cell it's pointing to?

  5. #5
    Registered User
    Join Date
    12-09-2014
    Location
    idaho, us
    MS-Off Ver
    2010
    Posts
    6

    Re: Auto filling a cell with data from a different sheet based on Today()

    Ha! Missed that... not sure where the R11 came from. I must have accidentally clicked that cell and kept reading over it. That seems to function well for now. Like you mentioned above, it only seems evaluate this once, so the first time I open the file, it will update to the current info in Q6, however it does not continually update as the data in Q6 changes.

    The formula I am now using is =IF(B2=0,IF(A2=$C$1,December!$Q$6,B2),B2). If I manually change the date in C1, the corresponding B cell will input the data from Q6. However if I then change the number in Q6 it does not update to the B cell. This is acceptable, however, it would be nice to continuously update as long as A#=C1. Is that a possibility?

    Again, many thanks!

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto filling a cell with data from a different sheet based on Today()

    You may want to add a bit of code to FarmPlan.

    This codey bit will run every time you change Q4, and force all open workbooks to recalculate which should update the other book if it's open.

    To apply this code, in Excel hit Alt+F11, double click the December project sheet, and copy the code below there. Close that window.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-09-2014
    Location
    idaho, us
    MS-Off Ver
    2010
    Posts
    6

    Re: Auto filling a cell with data from a different sheet based on Today()

    Thank you.

  8. #8
    Registered User
    Join Date
    12-09-2014
    Location
    idaho, us
    MS-Off Ver
    2010
    Posts
    6

    Re: Auto filling a cell with data from a different sheet based on Today()

    So, after all of that, I forgot to save my file. I went back through and re-added the formula and the code as well as updated the iterative calculations setting. Everything is functioning the same with exception to the auto update... It will not update on its own as Q6 is changed. If I double click on the corresponding B# cell and then hit enter, it will update. What could I have missed?

    Sorry and thanks...

  9. #9
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto filling a cell with data from a different sheet based on Today()

    Did you add the code to the same sheet Q6 is on?

    Note that Q6 in your example, you show Q6 as the output of other cells. The Worksheet_Change event should be targeted to Q2 and Q4, as they're the precedents.

  10. #10
    Registered User
    Join Date
    12-09-2014
    Location
    idaho, us
    MS-Off Ver
    2010
    Posts
    6

    Re: Auto filling a cell with data from a different sheet based on Today()

    Yes, I had copied it directly from your post... I just deleted it, copy and pasted again and now everything is working...

    I, again, must have missed something. It is now saved

    Thank you.

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto filling a cell with data from a different sheet based on Today()

    ..and there was much rejoicing.

+ 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: 4
    Last Post: 06-19-2016, 09:39 PM
  2. Filling a cell with YES based on data from another sheet
    By smartcard in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-01-2014, 07:26 AM
  3. [SOLVED] Pre-filling data based on a cell
    By beleza.jake in forum Excel General
    Replies: 15
    Last Post: 09-05-2012, 05:20 PM
  4. Filling a range of cells based on a cell in another work sheet
    By devious in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-02-2012, 04:33 PM
  5. Filling a cell with today's date (Datestamp completed marco)
    By Diane Alsing in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-14-2005, 07:52 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