title: copy data from 1 worksheet to another worksheet with data ranges that update frequently
body: <p>Alright here is what I am trying to do.</p>
  
  <p>I have a data range that updates whenever I open the spreadsheet which works fine. The problem I have is that the data cannot be formatted different from how it is generated and what I need to do is populate this data to another sheet for calculations and reporting.  Since this data doesn't have a unique indicator like Route Name all the way through (has "---" instead) I am unable to use any if statements that I can figure out the logic for.  Also I am at a loss for what to try and use for a macro/VB script to process this data.</p>
  
  <p>This is to give you an idea of what kind of data set I am working with.  The number of items processed under Route Name will change.  So even though Daily C as two entries tomorrow it could have three and so forth.  Once I am able to process this info into my other worksheets I am good in that my macros and functions there work fine but I am trying to get away from manually entering this data through copy and paste.  This data is uploaded to a hidden worksheet.</p>
  
  <pre><code>DATA EXAMPLE
  
  ROUTE   date/time   Duration    % complete //column headers
  Daily C 12/29/2013 7:47:06 AM   0h:32m: 27s 93.4
  --- 12/28/2013 7:20:50 AM   0h:57m: 43s 94.6 //this record falls under Daily C
  Process E   12/28/2013 8:26:02 PM   18h:47m: 5s 75 
  --- 12/23/2013 6:53:00 PM   0h:34m: 32s 100 //this record falls under Process E
  --- 12/23/2013 10:35:39 AM  0h:14m: 8s  100 //this record falls under Process E
  --- 12/23/2013 1:29:36 AM   1h:53m: 34s 100 //only 3 records but could have more
  Daily X 12/29/2013 3:14:30 PM   0h:16m: 40s 88.2
  --- 12/23/2013 10:37:34 AM  0h:30m: 31s 96.1 
  Daily AB    12/29/2013 7:18:27 PM   1h:29m: 24s 95.8
  --- 12/29/2013 7:17:20 AM   1h:17m: 53s 97.7
  --- 12/28/2013 6:51:11 PM   1h:45m: 46s 90.5
  --- 12/28/2013 7:04:03 AM   8h:24m: 12s 100
  --- 12/27/2013 7:13:30 PM   3h:42m: 9s  95.5
  --- 12/27/2013 1:44:47 PM   0h:46m: 56s 97.7
  --- 12/27/2013 6:59:45 AM   1h:27m: 23s 99.6
  --- 12/26/2013 10:10:05 PM  0h:42m: 6s  97
  --- 12/26/2013 1:45:56 PM   1h:23m: 18s 83.3
  --- 12/26/2013 6:41:00 AM   3h:56m: 19s 99.2
  --- 12/23/2013 7:32:17 PM   2h:22m: 10s 96.2
  --- 12/23/2013 7:59:22 AM   1h: 6m: 57s 95.8
  Process D   12/28/2013 8:12:33 PM   19h: 1m: 32s    75
  --- 12/23/2013 10:36:28 AM  0h:11m: 31s 100
  Daily D 12/29/2013 1:58:56 PM   0h:22m: 18s 90.3
  --- 12/29/2013 7:43:42 AM   0h: 4m: 50s 100
  --- 12/28/2013 1:53:23 PM   0h: 5m: 36s 90.3
  --- 12/28/2013 7:33:14 AM   0h: 5m: 9s  100
  </code></pre>
  
  <p>The nice part is when I upload the external data it separates properly into columns just like you see with the headings "Route", "date/time", Duration, and "% complete" however since the data changes including numbers of records in each section I haven't the foggiest idea on how to process this in Excel without a set key.  If some way I could change all those darn --- to its route title even would be good to but I am looking for any sort of answer here.  THanks.</p>