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

1. ## 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. ## 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.

3. ## 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. ## 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. ## 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. ## 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.``

Thank you.

8. ## 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. ## 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. ## 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. ## Re: Auto filling a cell with data from a different sheet based on Today()

..and there was much rejoicing.

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

#### 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