Updating Excel Data from another spreadsheet with a single cell reference

Hi All,

Today I am trying to update an excel spreadsheet with updating data from another spreadsheet. What makes this a little tricky is the spreadsheet with the updating data is from a formula from a single cell (as shown by the red square). This value of 1.01% for example, updates weekly according to the formula and I was wondering if the updated values can be added into the cells (on another spreadsheet as shown by the blue square on the other screenshot).

Overall, this means that I would be able to recieve updated data (from the red square) which inputs into the Weekly Maintenance DT Actual %. I was thinking about an If Statement but I would assume that it has to be a little more advanced as the changing value makes a little tough.

Kind Regards,
Joel

It is hard to work with a picture. It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

Cell L15(Workbook 1); Cell C175 (Workbook 2)

Hi All,

I have included the spreadsheets this time.

So in workbook 1, on Cell L15 we have a calculated % DT (Downtime) Due to Engineering column which is calculated using FILTER and SUM functions.

It would be great if I could get this figure pulled through weekly into Column C, starting with Cell C175 being this current week. When the number in Cell D15 is refreshed weekly I would like it to add the new number over into the Column C.


Thanks for the Help,
Joel

Automatically copying a cell to another cell is normally done with a Worksheet_Change macro. The problem here is that a Worksheet_Change event is automatically triggered only when a cell is changed manually. Cell D15 is not entered manually but instead is the result of a formula. A Worksheet_Calculate event is triggered automatically when a formula in a cell is calculated. The problem with this method is that the event is triggered by any formula in the worksheet, not just the formula in D15. Assuming that the values in the range A28:C315 are entered manually, the only way that I can think of to possibly make this work is to track the manual changes in that range for any manual data entry because it seems that the formulae in D8:D14 reference the values in A28:C315 which determine the value in D15. If this is the case, then a Worksheet_Change macro should work because it will track manual entries. You could give this a try to see if it works for you. First make sure that both workbooks are open. Then copy and paste this macro into the worksheet code module by doing the following: right click the tab name for your "Main DT Report" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. After entering or changing any value in A28:C315, press the ENTER key. Check column C in Workbook 2 to see if D15 was copied to C175. Change the workbook name and sheet name (in red) to suit your needs.

Please Login or Register  to view this content.

I don't think any event code will work because all the referenced cells are the result of a formula. None is entered manually. The only other approach I can think of is to run a macro manually when you want to update column C. You could add a button on the Main DT Report sheet and assign this macro to it.

Please Login or Register  to view this content.

As an afterthought, did you try changing D15 to L15 in the code? Also, did you place the macro in the worksheet code module as I described in Post #4?

Please Login or Register  to view this content.

Your threads have been merged and I'm moving this to the Excel General section for visibility.