SUMIFS formula to fill data in cell depends upon date and name and also keep previous data in a cell

Question

I have an excel with two sheets named raw and data here

raw sheet :

date sheet :

In raw sheet I will be uploading data manually.

What I am looking for :

In this, I want to create a formula which will check each name from the data sheet in raw sheet and it has to pick the total value (D column in raw) and update the same in data sheet in a cell which matches with last modified date.

For example :

Name is R420864561 and its last modified date is 20141201 and total value is 10.

So in my data sheet, it has to be updated like this

Above, value 10 is updated in a cell which matches the last modified from raw sheet with date in data sheet, here both are 20141201.

I am using below formula for this (formula in C3)

=SUMIFS(raw!$D:$D,raw!$A:$A,$A3,raw!$C:$C,C$2)

Now, if last modified date 20141202 with total 50

Now, data sheet will updated like below using above formula

What I need now :

In above, if I update value in another date, the data updated in previous date is getting changing to 0. But I need to keep that data in that cell, if there is no update in that.

In above case, I need to keep previous data (for date 2014201)10 in data sheet cell C3, and update 50 in D3 (for date 20141202)

I have added below code with macro, to copy paste the formula value, as simple value

 Dim rngCell As Range
    For Each rngCell In ActiveWindow.RangeSelection
        rngCell.Value = rngCell.Value
    Next rngCell

But that is not working here, It still resetting previous value to 0 when I update for a different date.

Any suggestion to correct this is much appreciated

I have uploaded the copy of the excel which I am working in below location :

https://www.dropbox.com/s/jpubzqbco5mflu5/1.1_test.xlsm?dl=0

Answer 1

This should work assuming your loop is correct.

rngCell.Copy
rngCell.PasteSpecial Paste:=xlPasteValues

Try For Each rngCell In Application.Selection to start the loop

Answer 2

you VBA is wrong:
update it using this one:

Sub saveMyData()

Set ws = ActiveSheet

ws.Range("C3:E999").Copy

ws.Range("C3").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

Range("C3").Formula = "=SUMIFS(raw!$D:$D,raw!$A:$A,$A3,raw!$C:$C,C$2)"
Range("D3").Formula = "=SUMIFS(raw!$D:$D,raw!$A:$A,$A3,raw!$C:$C,D$2)"
Range("E3").Formula = "=SUMIFS(raw!$D:$D,raw!$A:$A,$A3,raw!$C:$C,E$2)"

End Sub

Here is ***UPDATED***the example sheet to try and see if it works.

it was because your VBA code was mixed up with unncessary codes.

Answer 3

From all appearances, the problem is with how you are presenting the question and plan to use the raw spreadsheet. From the information provided, it seems like you plan on entering records in raw, but then you also intend to change them. Also, based on your example and how you are describing the problem, it's not clear why you are using SUMIFS rather than INDEX(MATCH).

It would seem that your raw spreadsheet should have multiple records, one for each Name/Last Modified combination, and more than one for each combination to necessitate the use of SUMIFS. Then your formula in data will pick up the relevant info in raw.

If you plan on only having one entry for each Name in raw, and changing the last modified date and the total in raw it seems rather pointless to have separate spreadsheets at all, just enter it into data in the first place under the respective last modified date.