Update data when new data is entered in a rage of cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2211652%22%20slang%3D%22en-US%22%3EUpdate%20data%20when%20new%20data%20is%20entered%20in%20a%20rage%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2211652%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20update%20data%20on%20a%20different%20sheet%20(like%20a%20dashboard)%20only%20when%20new%20data%20is%20entered%20in%20a%20different%20sheet%20-%20and%20only%20display%20the%20new%20data.%26nbsp%3B%20For%20example%20in%20sheet%202%20I%20have%20a%20row%20of%20names%20and%20then%20a%20new%20dollar%20amount%20is%20entered%20next%20to%20their%20name%2C%20on%20sheet%201%20I%20only%20want%20to%20display%20their%20name%20and%20the%20dollar%20amount%20when%20the%20dollar%20amount%20is%20updated.%26nbsp%3B%20Only%20one%20will%20update%20at%20a%20time%20so%20I%20only%20want%20to%20display%20one%20name%20and%20dollar%20amount%20at%20a%20time%20when%20it%20updates%20on%20sheet%202.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2211652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2211927%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20data%20when%20new%20data%20is%20entered%20in%20a%20rage%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2211927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F998118%22%20target%3D%22_blank%22%3E%40rwagner34%3C%2FA%3E%26nbsp%3B%20This%20can%20be%20done%20using%20a%20macro%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0ADim%20dashUpdate%20As%20Range%0ASet%20dashUpdate%20%3D%20ThisWorkbook.Sheets(%22Dashboard%20sheet%22).Range(%225%3A5%22)%0A%20%20%20%20If%20Intersect(Target%2C%20Range(%22G%3AG%22))%20Is%20Nothing%20Then%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20Target.EntireRow.Copy%20dashUpdate%0A%20%20%20%20End%20If%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20macro%20must%20be%20on%20the%20sheet%20object%20where%20the%20data%20will%20be%20updated%3C%2FP%3E%3CP%3E%22Dashboard%20sheet%22%20needs%20to%20be%20the%20name%20of%20the%20sheet%20where%20the%20row%20will%20be%20copied%20to%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20change%20%225%3A5%22%20to%20the%20row%20where%20you%20want%20that%20row%20copied%20to%3C%2FP%3E%3CP%3Eand%20change%20%22G%3AG%22%20to%20the%20column%20that%20matches%20the%20column%20with%20dollars%20that%20if%20updated%20you%20want%20copied.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2213877%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20data%20when%20new%20data%20is%20entered%20in%20a%20rage%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2213877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3EI%20am%20getting%20an%20error%20from%20the%20first%20line%20-%20below.%26nbsp%3B%20I%20did%20update%20the%20other%20data%2C%20should%20%22Worksheet_Change%22%20be%20something%20else%3F%26nbsp%3B%20Under%20the%20screen%20shot%20is%20the%20Marco.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rwagner34_0-1615899513178.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F264333i4BB0211CEF29A5C3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rwagner34_0-1615899513178.png%22%20alt%3D%22rwagner34_0-1615899513178.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EDim%20dashUpdate%20As%20Range%3CBR%20%2F%3ESet%20dashUpdate%20%3D%20ThisWorkbook.Sheets(%22Dashboard%22).Range(%22%24A%246%22)%3CBR%20%2F%3EIf%20Intersect(Target%2C%20Range(%22%24B%244%3A%24B%24101%22))%20Is%20Nothing%20Then%3CBR%20%2F%3EElse%3CBR%20%2F%3ETarget.EntireRow.Copy%20dashUpdate%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Is there a way to update data on a different sheet (like a dashboard) only when new data is entered in a different sheet - and only display the new data.  For example in sheet 2 I have a row of names and then a new dollar amount is entered next to their name, on sheet 1 I only want to display their name and the dollar amount when the dollar amount is updated.  Only one will update at a time so I only want to display one name and dollar amount at a time when it updates on sheet 2.  

Thanks.

6 Replies

@rwagner34  This can be done using a macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dashUpdate As Range
Set dashUpdate = ThisWorkbook.Sheets("Dashboard sheet").Range("5:5")
    If Intersect(Target, Range("G:G")) Is Nothing Then
    Else
        Target.EntireRow.Copy dashUpdate
    End If

End Sub

This macro must be on the sheet object where the data will be updated

"Dashboard sheet" needs to be the name of the sheet where the row will be copied to 

and change "5:5" to the row where you want that row copied to

and change "G:G" to the column that matches the column with dollars that if updated you want copied.

 

 

@mtarler 

Thank you!

I am getting an error from the first line - below.  I did update the other data, should "Worksheet_Change" be something else?  Under the screen shot is the Marco.  

rwagner34_0-1615899513178.png

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dashUpdate As Range
Set dashUpdate = ThisWorkbook.Sheets("Dashboard").Range("$A$6")
If Intersect(Target, Range("$B$4:$B$101")) Is Nothing Then
Else
Target.EntireRow.Copy dashUpdate
End If

End Sub

 

@rwagner34 so in the VBA editor you need to open the code page for the specific Sheet.  And then in that window select Worksheet from the left drop down and the right will likely auto populate with SelectionChange but otherwise select that:

mtarler_0-1615903192987.png

If you pasted that Sub into workbook or a module or something, yeah it won't understand.

Thank you! I got it to work!

@mtarler Is there a way for this to work if I am linking the changed cell to another worksheet?  It does not seem to update the dashboard when the data changes in the cell that is pointing to another excel file.

In the cell that is changing:

='[rotation schedule.xlsx]Monday 1A'!$G$2

I have the code below on the worksheet, if I manually change the field it appears on the Dashboard, but it does not when I link it like above and the data changes.  The field that is changing is in D2

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dashUpdate As Range
Set dashUpdate = ThisWorkbook.Sheets("Dashboard").Range("$A$6")
If Intersect(Target, Range("$A$2:$D$47")) Is Nothing Then
Else
Target.EntireRow.Copy dashUpdate
End If

End Sub

 

That is correct since the contents of the cell haven't changed only the value being displayed is changing. There is a legacy functionality that has to do with track changes that might be useful for this but I'm not going to go there. Instead, you would need to create a mirrored sheet and track those cells of interest on every _CALCULATE and if a change is detected you can copy the row and update the value onto the mirrored/storage sheet. That said, what will you do if more than 1 value changes? Technically this could be a concern on the _change function also.