Keep a cell value on Excel 2010

Question

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%

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.

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

Answer 1

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

Answer 2

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").