Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Design for Automation
I'm trying to automate a spreadsheet that has a list of trucks and another
list of depots that are used each month but sometimes not all are used or a new depot or a new truck turns up, I want my spreadsheet to reflect this or else it doesn't balance and requires manual work to identify what's new or what's old and won't appear again. One month there may be 100 trucks used but the next there might be 90 or 110. Vehicle reference numbers for the trucks are known but there are 999 truck codes even though there may only be 200 trucks in use. A lot of vacant reference numbers. I thought that if I had a spreadsheet that takes the raw data on one worksheet and vlookup that data to a truck worksheet, this would solve my problems but would need to have 999 rows which with only 200 trucks, I thought there must be a better way. Any ideas how best to deal with this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Design for Automation
Actually my first thought is that having a sheet with the 999 rows of
information for all trucks is probably the best way. Somewhere you're going to have to have that to 'fully' automate things. I realize that it's a bit of a PITA to set up that table initially, but at least you only have to do it one time, with whatever maintenance to the list has to be done from time to time. This is the case for a lot of efforts to automate things: there is a good bit of data entry and set up to be done initially to get the effort up and running, but after that, the time saved by the automatic functions begin paying you back for that effort. You achieve 2 things after the setup is done: increased speed of operations, and improved accuracy of results. Question for you to think about: do you actually need 999 rows? You would need 200 rows to identify trucks that already have unique identification numbers, but all others would either have a 999 number or no number at all? VLOOKUP() will return a #N/A error when no match is found in the lookup table: that would clue you in to new trucks, which you'd then deal with by going to the table and adding a new entry for the new truck; a one-time operation per new truck. All '999' trucks would be treated as the same truck? "markmcd" wrote: I'm trying to automate a spreadsheet that has a list of trucks and another list of depots that are used each month but sometimes not all are used or a new depot or a new truck turns up, I want my spreadsheet to reflect this or else it doesn't balance and requires manual work to identify what's new or what's old and won't appear again. One month there may be 100 trucks used but the next there might be 90 or 110. Vehicle reference numbers for the trucks are known but there are 999 truck codes even though there may only be 200 trucks in use. A lot of vacant reference numbers. I thought that if I had a spreadsheet that takes the raw data on one worksheet and vlookup that data to a truck worksheet, this would solve my problems but would need to have 999 rows which with only 200 trucks, I thought there must be a better way. Any ideas how best to deal with this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Design for Automation
Thanks Man - if not a total solution it confirms I'm in the proper direction.
Thanks for the help - much appreciated. "JLatham" wrote: Actually my first thought is that having a sheet with the 999 rows of information for all trucks is probably the best way. Somewhere you're going to have to have that to 'fully' automate things. I realize that it's a bit of a PITA to set up that table initially, but at least you only have to do it one time, with whatever maintenance to the list has to be done from time to time. This is the case for a lot of efforts to automate things: there is a good bit of data entry and set up to be done initially to get the effort up and running, but after that, the time saved by the automatic functions begin paying you back for that effort. You achieve 2 things after the setup is done: increased speed of operations, and improved accuracy of results. Question for you to think about: do you actually need 999 rows? You would need 200 rows to identify trucks that already have unique identification numbers, but all others would either have a 999 number or no number at all? VLOOKUP() will return a #N/A error when no match is found in the lookup table: that would clue you in to new trucks, which you'd then deal with by going to the table and adding a new entry for the new truck; a one-time operation per new truck. All '999' trucks would be treated as the same truck? "markmcd" wrote: I'm trying to automate a spreadsheet that has a list of trucks and another list of depots that are used each month but sometimes not all are used or a new depot or a new truck turns up, I want my spreadsheet to reflect this or else it doesn't balance and requires manual work to identify what's new or what's old and won't appear again. One month there may be 100 trucks used but the next there might be 90 or 110. Vehicle reference numbers for the trucks are known but there are 999 truck codes even though there may only be 200 trucks in use. A lot of vacant reference numbers. I thought that if I had a spreadsheet that takes the raw data on one worksheet and vlookup that data to a truck worksheet, this would solve my problems but would need to have 999 rows which with only 200 trucks, I thought there must be a better way. Any ideas how best to deal with this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Design for Automation
One sort of a hint and I'll leave you to it.
When you set up your table, if it is a 'short' one (1-200 + 999, not all 999 possibles), then when you add new trucks to the list, insert new rows within the existing list rather than adding them to the end of it and resorting. This will keep the references to that table updated automatically and you won't have to keep revisiting the formulas to add new rows to the table reference. Either that or make the references include all possible rows right from the start. Lets say you have 6 columns of data (A through F), your reference could be like: Sheet1!$A$1:$F$1000 While this is less efficient, and thus slightly slower, than keeping it to the absolute minimum, it does make for less maintenance of formulas. Or even give consideration to selecting the range $A$1:$F$1000 on the table sheet and giving it a name to use in formulas to make typing new formulas easier. See Excel Help on topic 'Define named cell references or ranges', subtopic 'name a cell or range of cells' for guidance on that. "markmcd" wrote: Thanks Man - if not a total solution it confirms I'm in the proper direction. Thanks for the help - much appreciated. "JLatham" wrote: Actually my first thought is that having a sheet with the 999 rows of information for all trucks is probably the best way. Somewhere you're going to have to have that to 'fully' automate things. I realize that it's a bit of a PITA to set up that table initially, but at least you only have to do it one time, with whatever maintenance to the list has to be done from time to time. This is the case for a lot of efforts to automate things: there is a good bit of data entry and set up to be done initially to get the effort up and running, but after that, the time saved by the automatic functions begin paying you back for that effort. You achieve 2 things after the setup is done: increased speed of operations, and improved accuracy of results. Question for you to think about: do you actually need 999 rows? You would need 200 rows to identify trucks that already have unique identification numbers, but all others would either have a 999 number or no number at all? VLOOKUP() will return a #N/A error when no match is found in the lookup table: that would clue you in to new trucks, which you'd then deal with by going to the table and adding a new entry for the new truck; a one-time operation per new truck. All '999' trucks would be treated as the same truck? "markmcd" wrote: I'm trying to automate a spreadsheet that has a list of trucks and another list of depots that are used each month but sometimes not all are used or a new depot or a new truck turns up, I want my spreadsheet to reflect this or else it doesn't balance and requires manual work to identify what's new or what's old and won't appear again. One month there may be 100 trucks used but the next there might be 90 or 110. Vehicle reference numbers for the trucks are known but there are 999 truck codes even though there may only be 200 trucks in use. A lot of vacant reference numbers. I thought that if I had a spreadsheet that takes the raw data on one worksheet and vlookup that data to a truck worksheet, this would solve my problems but would need to have 999 rows which with only 200 trucks, I thought there must be a better way. Any ideas how best to deal with this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automation | Excel Worksheet Functions | |||
Automation?? | Excel Discussion (Misc queries) | |||
automation | New Users to Excel | |||
Worksheet form design | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) |