Automatically Update a Cell after entering data in that cell

LT

Board Regular
Joined
Mar 23, 2007
Messages
82
The Problem:
I process several parts a day (up to hundreds) that each have their an individual unique serial number (sequential).

The Process:
When I begin processing, a new worksheet is created and I input the serial number into the worksheet (we'll say cell A1 for sake of argument). Serial numbers can be compose of up to 7 Alphanumeric and/or numeric characters. Once the data is entered, I click another button to continue processing. The workbook is saved under a new name, and the process is reset to the beginning using the original workbook (which does not have a the worksheet created yet).

The idea:
I would like to set this up so that after the worksheet is created, all I have to do is enter a minimum amount of information in cell A1 (such as the last two digits), and when I hit enter, the cell (A1) will automatically have the complete serial number information, and will begin the next step of processing (so that I don't need to hit another button).

Is this possible, or am I am just another raving lunatic looking for an easy way out?

Example set of Serial Numbers: XX00001 - XX00201
When processing these 200 parts, I would love to be able to only have to type in the final 2 or 3 digits, and processing would continue automatically.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
LT:

Perhaps the following will help you solve your problem:
Take some unused cell, say cell M1, format it as Text, and use it to enter the last one, two or three digits of your sequential serial number.
In cell A1, enter,
="XX00"&IF(LEN(M1)=1,"00",IF(LEN(M1)=2,"0",""))&M1

Do let us know if this meets your need. The formula above is good for serial numbers ending in 001 through 999 only.
 

LT

Board Regular
Joined
Mar 23, 2007
Messages
82
I looked further into my code and realized that the worksheet that I am trying to modify is created in a third party (password protected) macro. I have really good reason to believe I will not be allowed to have that password.
I was thinking of attaching code to the worksheet itself in order to modify the cell and execute the next sub, but without being able to modify the third party software, that is not an option.

So here is what I have done instead. When I run the first macro and it selects the correct cell and sets it to text only. Then when I run the next macro to complete processing, it executes the following code (which I modify as needed).

Code:
Dim appendr As String
Range ("A1").Select
' set to text
appendr = ActiveCell.Value
appendr = "X0000" & appendr
ActiveCell.Value = appendr
{continue processing}

I pull up the code and modify the "X0000" value as needed. For example, I change it to "X0001" when I reach part 100 and so on. I have done this long enough that I am now heading toward 10,000 parts. I was hoping there was some way I could eventually set up a variable that would be input by a user that would take the place of "X0000", but I know the other users well enough to know that I should continue to have them type in the full serial number. As much as I would like to make their lives easier, some things just aren't that realistic. If I change anything, I have to make it very simple and very clear or something in the process will get really messed up and then I'll have a few hours of work to do just to correct what the other users have done.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top