Worksheet Design for Automation

[markmcd]

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?

 

[JLatham]

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]

Thanks Man - if not a total solution it confirms I'm in the proper direction.
Thanks for the help - much appreciated.

 

[JLatham]

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.