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.

Making Banded Rows Dynamic

In the last post we used conditional formatting to fill alternating rows in a range of rows.  However, there may be occasions when you want to use banded rows but only if the rows contain data.  This can be accomplished by changing the formula previously used.


For Excel 2007 select a cell in the range previously used and then from the Home tab select Conditional Formatting > Manage Rules > Edit Rule and change the formula to =AND(MOD(ROW(),2),COUNTA($A1:$E1)).

Click OK and the Apply


For earlier versions of Excel click Format > Conditional Formatting and change the formula as above

Click OK


Now the cells in your selected range are filled only when an entry is made in any cell in the odd numbered rows.

As always if you have any questions or comments you can enter them as a comment here on the blog or email me at the office.

Banded Rows

Occasionally there is a need when formatting a worksheet to use banded rows (i.e. cells in the odd rows color filled and the cells in the even rows not filled).  If the worksheet has only a few rows you can simply select the cells in the appropriate rows and format them with the desired color.  However, that's not practical when the sheet contains dozens or hundreds of rows.  When you have too many rows to do manually you can use conditional formatting.


Select the range of cells to which the formatting is to be applied.  For this example I've used the range A1:E10.

For Excel 2007 from the Home tab select Conditional Formatting > New Rule > "Use a formula to determine which cells to format".  Type the following formula in the "Format values where this formula is true" box:


=MOD(row(),2)


Click the Format button and select the Fill tab and then select the fill color.

Click OK

Click OK and your worksheet range now has banded rows.

In the future we'll look at how to make the rows dynamic so when you add data to rows after row 10 the conditional formatting will expand to the newly populated rows.


As always if you have any comments or questions, please comment here or email me at the office.