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