+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    08-11-2017
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    24

    Question 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.

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

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

    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?

  3. #3
    Registered User
    Join Date
    08-11-2017
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    24

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

    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.
    Attached Files Attached Files

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

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

    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?

  5. #5
    Registered User
    Join Date
    08-11-2017
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    24

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

    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?

  6. #6
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

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

    Quote Originally Posted by jderosa3 View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic VBA that picks up filtered data which is daily updated.
    By Raeab in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2020, 12:09 PM
  2. changes made in master worksheet will not mismatch data in other worksheets
    By paula.mccall in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2019, 07:23 AM
  3. Data (Drop Down) Value in 1 Worksheet / Copied-Updated to Another Worksheet
    By SMurtagh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2016, 01:28 PM
  4. Array of Worksheet Names to be Updated regularly from Web Query
    By Ham Goat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-12-2013, 09:04 AM
  5. Replies: 1
    Last Post: 06-21-2012, 10:08 AM
  6. dynamic pie chart labels not updated with new data
    By Flembo in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-13-2009, 07:11 AM
  7. [SOLVED] Dynamic Table Should Always Maintain Borders When Data Is Updated
    By Dynamic Table Should Maintain Borders in forum Excel General
    Replies: 0
    Last Post: 09-09-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1