justinlevi
New Member
- Joined
- Jul 29, 2014
- Messages
- 1
So here’s my situation…
I’ve searched the web and various sources for using different formulas, various VB scripts, and other such solutions. I can never seem to get anything to work in Google Sheets, and it’s become a massive headache. I’m just about to the point of learning js from the beginning and working my way up from there. In the meantime, I’m seeking help from more knowledgeable persons.
I manage, along with four others, a monthly spreadsheet that relates to our daily functions. Specifically, we are management and take calls, filling in the information on a Sheet for each customer of any given day once the work has been performed by a technician. This is how we track everything - i.e., sales; another work order being created; the date that work order will need to be scheduled for; if the current work order is closed, open, cancelled or rescheduled; does the customer currently have broadband; did we get a code for connecting; did the technician perform a completion call; who took the call; etcetera.
All of this information is acquired throughout the duration of the day. Meanwhile, early in the morning I export an excel sheet containing the ‘routesheet’. all of our customers and their information for the given day. That spreadsheet is copied and pasted into, let’s say a Sheet titled ‘JULY 21’ in the workbook titled ‘July Completion Calls’.
However, the Routesheet isn’t in the same layout as the end result in the Completion Call grid. My workaround for this is making the copy and paste process simple by using Hidden columns, and making an entire column (column L) reflect everything in Column ‘A’, with the simple “= A 1”, etcetera. I then hide column A as well.
Columns B, E, F, J, and K are totally irrelevant to the grid. Column A (therein “Attributes”) ultimately should be moved to last, thusly becoming Column F.
So what I end up with is as follows:
[TABLE="width: 624"]
<colgroup><col><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Technician
[/TD]
[TD]Customer
[/TD]
[TD]Work Order #
[/TD]
[TD]Phone1
[/TD]
[TD]Phone2
[/TD]
[TD]Attributes
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]xxxxxx
[/TD]
[TD]xxxxxx
[/TD]
[TD]1000000001
[/TD]
[TD]9999999999
[/TD]
[TD]9999999999
[/TD]
[TD]xxxxxx
[/TD]
[/TR]
</tbody>[/TABLE]
Instead, what I have is as follows:
[TABLE="width: 624"]
<colgroup><col><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"></colgroup><tbody>[TR]
[TD][/TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[/TR]
</tbody>[/TABLE]
And I end up hiding cells A, B, E, F, J and K. This works for the moment, as I can work around anything without messing up the Filters, Conditional Formatting, and Data Validation of the sheets and cells within. However, I’m not the only person who accesses the grid and it often gets messed up. All Sheets, named respectively for each day of the month, should remain uniformly the same amount of rows and columns. This makes it easier to gather data from all sheets simultaneously on a ‘month totals’ sheet, to use Pivot Table reports, etc..
The Import feature in Google Sheets doesn’t work for this. I’ve tried a ton of ways to work around it. What I honestly want is a way to just click a button and import a routesheet to the specific area of that days Sheet, which would be A2 to F200, maximum. We never exceed 200 customers, and rarely have made it to as many as 150. At the same time as simplifying the importing of the day’s customers, I also want to automatically filter out unneeded data and reorder its presentation.
Overall, the grid is extended to column Q, or column U, depending on the time of year and what information we wish to ‘fill’ out based on the criteria of our completion calls during a period. And this is AFTER I have deleted the Hidden and unused columns.
I apologize in advance for being so long winded and if my needs are too high. I’m just seeking out whatever I can get - ideas, suggestions, advice, etc. Thanks for anything and everything.
I’ve searched the web and various sources for using different formulas, various VB scripts, and other such solutions. I can never seem to get anything to work in Google Sheets, and it’s become a massive headache. I’m just about to the point of learning js from the beginning and working my way up from there. In the meantime, I’m seeking help from more knowledgeable persons.
I manage, along with four others, a monthly spreadsheet that relates to our daily functions. Specifically, we are management and take calls, filling in the information on a Sheet for each customer of any given day once the work has been performed by a technician. This is how we track everything - i.e., sales; another work order being created; the date that work order will need to be scheduled for; if the current work order is closed, open, cancelled or rescheduled; does the customer currently have broadband; did we get a code for connecting; did the technician perform a completion call; who took the call; etcetera.
All of this information is acquired throughout the duration of the day. Meanwhile, early in the morning I export an excel sheet containing the ‘routesheet’. all of our customers and their information for the given day. That spreadsheet is copied and pasted into, let’s say a Sheet titled ‘JULY 21’ in the workbook titled ‘July Completion Calls’.
However, the Routesheet isn’t in the same layout as the end result in the Completion Call grid. My workaround for this is making the copy and paste process simple by using Hidden columns, and making an entire column (column L) reflect everything in Column ‘A’, with the simple “= A 1”, etcetera. I then hide column A as well.
Columns B, E, F, J, and K are totally irrelevant to the grid. Column A (therein “Attributes”) ultimately should be moved to last, thusly becoming Column F.
So what I end up with is as follows:
[TABLE="width: 624"]
<colgroup><col><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Technician
[/TD]
[TD]Customer
[/TD]
[TD]Work Order #
[/TD]
[TD]Phone1
[/TD]
[TD]Phone2
[/TD]
[TD]Attributes
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]xxxxxx
[/TD]
[TD]xxxxxx
[/TD]
[TD]1000000001
[/TD]
[TD]9999999999
[/TD]
[TD]9999999999
[/TD]
[TD]xxxxxx
[/TD]
[/TR]
</tbody>[/TABLE]
Instead, what I have is as follows:
[TABLE="width: 624"]
<colgroup><col><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"><col width="*"></colgroup><tbody>[TR]
[TD][/TD]
[TD]
A
[/TD][TD]
B
[/TD][TD]
C
[/TD][TD]
D
[/TD][TD]
E
[/TD][TD]
F
[/TD][TD]
G
[/TD][TD]
H
[/TD][TD]
I
[/TD][TD]
J
[/TD][TD]
K
[/TD][TD]
L
[/TD][/TR]
[TR]
[TD]
1
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
XXX
[/TD][TD]
=a2
[/TD][/TR]
[TR]
[TD]
3
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
xxx
[/TD][TD]
=A3
[/TD][/TR]
</tbody>[/TABLE]
And I end up hiding cells A, B, E, F, J and K. This works for the moment, as I can work around anything without messing up the Filters, Conditional Formatting, and Data Validation of the sheets and cells within. However, I’m not the only person who accesses the grid and it often gets messed up. All Sheets, named respectively for each day of the month, should remain uniformly the same amount of rows and columns. This makes it easier to gather data from all sheets simultaneously on a ‘month totals’ sheet, to use Pivot Table reports, etc..
The Import feature in Google Sheets doesn’t work for this. I’ve tried a ton of ways to work around it. What I honestly want is a way to just click a button and import a routesheet to the specific area of that days Sheet, which would be A2 to F200, maximum. We never exceed 200 customers, and rarely have made it to as many as 150. At the same time as simplifying the importing of the day’s customers, I also want to automatically filter out unneeded data and reorder its presentation.
Overall, the grid is extended to column Q, or column U, depending on the time of year and what information we wish to ‘fill’ out based on the criteria of our completion calls during a period. And this is AFTER I have deleted the Hidden and unused columns.
I apologize in advance for being so long winded and if my needs are too high. I’m just seeking out whatever I can get - ideas, suggestions, advice, etc. Thanks for anything and everything.
