Dynamic data that is updated on a worksheet, can it be captured and made into an array?

I hope I can explain this properly and see if there is a way to do this as this is more of a theoretical question.

I have reports that dump into a directory on a server every 30 minutes... I take this data on a worksheet and move it to another sheet I show sales people - this report is a sales report for the day as it accumulates. The file is over written each time the system dumps it. When it over writes the data I would like to on the sheet I performed the Vlookup on... to capture the 30 minute increments and make sort of like a stock chart for the day.

So first 30 minutes might have zero sales, the next might have gone up to 1,000.00 and the next 2,000.00, so on and so forth.

How can I keep track of the data as it changes each half hour so I can create a spark chart or some type of chart that shows the progression? Is this even possible.

Finger crossed because I think this could be a neat way to look at somewhat simple dynamic data.

you said: I take this data on a worksheet and move it to another sheet I show sales people - this report is a sales report for the day as it accumulates. The file is over written each time the system dumps it.

ok, so you said the file is overwritten everytime the report is dropped from the server. if that's the case, you can't do what you want because a file is made up of many worksheets and you said that you are only moving the dumped date from one sheet to another in the same file. or is it from one file to another?

not sure what you mean by this either: When it over writes the data I would like to on the sheet I performed the Vlookup on

does that mean that on the sheet you have copied the data to, that the VLOOKUP() formulas are there?

I left out a crucial detail... I should clarify.

I have a server that the CSV file gets dumped to. I use a BAT file to move these reports to a Google File Stream folder. I then use an app called SheetGo hat then allows me to sync this file with a Google sheet at specifed time intervals. I am using a VLOOKUP on this sheet within the workbook. When the file on the server get's over wrote. It also overwrites the google sheet that I am referencing with the VLOOKUP. See attached for an example.

The sheet 1 is where I would like to record an array form the second sheet each time it is updated (while this is a static example... just imagine the second sheet being updated every 30 minutes with new up tot he minute data for the day as a cumulative total)

So really I need to make this work in google sheets... or maybe the beginning portion of a script that might record the data over the day so I can chart the cumulative values...

Thanks if you are able to help. Sorry if it is confusing.

The sheet 1 is where I would like to record an array form the second sheet each time it is updated

I don't really think you need to do this. it looks to me as though all you need to do in code would be something like this:

=> detect the update when the file drops
=> either use an excel range object to throw the data from the sheet that gets updated to the cumulative sheet at the end of the data that was already there
=> or, as an alternative to the previous suggestion, simply put VLOOKUP functions in all the cells on Sheet1 in the appropriate places to look up the data from the master sheet that gets update, then copy all the data on sheet1 and paste it onto itself, replacing the functions with literals.

how does that sound? and by the way, it doesn't sound like your process is all that great. it's not bad from the way it sounds, however I'm guessing your management personnel set this up and you were just thrown into it?

Hmmm... a bit beyond my scope but I will dig further. I appreciate the help.

As far as what I am trying to accomplish... cleaning the data and processes within a world of an ERP system. You know how it is right?

Originally Posted by jderosa3

As far as what I am trying to accomplish... cleaning the data and processes within a world of an ERP system. You know how it is right?

of course I do. that's exactly technology jobs suck a lot of times. because management is forced to buy junk like that. and the only reason for that is because they have to APPEAR as though they are buying value from other companies. and that's a very sad thing. if they knew anything at all, they wouldn't do it and the value would naturally increase for them. but of course, I've not met one manager in my life that understands that truth. so don't blame them. you're not alone.

post back here when you start something. it should not be difficult. trust me, it's not really beyond your scope. VBA is the easiest language in the world to learn. now, if you want complexity, try learning the newest javascript frameworks for web apps. new ones come out about every ''x'' months. it's just stupid. funny thing is, NONE of them have done anything new since the first one came out.