Software

Ask something
Discussion Locked

Automating Data-Entry in Excel

0 helpful
13 replies

by stewart54 3 views

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

It is realized that the following blurb is kind of long-winded, and it is also realized that this topic was probably already addressed elsewhere; but it is asked to please bear with me.
In not knowing what to ask, the following is presented.

In an attempt to automate the Data-Entry process for hourly employees using Excel, and for the purpose of making data input and manipulation a task that even a $9.00 per hour secretary can understand, an Excel workbook consisting of four spreadsheets was created and are depicted here.

Please refer to the scenario below which will be used as reference to questions that will follow:

To begin the process, a mock Leave and Earnings Statement (LES) was created with all pertinent information that will be viewed / printed when a record is recalled. Two records were manually entered to test the formulas and functionality of this form and the following scenario is given:

1. The first record began as Record# 1001 starting in cell A4 in a spreadsheet called "Input". The data to input involves 19 cells occupying B4:T4. Cell-A4 is the Record# and is not intended for manual input as in its current form, is a formula.

2. CELL-A3 of this sheet contains an initial value of 1000. It is used to increment the First record number.

3. The Formula in Cell-A4 is used to test for record limit of say 1050 (50 records), incrementing the initial count by 1 if < limit; it is now 1001 for the First New record#.

4. The appropriate data was entered within the cells beginning at B4 and continued on to Cell T4. The formulas associated with this record occupy Cells U4-AA4.

5. Once this Record was created, the entire row the data occupied was copied (including the Record # and the formulas.)

6. On the spreadsheet called "Records", the next empty cell for pasting the record was Manually-found at Cell A3

7. The record was then "Pasted as Values" to the empty row at this cell.

8. Going back to the spreadsheet called "Input", the record # (1001) in Cell A4 was copied to Cell A3 as a "Value" increasing it by 1 and thereby incrementing Cell A4 by 1 for the next record# or 1002. (1001 is the new Pasted value in Cell A3)

9. Steps-4 through 8 were repeated with the appropriate results and indications.

Along with the need for automating this process, it is also considered in using a separate area of the "Input" sheet (or another sheet) for some kind of Input form when entering the data. The built-in "Form" function in Excel requires manual manipulation of selecting a range; this is not desirable. It works well but still requires some kind of user intervention and manipulation of the data.

Alternately, the LES form can be used for data input but currently has several lookup formulas in the form for retrieval of a pre-existing record. In any case, the problem of having Excel Automatically find the next empty slot to copy or paste a record to is one issue; and thus-far, have not been able to resolve.

The question I have is this:
How can the above process be automated?

The idea is to use this formula to invoke a series of commands occupying another cell to select all 50 records in the "Records" form then use the cut command. Following this operation, the cut data is then directed to another sheet called "Posted" Here, a conditional test is made by checking Cell-A1 for a value. Of course, there is none because it will be the first posting.

Cell A3 is then checked for content; if blank, the paste operation begins here. Once pasted, another command directs the cursor to the last record, say; with an end-down command, then copies this cell to Cell A1 as a record counter; in this case, 1050. For the next Posting, the cut records are first directed to cell A1 to check for records already posted.

If < 5000 say, it would go to Cell A3 then an end-down > down commands are invoked directing the content to be pasted to the bottom of the previous row; "clearing" the "Records" sheet for the next 50 records.

In LOTUS, this was simple by using END-DN, END-RT, CUT, PASTE, HOME, etc when building these commands into macros, but Excel took all the fun out of this.

For clarification, a file attachment can be emailed to a respondent's email address if requested; the attachment will be an XLS, a ZIP archive, or an executable EXE; just indicate your preference.

Any feedback on this is greatly appreciated.

Regards; Bill

13 replies

by absolutely

I do have a suggestion, based on your use of the Magic Words: "lookup formulas". Whenever I see a large number of vlookup functions used I know Excel is no longer the right program for the work its user is doing. I've never gotten friendly with Lotus Notes, so what you described there didn't help me, but it's pretty clear to me that the first thing you need to do is copy the value pairs in all your lookup functions into new workbooks. One file per lookup pair. Save each as a .csv file (or tab delimited .txt if you like), either of which will only save the first (or active? I'm not sure) worksheet. These plain text files are much simpler to import into Access than multiple-sheet workbooks, and DON'T EVEN THINK ABOUT TRYING TO IMPORT YOUR FUNCTIONS & MACROS.

I apologize for the shouting, but you really can waste a lot of time trying to make Access work like Excel. It works much differently, and once you get used to it, it's much better for large quantities of data, & data with even moderately complex relationships. Also, you can always use a select statement to retrieve a spreadsheet worth of data, if you need it or just start feeling nostalgic for Excel.

by stewart54

First and foremost, I wish to thank you for taking the time to read my lengthy spiel. I know it cost at least a coffee-break to do it.

Actually, it is more in trying to make Excel behave like Lotus. I'm starting on a smaller scale with Excel primarily due to some familiarity with what it is used for, and with a simple goal.

Since it pretty-much replaced Lotus (for reasons yet unknown to me), it was chosen as it is currently the only app. available to me for the intended purpose (Input data, have it resolved, then store / list the resolved data as a record).

The key is, having the program manipulate the "Movement" of the data.

Multiple sheets were depicted in the previous scenario to show key areas; Data Input, Records completed (subject to editing), Records Posted, etc. All data can be on one sheet.

I suppose that due in part to the lengthy blurb of the last post, I problably failed to "Cut-to-the-Chase"

How can "Programmed Movement" of the data be accomplished with Excel, or can it?

Thank you again for your time
Bill

by absolutely

I'll be posting follow-up questions, to be sure you're saying what I think you're saying, but the first "opportunity" I see to automate some work is the copying of data from worksheet "Input" to "Record". For cells on the same worksheet, you can "automate copy & paste" with the function '=cell(##)'. For data on a different worksheet (must be the same workbook, though!), the function is '=worksheet!cell(##)'.

Also, you asked about an "end-down command". The key combination for that is Ctrl + Down Arrow, but be warned: it only works for [u]contiguous[/u] non-empty cells. If you have three populated cells, one blank and 900 more populated cells in a column, then starting at the top of that column, the first time you press Ctrl+Down will only take you to the bottom of the group of 3 cells. Try it out.

Now, follow-up question 1:
In your item 8, you refer to the contents of cell A4 as "now a formula", implying a change. But, in item 3 it is also described as a formula. So, what changed? Or did anything change? Maybe I just misunderstood the implication in 8. If there was a change in A4 between steps 3 & 8, what was it?

by stewart54

Greetings: and thank you for your reply.
Sorry again for being so wordy.

For the sake of simplicity and to illustrate where the entered data is to end up for calculations' "Following" its input, the data was entered in a linear fashion as depicted in the previous post. This was done to test the formulas and accuracy of their results. The actual Input form has since been created but not yet functional and the two rows of data previously copied to the sheet called "Records" have since been moved to A101 and A102 of the same sheet the input occurs. All other sheets in fact, have been subsequently deleted; now working with one sheet. This was done in an attempt to further simplify what is to follow in the build of this program.

You are correct in the assumption that Automation begins with the copying of data from one sheet to the next; but this is only part of my perplexing puzzle. It actually begins (or should) at program start-up.

In regards to the Record Number at cell A4, the record# (along with the rest of the record) was copied, then pasted as Values elsewhere; leaving the integrity of Cell A4 intact and remains as a formula. It was later copied again and pasted as a Value to Cell A3 this time, causing cell A4 to increment to the next record#. This method may be modified later. The intent was to illustrate that the record number is a formula as-copied in its initial form and was not touched during its input. Sorry for the confusion. In the depicted scenario, it was attempted in incrementing the record number using this method and is the reason this formula remained a formula.

In the "modified to one-sheet version" I am now toying with, the Record number is now a MAX function in a protected cell of the new input form. Why MAX? Simply put, if a record is deleted, and it happens to be the last one entered, a simple incremented indication would probably be wrong. The idea is to Not go below the MAX value already entered thus causing duplication later on; using the MAX function will hopefully prevent this. This formula (or function) is now copied / pasted as a Value to Cell-A4, along with the data from the input form to their respective cells following it. This occurs when the Input sequence is completed and the user is ready for the next input cycle by invoking a hot-key sequence or clicking a button.

The Record range is A4:T4; this includes the Record Number that is now a value. The data just pasted to this row (like all-other records to follow) will be acted upon for calculation with pre-existing formulas beginning at Cell-U4 on the same row. The formula range is protected to prevent accidental over-write. Following calculations of the record, the entire row the record occupies (including the formulas at U4:AA4) are copied then pasted as Values to a record stack elsewhere in the sheet; hence the END-DN, END-RT, etc. Of course, a conditional statement can be used to test for the next empty cell before invoking the "Past as Values" sequence.

The goal is to be able to invoke commands with either a hot-key combo or programming a few buttons for functions like "Add New Record", "Delete Record", "Edit a Record", "End Input and Post", and "Quit". These functions would include a series of formulas, commands, and/or other necessities to manipulate the data under program control once data is entered. They would be used from within the Data Input form, as currently proposed. This is my weak area in Excel; telling it what to do, and how to do it. I can do all of these functions manually, but to allow someone else at the keyboard or have someone not knowledgeable in this can be a mistake. I realize there are applications out there that does this, but these are all proprietary software, expensive, and some of it is just plain iffy. I am also on a continual learning curve with the Programs I already have and thus far, I barely scratched the surface of Excel.

I used Lotus for quite a while and it sort of grew on me. I kind of miss it, actually. With this comes the pre-existing knowledge of END-DN, END-RT, etc. Of these functions and more, I am well-aware.

Now I have to use Excel and the Lotus notes in the older version of Excel only went so far. Currently, I am using Excel that shipped with the 2003 suite.

I hope this clarified a few things and Thank You once Again.

Kindest regards;
Bill

by stewart54

UPDATE:

In an attempt to illustrate the authors clouded chain-of-thought and to clarify the previous post entitled "Automating Data-Entry in Excel", the following update is provided:

The previous submission was intended as a general idea of what the author is trying to do. It began with an idea that eventually spawned to that submission.

The program had no menus or input forms per se; the data was inputted to their respective cells by selection beginning at a known location; Cell-B4 (A4 as you recall, was the record# formula).

Once the calculations were performed to this data, (using the pre-existing formulas occupying U4:AA4) the entire row the data occupied was manually copied and pasted as values to a records stack.

Now that records exist, and now knowing the formulas are correct, the next stage was to streamline and simplify the program-in-build by moving all data previously occupying the other sheets to the Input sheet; the Input sheet is now the only sheet in use as all-other sheets have since been deleted.

The current (revised) build is an attempt to Now-automate the data-manipulation process by adding forms and "Buttons". The "MENU" and "Add a Record" forms, along with its associated "Record Input" form have since been added but they lack the needed programming for the automation process. In addition to these, another Menu button was added to the MENU entitled "EXIT Excel". This button would perform an "Auto-Save", then Exit; again, not programmed.

It is in this area (programming these functions) that help is needed, and is hoped it can be offered.

The scenario would go something like this;
? When the user starts the spreadsheet with Excel, the program would "Auto-Start" taking the user to the "MENU" screen. Here, the user would have these options; "Add a Record", "Edit a Record", "Delete a Record", and "Exit"; the options are in the form of Buttons.
? Since only the "MENU" and "Add a Record" forms are currently proposed at this time, (neither of which are programmed) the user has two choices; Add a Record or EXIT:
? The user chooses the "Add a Record" button and the program goes to the Newly-Created "Input Form" starting at AA1. This form has the Next incremented Record# in Cell-AC3 and the data input cells at AC4:AC23. The Main controls are; "MENU" and "End Input & Post" buttons
? The user inputs the data in this Form then signals completion of Input by clicking the "End Input & Post" button. This action would invoke the "COPY" command of Cells AC3:AC23 (the Record# formula in AC3 and the inputted-data)
? The data is then pasted using "Transpose" and the "Values and Number Formats" switches to Cell-A4. The pasted range is A4:T4.
? After the pasted data is resolved using the pre-existing formulas in the same row starting at U4, the entire row (including the formulas) is then copied and pasted as values to the next available row in the "Records" stack.
? Following these "Programmed" events, the program returns the user to the "Add a Record" form where the options are to add another record or "MENU".

The other "MENU" items would work in a similar fashion using their own forms and buttons programmed for the specific tasks.

Again, it is hoped that some assistance can be provided in this quest. The help areas are confusing-enough when it is known "What to ask." However, when "What to ask" it is not-known, (as is the case depicted in these writings) this makes finding automated-answers through search engines and other sources, impossible.

Thank you for your time
Bill

END-UPDATE

by stewart54

It is attempted in sharing what is learned in hopes of bolstering this knowledgeable community.

When automating Excel functions using buttons; along with Sub-routines that are programmed to work when a button is used, a "Call" function can be invoked from within the buttons code. (Of course, you all know that; but some of us may not.) The code below depicts the "End Input & Post" button a user invokes by simply clicking on it. This would be done to signal the input cycle as complete and the program is to take the data for processing and storage. Following this, the program returns to the same screen for further input, or the user can take some other action. All of this occurs in a time frame of less than 1-second once the button is used.

It is realized this code can be simplified (and probably will be later on)

In consideration to this, the following is observed;

'Begins Sub-routine; Ends Input: Posts record to next-available row as completed record
Private Sub CommandButton3_Click()
Worksheets("les").Activate

'This group selects the users input (some from lists) Since 1 cell contains a formula, the range is copied.
Range("AC3:AC24").Select
Range("AC3:AC24").Copy

'This expression pastes the copied range as Transposed and values to a specific target. Once pasted, it is manipulated to combine some cells.
Range("A94").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=False, Transpose:=True

'Row 95 consists of the resolved data pasted in row 94. Since the modified range contains formulas and will be pasted as Values, the range is copied
Range("A95:T95").Copy

'This expression pastes the copied range as Values for formula manipulation now. Formulas adjacent to the pasted range are used for this purpose.
Range("A96").PasteSpecial xlPasteValuesAndNumberFormats

'The resolved data is then copied in its entirety. Note the extended range in row 96 to include the formulas used.
Range("A96:AD96").Copy

'This expression takes the range copied from row 96 to the beginning of the records stack (contiguous cells below it) then "Pastes as Values"
Range("A199").PasteSpecial xlPasteValuesAndNumberFormats

'Since the pasted row is "Values" with no further calculations now, the "Cut" command is used.
Range("A199:AD199").Cut

'The next Record entry has to know where to be pasted to. Now a "Call" is invoked to test for the next available row for the data to be pasted
Call Sheet1.check_for_empty_Cell_

'The sub-routine finds the next empty cell and ends.
'The call-from sub-routine is resumed.
'Since "Paste" is to follow as resumed from the "Call", it is invoked.
Paste

'Once-pasted as a new record, the following group takes the user back to the Input Form then homes the cursor to the first input cell waiting for next input
Range("AF1").Select
Range("AC4").Select
End Sub

'Sub-routine testing at bottom of loop finds next empty cell in column (A or 1)
Sub check_for_empty_Cell_()
If IsEmpty(ActiveCell) Then Exit Sub
Do
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
End Sub

There is one significant drawback to what was accomplished here; the code eats a lot of memory and space when using this method. It took a file-size of around 300k and it became a file-size of over 6meg. This is a 20-fold increase of 2,000%.

Since the author's familiarity is weak in programming with VB, VBA or whatever you wish to call it, improper coding techniques is assumed as probable cause for this, but not sure.

Although the above code was scripted by the author, some of it was based on ideas from other users of Excel and VB. And since these authors encouraged to share with others, this author encourages the same.

If there are those who wish to offer improvements, comments or other usable feedback to this, feel free to do so.

After all,what is a Forum for; if not for sharing ideas?

by absolutely

Instead of comparing to the previous file size, I'd compare the size of your new file -- which now includes a VBA macro/VB program -- to the size of the Excel program. My reasoning is that what you have added is programming, not data, therefore you should compare your 6MB to the size occupied by your previous programming, not to the size of your previous data. Like Excel, the new VBA code can be used repeatedly, on multiple data files. After using your new & improved VB program to process your data faster, you can always copy that data to a delimited text file, and see that each data file remains small. Even if every user in your office installs that same macro, that's a one-time (per user) cost of 6MB, in exchange for ongoing productivity increase, for each user. My guess is that the real business cost of what you're doing is mostly the time invested in writing a new program, not the 6MB of (friggin cheap, these days!) disk space.

by stewart54

Thank you for taking the time for this and your valued input; it is greatly appreciated and an idea I will try.

In not knowing what to ask; I can sympathize with those trying to answer when one does not know the question. It is difficult indeed; but sometimes I just don't what to specifically ask.

It isn't the disk space as being so much the issue; this was only an observation of how a little bit of code can take so much extra space, but not a concern. It may have been poor coding methods by me, but not sure. This is why it was mentioned.

I am currently learning basic programming techniques in Excel using its built-in VB editor and the stumbling block at this point is coming up with a subroutine that will search a column of unique numbers for a match based on a number in another cell (the search-criteria).

The code will be written so that when a button is pressed, it executes the subroutine.

If the searched number is 2001 for example, the subroutine will go down the column until it finds 2001 and once found, the subroutine exits.

I am thinking a conditional statement using a loop expression; but I am not sure.

For limits, the minimum would be 1001 and 5000 for maximum. It should have some limitations so that it doesn't go out of bounds.

Any ideas on this is appreciated.

by stewart54

Thanks is given once-again to all those who participated and offered valuable feedback. Your input was indeed, valued.

This Post was edited to clarify the questions posed to this forum.

In the "Add a Record" phase, it was originally decided to combine a few cells before the record was posted; this would be fine if no changes were to be done. What if an employee changes his/her filing status or name? This happens frequently when people get married, divorced or widowed. (Incidentally, the cells that were combined before were the name fields) This has since been revised.

If cells needed combining for any reason, this can be better done using a form for the sole-purpose of viewing or printing a record. This was already done in the Leave and Earnings Statement or LES by populating the fields with LookUp expressions. In fact, this was the first thing accomplished in the build of this project. Here, the name fields and a few of the address fields (City, St, Zip) were combined to provide a more esthetic lay-out and display.

You are probably asking yourself what is the point to this?
Quite simply, no matter how far ahead we try to think, or how brilliant we think we are, no-one is free from making mistakes. This is also to exemplify the need for thorough testing During the Build of Any Project.

After Record Input, something was found in need of changing; the "Edit a Record" button comes in to play. But before it does, it needs programming. One needs to ask what specific steps need to be taken and for what purpose. The record will have to be retrieved from the records stack. In order to do this, a unique search criteria will have to be defined and a way to implement it.

In considering that the only thing unique to all records is the record number, it will be used as the criteria for search using an input form or a drop-down list box. Since this old-DOS-hacker has gotten accustomed to "just point and click" methods, a Drop-down list box will be used here. It will be pre-populated using Validation of all record numbers in the range of say A501 to A1500 or a total of 1000 records. Any un-populated cells will simply be blank and if selected, no data is retrieved so no harm done here.

Now the questions posed to the Forum:
1. When a valid record number is entered or selected, how can a routine be scribed to actually retrieve the record to the Edit form based on the content of the Drop-down List where the Record# is entered?

2. What methods can be used to find a record match in the Records Stack to re-post the Edited Record; over-writing the existing record in the stack?

Any assistance or ideas are greatly appreciated.

Bill

by stewart54

In response to Question-1 of previous post, a possible solution was tentatively found. Queation 2 however, remains un-resolved.

Look-Up formulas were tried as a possible solution for populating the "Edit Record" form and the following is observed:

After entering the desired record # from a Drop-Down list (in cell AC3), another button now has to be used (Find Record). When clicked, it populates the record with Look-Up formulas based on the record#. These formulas were copied from elsewhere in the sheet then pasted to the appropriate cells, AC4:AC24. This range was then copied and pasted as values in place allowing for normal editing of the record. (It has the same feel as the Input form along with the drop-down lists)

Although this method works, there are drawbacks in using it.
? Error detection of an in-valid Record# is accomplished by de-selecting the drop-down of the Record# list and therefore a valid number has to be entered, it does not Automatically populate the record with the correct data until another button is used (Find Record) to get it.
? If the "Get Record" button is not used following input of a new number, any existing record may be mistakenly keyed with the alternate record#. This of course can be misconstrued as the correct record associated with this now-bogus-number.
? Upon selection of, or returning to the Edit form from a previous edit, an expression can be used to select the Edit range (BC4:BC24) and invoke Clear Contents; but this will only work under program control. If manually scrolled to, errors are now more prominent.

The following questions are now posed:
1. A good start might be an alternate method of capturing a pre-existing record to display in the Edit form instead of using lookup formulas
How can an expression be used to display the record when a valid record# is keyed-in then entered?.
2. Once an edited record is ready to go back in the record stack, how can a routine be written to find the record Match using a "Loop" or similar Expression?

The idea is to Replace the existing record with the edited record by testing for a match in ac3 (record# of edit form) to the record# in the stack beginning at A501. When a match is found, the selected range in row96 is now pasted; replacing the pre-existing record.

Again, any help in these will be greatly appreciated.
Bill

by absolutely

You seem to have spent several hours
researching this problem by now. I'd like
recommend that you honestly account for all
the time you've invested already, starting
from the moment you decided to go with VBA,
and spend no less than 10% as much time
trying the "Import External Data" option on
the "Data" menu, which I hope is present
even though you don't have MS Access.
Assuming that feature is available to you,
it provides some rudimentary database
functionality, which might be useful in this
project, or your next one. Either way,
learning the ODBC capabilities built into
Excel will help you solve this type of
problem faster in the future.

by absolutely

I like the VBA help files in Excel. I think
they make finding the needed definitions
about as easy as it possibly could be. But,
they still take time, and you're the one
being paid for this project.

Cheers!

by stewart54

WIll do for future reference and for a worthier cause.

Trying to resolve a simple problem like having some code scan a list of numbers until a match is found, was the basic need. Once this is accomplished, the rest of it is a no-brainer. Something like a Do While ActiveCell.Value <> "" for finding the next empty cell was easy-enough to figure out; but using something similar to find a record matching a search value in another cell? I'll keep plugging on this when time permits.

Thus far, I have been running this thing manually so that things keep moving. Allowing other hands do this would only corrupt the data; but this takes from my other duties.

One can only idiot-proof so much when it is time to hand-over the reigns, especially for one who is less learned in things in general; like my supervisor.

Until I can crack this nut, I'll have to continue doing the work for others.

Thanks again

This discussion is closed to new replies.