Import JE Data into MyHD

One of the great new features in MyHD is the ability to easily import Journal Entry Data.  The user enters the G/L Account Number, Amount and Description in Excel and then saves as a .CSV file.  


The user simply navigates to the Journal Entry screen and enters the Journal Reference code and Reference Number then clicks OK.

The user now clicks the Import button

Selects the file to be imported

Click Open and the data is loaded to the JE

To make this process even simpler we've created an Excel workbook specifically for the data entry

This workbook has MS query that runs against the customers database and validates the G/L Accounts plus a formula to indicate that the data is in balance.  When the data entry is completed the user clicks the "Crt CSV" button and the data is copied to a new workbook which is then saved on the users C drive.

We also have a workbook for loading JE Data to the database for customers that are still using HD.  Since there is no Import feature in HD the data from the workbook is loaded to the database via Client Access.

If you would like a copy of either of these workbook send me an email and I'll forward it to you.

As always if you have any questions or comments you may post them here or email me directly.

Date Selections for HD Parameter Queries

I receive frequent requests for reports that require the user to provide a from and to date to the MS Query.  Ideally you would like to prevent the user from receiving an error message because a to date that is less than the from date is entered.  There are several way of accomplishing this and here is the one that I prefer.


Here's the layout of our sample workbook.

The todate list (column B) is built by entering =TODAY() in cell B13 and then entering =EOMONTH(B13,-1) in cell B12.  The formula in B12 is then copied and pasted in range B2:B11.  This populates the range B2:B12 with the last day of each month for the  previous 11 months.

The fromdate list (column A) is built by entering =DATE(YEAR(B2),MONTH(B2),1) in cell A2.  The formula is then copied  and pasted in the range B3:B13.  This populates the range A2:A13 with the first day of each month corresponding to the date in column B.

The Named Range "fromdate" is created by selecting the range A1:A13 and selecting  Formulas >  Create from Selection and clicking OK.

Select cell I2 and then Data > Data Validation and from the dropdown select List.  In the Source box enter =fromdate and click OK.  Cell I2 now has a drop down from which the user can select a date.  Format the cell with a date format.

Since the user should not be able to select a todate that is less than the fromdate it's necessary to determine where the todate list should begin based on the users selction for fromdate.  Enter =MATCH($I$2,$A$2:$A$13,0)-1 in cell E2.  The arguments for the MATCH formula are lookup_value, lookup_array, match_type.  The formula looks for the selected From Date (I2) in the list of From Dates (A2:A13) for an exact match (0) and returns the relative position of the cell where the match is found.  The -1 is used because the offset formual that will be used for the name range begins in row 2 instead of row 1. 

In cell E3 enter the formula =12-E2.  This will be used in the offset formula to determine how many cells to display. 

Select cell B2 then Formulas > Define Name and in the Name box enter todate.  In the Refers to: box enter =OFFSET(Sheet1!$B$2,Sheet1!$E$2,0,Sheet1!$E$3,1) then click OK.  The arguments for OFFSET formula are reference, rows, cols, height, width.  The reference is the cell address from which the offset will occur (B2).  Rows is the number of rows to offset from the reference cell, in this case it's the value in cell E2.  Columns is the number of columns to offset from the reference cell, in this case the value is 0 since we do not want a column offset.  Heighth  is the number of rows to be returned, in this caase we use the value in cell E3.  Width is the number of columns to be returned, in this case we only have the single column of data so we use 1.

Select cell I3 and then Data > Data Validation and from the dropdown select List.  In the Source box enter =todate and click OK.  Cell I3 now has a drop down from which the user can select a date that is always greater than or equal to the From Date.  Format the cell with a date format.

Cells K2 and K3 contain formulas to convert the dates to a format that can be used as parms for the MS Query .

  =SUM(YEAR(I2)*10000+MONTH(I2)*100+DAY(I2))

As always if you have any comments or questions you can enter them here or email me at the office.  Also, if you would like to have the sample workbook send me an email and I'll send it to you.