Hi,
I have a spreadsheet where we maintain PC asset numbers for machines we buy. Others have been entering asset number ranges in this file manually, but that can lead to errors. I've some up with a solution that automatically generates asset number ranges, but it's a little klugey. Since the file is used for other purposes, we need to keep the solution in Excel rather than in a formal database or commercial package. My issue is not automatically generating asset numbers (which I have done, not shown), but in maintaining the numbers when the number of PCs I'm ordering changes.
What follows will make sense after opening the attachment.
In Table 1, I have a column for the # of PCs I've ordered, and I also have a 2nd column that generates the range of asset numbers I need to assign. The columns to calculate this aren't shown, but if I order 5 PCs, then my asset number range is automatically calculated to be: '1-5, assuming I'm starting from "1". I also have helper columns (not shown) that look at the asset numbers used so far in prior orders. In that way, if I order another 5 PCs, the asset number range for those will be calculated to be '6-10. All good so far.
But, sometimes after folks enter some rows in the table, they need to go back in and alter the # of PCs ordered. This is shown in Table 2. For example, they might need to change the # of PCs from 5 to 3. Then, the asset number range for those PCs will become '1-3. But, with my simple example, the asset numbers of the other row entries changes also, which they don't want to happen.
I've come up with a solution, which is shown in Table 3. I still have the column that automatically calculated asset numbers, but I also have an override column, and then a final asset number column. The final asset column takes input from the override column, or if the override column is empty, from the calculated column. My thinking is that when they close the XLS file, I can have a macro run to copy and paste (as values) the info from the calculated column into the override column (as long as the override column is empty). This effectively locks in the values from the initial entry in the override column. The result of what this would look like is shown in Table 4.
Then, on the next day when they make a change in the 1st row from 5 to 3 PCs they want to order, they can then also manually update the override column to '1-3. The asset ranges in the other rows will remain fixed since they are reading from the frozen (override) column. This is a rare occurrence, but it does happen so I have to consider this use case in my design. I do have other columns that let the user know of the next available asset # and whether the user has inadvertently entered any duplicates (also not shown). This example is shown in Table 5.
What I don't like about the above solution is that it takes 3 columns (calculated, override and final) of asset numbers vs. the original single column when they were updating the file manually. I can put the calculated column off to the side to free up some real estate though. I have no problem running an Auto Close macro, but I consider it a little klugey to "lock in" the values of the day so they don't get overwritten. Yet, it's the only way I know for Excel to remember what was originally entered. Finally, if a user changes the # of PCs ordered from 5 to 3, they also have to remember to manually overwrite the range in the override column.
Does anyone have any suggestions to improve upon the above design?
Thanks,
Scotty81
Posted : 29/08/2019 1:17 pm
