2

I have two Excel books, source-book.xlsx and destination-book.xlsx. On the first I have B4 cell with a drop down menu (January to March) and C4 cell to input a value.

On the destination-book I have three columns:

-B4 to B6 to collect the values from source-book C4 with the formula =IF('[source-book.xlsx]source '!$B$4="January ";'[source-book.xlsx]source '!$C$4*10%;F4)

The space after January on the formula is because I did this on the phone and did not realized about the trailing space mobile keyboards places automatically after each word.

-C4 to C6 with the three months (January to March)

-and D4 to D6 to get the values from B4 to B6 with the formula =B4 =B5 =B6.

-Then I have D7 to calculate the accumulated value for the three months with the formula =(D4+D5+D6)*35%

both books and it´s relations

What´s the problem?

Well, as in source-book the value of B4 is on a month it reflects on the corresponding cell on the destination-book, the value for January on B4, for February on B5, and for March on B6; but what I need to calculate on D7 is the sum-up of the the three months. Then I need a way, with a formula or a Macro, to keep the value of each month on D4, D5 and D6, even when the user changes month on source-book.

For example, in the picture, January has 500,000.00, then user changes to February and inputs 700,000.00 and in March 800,000.00, then it should, in D4 keep the 500,000.00 when changes to February, keep in D4 the 500,000.00 and in D5 the 700,000.00 when changes to March, so the calculation on D7 would really be the three months accumulated amount. Just like in this picture below.

the expected result

I have deeply search on the web, also here on stackoverflow, but found nothing to solve this using just Excel. Can anyone help me with this one?

Here is the link from where the example with the above Excel files can be downloaded download the example files

Improve this question
8
  • 2
    So in the destination workbook you're copying and pasting value to column D and column B is a link to the source workbook... is the issue that this is manual? What would the trigger be to update/change the column D values? Commented Oct 30, 2024 at 13:39
  • No, Cyril, the values on B are retrieved with a link to the source-book and the data on D is retrieved with a link to B. The problem is that when user changes month in source-book B and value in source+book C, the value of the previous month in D changes to 0, and I need, someway, to keep the value of the previous months in destination-book D4, D5 and D6 so that the formula in D7 calculates the three months amount. Commented Oct 30, 2024 at 14:12
  • 1
    Similar to above... you can manually paste-value in column D from B and those values are no longer linked. That doesn't account for when you want to trigger the values in column D updating. If one were to keep inserting a column to D and keep all previous values to the right, so you're seeing the history, that is something that can be done quite quickly, but does impact how your data exists (we don't know if you have more data to the right, etc., which would be impacted). Commented Oct 30, 2024 at 15:21
  • 1
    I believe I'm possible being too cryptic; let me rephrase: If you want to capture time stamps, then you need to capture timestamps. You have not said "when" you need to capture; you can script this and manually press a button to capture your time-stamp values, etc. Currently you do not have enough details about your data, and anything being suggested is subjective. You have not provided any script to allow for an objective correction/fix, and that leaves the post as a request for a script, where SO is not a code-for-you service. Commented Oct 30, 2024 at 16:54
  • 1
    My apologies, "timestamp" was incorrect terminology; it should be "snapshot in time". You want to capture values in column B and not calculate a total value in column D until you've determined when to use the data from column B. We do not know when you're going to determine that. Commented Oct 30, 2024 at 18:57

2 Answers 2

Reset to default
1

Since this can be done with VBA, you need to save your source workbook with macro.

Place this code in the source-book.xlsm source worksheet's code pane

Private Sub Worksheet_Change(ByVal Target As Range)
Dim calcsheet As Worksheet, selectsheet As Worksheet
Dim selwb As Workbook, calcwb As Workbook

Set selwb = Workbooks("source-book.xlsm")
Set calcwb = Workbooks("destination-book.xlsx")
Set selectsheet = selwb.Worksheets("source")
Set calcsheet = calcwb.Worksheets("sheetname")  'apply the actual name of the result sheet

If Not Intersect(Target, selectsheet.Range("B4")) Is Nothing Then
 found = calcsheet.Range("C4:C6").Find(selectsheet.Range("B4"), , xlValues, xlWhole).Row
 calcsheet.Cells(found, "B") = selectsheet.Range("C4") * 0.1
End If
End Sub

In the destination-book

Don't fill with formulas the Range("B4:B6") since the code will place the value here.
This will preserve all values of the cell C4 when you select a month in the dropdown. Therefore first place the actual value in C4 and then select a month from the dropdown. You can select repeatedly the same month and the code will always place the actual value.

Leave the formulas in the Range("D4:D7") unchanged for the additional calculations.

To avoid error, all monthes listed in the dropdown must be found in the destination sheet Range("C4:C6").

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Estimated Black cat, as soon as I reach home I will test your solution since I am on the street and my phone Excel editor doesn't not support VBA. But reading the code I can understand what it is supposed to do and I think it will work or be very close to the solution. I will tell you as soon as possible. Thanks
And additional question. What do you mean by 'sheetname' in line 8 'Set calcsheet = calcwb.Worksheets("sheetname") 'apply the actual name of the result sheet'?
Another question, which now I realize I forgot to place on the main question. If I open the workbook on January, select the month and place the value on C4, then I save and close the book, then in February I open the book, select the current month, place the value for this month, will still be the value of D4 be the same I placed on January? I mean, will it persists through time and subsequent sessions?
In reviewing the lack of indication from the OP around when timestamps are required to be captured, I would be hesitant to use a worksheet change event. Leaving the linked cells for D4=B4 does not seem to address having columns(d) dependent on some unknown-to-us user-defined criterion for when it needs updated.
@SIMBIOSIS surl Yes, sheetname is the actual name of the result sheet. Yes after save the set values are kept in the result sheet.
|
0

I will ANSWER my own question since, as it was closed I...

After some research I have developed this solution using the following macro:

Sub Execute()
   Row = 0
   temp = False
   Do
     Row = Row + 1
     If Workbooks("destination-book.xlsm").Worksheets("destination").Cells(Row, 7) = Workbooks("destination-book.xlsm").Worksheets("destination").Cells(4, 2) Then
     Workbooks("destination-book.xlsm").Worksheets("destination").Cells(Row, 8) = Workbooks("destination-book.xlsm").Worksheets("destination").Cells(10, 3)
     temp = True
     End If
   Loop While Row < 12

 End Sub

The new example books with the solution can be downloaded from here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.