• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Looking for a less klugey to maintain & update PC Asset Numbers|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Looking for a less klugey to maintain & update PC Asset Numbers|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Looking for a less klugey to mainta…
sp_PrintTopic sp_TopicIcon
Looking for a less klugey to maintain & update PC Asset Numbers
Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 33
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
1
August 29, 2019 - 3:17 am
sp_Permalink sp_Print
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
sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
August 30, 2019 - 2:55 pm
sp_Permalink sp_Print

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.

sp_AnswersTopicAnswer
Answers Post
Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 33
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
3
September 6, 2019 - 12:40 am
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Shanna Henseler
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27237

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.