Forum

Notifications
Clear all

Tracking data changes from a web query

2 Posts
2 Users
0 Reactions
93 Views
(@blinky101)
Posts: 2
New Member
Topic starter
 

I am attempting to track what data changes after a web query refresh. 

I have sheet 1 as the web query. 
Sheet 2 is linked to the data from the web query so I can format it the way I want it. 

The issue is that when your working with 400-600 rows you do not know which column (goals, assists, points) has increased in value from the night before when the data refreshes. 

I am looking for a way to track which cells (goals, assists, points) have increased and ideally by how much. That is what the "games last night' "goals last night" etc. columns would be for. 

I am not wanting to manually update the cells every day as I am dealing with multiple leagues and sheets on this. 

Unsure if there is a vba or formula to track this. 

So for example 
If Sidney Crosby scored 3 goals and 2 assists the night before the columns would say

E2 would reflect 1 (game played) 
G2 Would reflect 3 for goals scored
K2 would reflect 2 (for assists)
I2 Would reflect 5 (for points)

While after the data query refresh
D2 reflects 11 (total games)
F2 reflects 13 (total goals) 
H2 reflects 12(total assists) 
J2 reflects 25 (total points)

So the formula would detect the increase in D F H and J2. 
Sample sheet attached. 

 
Posted : 27/10/2017 1:51 pm
(@catalinb)
Posts: 1937
Member Admin
 

Hi Thomas,

A possible solution is to use a simple code to make a mirror copy of the Web Query sheet data:

Sub CopyData()
Sheets("Sheet3").UsedRange.ClearContents
Sheets("Web Query").UsedRange.Copy
Sheets("Sheet3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Then , after you refresh the Web Query sheet, you should compare the 2 sheets, according to what you want to see.

Can you do that manually so we can see how you want data comparison to be displayed?

 
Posted : 31/10/2017 7:19 am
Share: