Copy data from one sheet to another daily.

mpfarrer

I need to copy a row of data (Data set A) from one sheet (Summary Sheet) to another on a daily basis. The intent is to track this data over time to discover trends and to see progress.

The data in Data set A is pulled and manipulated from several other sources using formulas ... so thus it is (I think) in feasible to pull the data straight from the sources.

I need to pull [Delta - Non Zero = Issue] Column (the right most column) and put it daily into a Summary Sheet.

Please note ... the data in the Summary Sheet was done manually to show what I need.

I have tried using Automation (if row is added or changed, copy to new sheet), but when this runs, it zero's out the day before and copies over the new row ... so thus I lose the previous days data (not good).

I tried using an index, but that does not work because when Data set A changes daily, it changes the results in the Summary Sheet.

I have used a "pass through" sheet that pulls data from Data Set A, pivots it so that it can easily be copied using the following formula ...

=SUMIFS({BackOffice: PSE End of Day Checkpoint v6.2 Range 1}, {BackOffice: PSE End of Day Checkpoint v6.2 Range 2}, $Date@row, {BackOffice: PSE End of Day Checkpoint v6.2 Range 3}, [Technician 2 Delta]$1)

But this does not work either ... it is deleting the day before.

Any help would be greatly appreciated.

Paul Newcome

So you want to take the [Delta - Non Zero = Issue] column, flip it so it is going out horizontally and have that copied over to another sheet on a daily basis?

mpfarrer

Yes sir.

As a note, we do not have the "advanced" license yet that allow for pivot tables.

Paul Newcome

To do this you will need an intermediary sheet. How often are new rows added to the original sheet (vertical structure)?

mpfarrer

Daily ... basically at the end of the day.

A technician will report their truck inventory, then how many installs they have done in a day. I take the number of installs and subtract the amount of inventory decrease with the goal of 0. If the number is higher or a negative, it indicates an issue. These daily numbers are used by their supervisors to resolve the issues on a daily basis. The goal of this exercise (my Smartsheet issue) is to collect data over time so that I can see if any one technician has constant issues, or someone is trending positively/negatively, or if yesterday's issue was resolved.

Paul Newcome

So if new rows are being added to the vertical sheet daily, then converting it to horizontal would require a new column daily. Why not just track on the sheet that has the vertical data? You can use a helper column and an INDEX/COLLECT to pull in the previous day's entry then use another column to run the calculation.

From there you can slice and dice the data through reports and filters.

mpfarrer

I think I understand ... but let me make sure ...

Step one: Technician enters value (ie. Inventory Numbers and Install Number)

Step two: Using a helper column(s), I convert the number(s) to a value

Step three: I pull the "valued" data into another sheet using INDEX/COLLECT?

Paul Newcome

In the very first screenshot... Where is all of that data coming from?

mpfarrer

The beginning of the day inventory is copied from the end of day numbers from the day before. The blue columns are coming from a different sheet (fed by a form). The End of day numbers and Number of Installs are being fed by a from another sheet (basically a working sheet where technicians input their numbers). The pink columns are calculations (basically I take the beginning of the day numbers plus any new inventory and subtract the number from the inventory reported at the end of the day). Finally the Delta column is a calculation.

Here is the process flow (try not to laugh too much):

1. Technician fills out a nightly form
2. a. Includes number of inventory on hand for each category
3. b. The number of installs completed
4. The form populates the "End of Day Checkpoint" sheet where:
5. a. The previous day's End of Day numbers are moved to the Start of Day numbers
6. b. The inputed end of day numbers are inserted
7. c. The Number of installs are inserted
8. The BackOffice sheet is then populated with key values: (mainly end of day inventory and # of Installs)
9. a. Start of Day Inventory Numbers
10. b. End of Day Inventory Numbers
11. c. Number of Installs
12. d. Restock numbers from a separate form/sheet (tracking every handoff of a module)
13. Calculations are made on the BackOffice Sheet

The purpose of this process is we want to know where every module we have is, and alert leadership when there is an issue with inventory.