Excel Forum

Looking for a less klugey to maintain & update PC Asset Numbers

Scotty81

Member

Members

Forum Posts: 45

Member Since:
April 25, 2017

Offline

August 29, 2019 - 3:17 am

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

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

August 30, 2019 - 2:55 pm

Hi Scotty,

You cannot use formulas in this situation, you will have to control what happens only with macros. Use Worksheet_Change event to detect changes in column A, and calculate column B within macro only if it's empty. If column B is not empty, display an input box with the existing value and ask user to manually edit the existing value in the inputbox.

You know best your work flow, so you have to code to achieve your desired outcome.

You can also use the right click event to associate some actions, like auto recalculate the Asset codes.

Answers Post

Scotty81

Member

Members

Forum Posts: 45

Member Since:
April 25, 2017

Offline

September 6, 2019 - 12:40 am

Hi Catalin,

I eventually figured out a solution to my issue without having to "remember" a prior value in a cell. But along the way, I came across a nifty solution that did use the Worksheet_Change event. When it detected a change, it would first set a variable equal to the current value of the cell. Then, it would undo the latest change and record that cells (prior value). Then, it would paste the new value back in the cell, but one would still have the prior value to reference in code.

Thanks,

Scotty81