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?