Excel Tips for excelwizards

I’ve been hoarding a few neat excel tips over the last few months, so I thought I’d share them on a blog post.

TIP #1: A Tricky LEFT/FIND Combo Formula

Have you ever wanted to get everything to the left of a specific character (ex: dash, or colon) when the number of characters is not consistent? If you use Text to Columns, sometimes what’s left gets a funky formatting and then you’re data is incorrect. So here’s a neat formula to solve for that:

Assume I have the following in Cells A1 and A2:

LEFT Formula (A1)

In Cell, B1 type the following formula:

LEFT Formula (Formula).jpg

Cool excel wizardry at it’s finest.

TIP #2: A Sneaky Way to Add an Empty Row After Each Row of Data

Imports usually read a row at a time. Unfortunately, that means you need the first row of a journal entry, followed by the second in rows 1 and 2 of your Excel workbook. What if the export file has the debit and credit in the same row? Or just 1 single column for amount and you need to add the second row?

Sure, you could insert a row after each line of a 1000 row file, but do you really hate yourself that much? I like you, and your time, so I’ll share my magic with you 😉

Imagine you have the following file:

Insert Blank Line (File)

Step 1: In column A, number your rows consecutively. (Use the formula highlighted to help you do it).

Insert Blank Line (Number Rows)

Step 2: Copy the entirety of Column A and paste it also in Column A, below your last row. (When you copy and paste, if you used the formula, make sure you right click cell A7 and choose the clipboard with 123 to paste the values and not the formula).

Insert Blank Line (Copy Numbers).jpg

Step 3: Sort on Column A from smallest to largest.

Insert Blank Line (Sort).jpg

Step 4: Go get yourself a coffee and tell people you’re really busy formatting an Excel file to add empty rows between each row of data 😉

Insert Blank Line (Final).jpg

Helpful Tip: Copy anything that will be the same in row 2 (the blank row) when you copy the numbers, before sorting, save yourself any extra formatting. In my example, I might want to copy the amounts into the DEBIT column for all the blank rows I’m creating.

TIP #3: Auto-Number lines of a Journal Entry/Transaction File Using IF

I can’t count the number of times I’ve used this formula recently. While it’s incredibly simple, it’s a nice little time-saver.

Let’s say you have the following non-numbered Journal Entry:

Line Numbering (No Formula).jpg

You would like to start at 1 in cell D3, and number consecutively until you reach D8, where you’d then like the numbering to restart at 1 again.

Put the number 1 in cell D3, then use this formula for all cells in the column:

Basically, the IF formula checks the date of the current row to the date above it, if they match it takes the cell above it and adds 1. If the date is not the same, it assigns the number 1 to it.

Line Numbering (Formula).jpg

**Use the two tips above in combination (with a little removal of duplicates on the second step of copying and pasting the numbers from Tip #2) to add an empty row after each group (journal entry) to really blow someone’s mind. Or better yet, don’t tell anyone, let them marvel at how fast you are and give yourself an extra few minutes for some coffee.


Creating an HTML table using jQuery

Last month I battled how to create an HTML table using jQuery. It sounded simple and definitely possible to me but it took some time to get it to work. Since it took some time I wanted to write it out to keep notes for myself in the future and help others who might also be trying to do the same thing.

I wanted to use jQuery so that I could create a nice clean table with 50 rows without having to actually write out an HTML table with 50 rows in it (that would be painful and I would probably hate for anyone to actually look at it and see that’s how I got the 50 row table built in the first place). So jQuery was a logical solution. I knew I could use an array to loop through and create 50 rows but I had never done it before.

My table at first without jQuery:


And yep that’s just 3 rows … yikes!

My table using jQuery:


My 50 row table is shorter than the original 3 row table. So cool!

And here’s what the table looks like:



jQuery to Autofill Dates and Increment Annually

My idea of Friday afternoon fun was learning some date functions! In my last post I went over how to take the value of the total amount entered in a field and split it into even installments of related fields based on the number of years in another field and a button push. (Ex: Enter $300,000 in field A, enter 3 years into field B, then push a button, update field C, D, and E with $100,000 each). That was pretty neat! I learned .val and how to use arrays.

Now, Friday my goal was to figure out how to auto-fill 3 more fields with dates based on the start date and number of years fields. For example, a user enters a start date of 1/1/2017 and 3 years… I then wanted the three date fields next to the amounts to auto-fill with 1/1/2017, 1/1/2018, and 1/1/2019. Easy, right? Not quite. When I first tried I didn’t realize that simply adding “1” would basically result in 1/1/20171, 1/1/20182, 1/1/20193. Not exactly what I want. Because I wasn’t treating my field as a date, I was just adding 1 as a string to the end of my value.

Here’s what I ended up with:

Screen Shot 2017-05-08 at 7.14.16 PM.png

A couple things I had to learn:

1.Declaring my date field variable as a Date: var myDate = new Date(input);

I had to set my date field as a value that I could do a calculation on in ms.

2. Doing a calculation on dates: myDate.setFullYear(myDate.getFullYear() + 1);

myDate.setFullYear() sets the year of my date. So I’m saying, take my field “myDate”, then set the Year. What should the Year be? That which is in the parenthesis! Another Date function. Take myDate and get the full year then add 1. So, in essence I am saying I want to set the year of myDate… what do I set it to? myDate’s year +1. Awesome! Now how to throw that into my fields within my array?

3. Well, I actually had my first date be auto-filled to equal the start date. So I can skip field i=0. Then, I only needed the array to auto-fill the other date fields.

So I started my array at 1 rather than 0 like in my last post. So the jQuery will only fill my fields starting with gm_grantapp_ar_invoice_due_date1 … through the number of years value.

4. Learn some jQuery: ($.datepicker.formatDate(‘dd/mm/yy’, myDate));

This statement says the value of the gm_grantapp_ar_invoice_due_date[i] field should be the value of myDate that I calculated in #3 above, then format it.

A final note, if you’re learning these as you read along: here’s a helpful note on syntax of jQuery. It follows the format: $(field).action(value)

  • Meaning … what field (HTML) am I editing is declared first. (after $ which calls the jQuery library).
  • Then, a period.
  • Then, what function/action am I using (getYear, setYear, val()).
  • Finally, what the value of the field should be is in parenthesis.

Easy, right?! Keep tuning in for more fun javascript/jQuery!

On a separate note, I learned how to use Process Builder in SalesForce today and have a practical use-case for it so if you like my mixed bag of topics keep your eyes open for an upcoming post on that!


jQuery to Auto-Fill Fields with HTML Buttons

Have you ever noticed when placing an order on certain sites you can enter your shipping address and then have the option to check a box that says “my billing address is the same as shipping”? Then, like magic, the shipping address fields auto-fill? It’s a cool little efficiency isn’t it? Why type something twice when it’s identical? (Taken for granted a lot but cool non-the-less). Have you ever wondered how to do that?

Today’s post will be a slight detour back to Intacct but in a broader sense. While the example is Intacct-specific to something I needed (mostly wanted because it’s cool) for an app I’m building, the use-case is not and applies to any site where you can add html and jQuery.

So how do you do it?

Continue reading

SalesForce: Formulas

SalesForce formulas have many potential uses. Today’s post will cover one of my favorites:

Deploying SalesForce formulas as a simple mechanism to overcome potential system integration point hiccups without adding any additional data entry for users.

Imagine you would like to integrate SalesForce Opportunities with Intacct Sales Transactions. On the Intacct Sales Transaction, you would like to send from SalesForce to Intacct the Opportunity Owner field. Great! We know exactly what we need to sync.

SalesForce Opportunity Owner Field:


Intacct Employee Field:


Why might we want to do that?

  1. Visibility/Ease of Use: The finance team can simply look at a sales transaction from within Intacct to see who the owner of the opportunity is and reach out to them with questions (or even send a collaborate/chatter message).
  2. Reporting: There is added benefit of reporting. Being that Employee is a dimension in Intacct, you’ll have all the added financial reporting capabilities.

Herein lies the Problem:

The Employee field is on the Intacct Sales Transaction line item, while the Opportunity Owner field in SalesForce is on the SalesForce Opportunity (instead of associated Products). Why is that a problem for us? Well, the standard integration doesn’t allow for syncing fields located on the SalesForce Opportunity to Intacct Sales Transaction line items. In addition, the Employee ID that is passed through needs to match what is set up in Intacct – which is likely not what is set up in SalesForce.

Don’t give up yet! How do you solve these two issues? You probably guessed from the title of this post: Formulas!

Continue reading

excelwizards: SalesForce

It has been a few months almost a year(!) since my last post on excelwizards so I thought a revival post was due. This blog began as a way to share Excel tips with no expectations whatsoever (being there are so many other Excel sites at least this one hopefully kept you awake while teaching you something new). It then evolved into Intacct tips, tricks, neat things, and how-to’s as I went through an implementation first hand and eventually learned Intacct and made my way into consulting.

Continue reading

Smart Event API Calls – Updating Line Items on Sales Transactions

Have you ever wanted Intacct to update a corresponding custom field on a transaction line item based on another field? Have you ever wanted to update a field in a transaction’s line items based on a header level field? Those are two things I learned this weekend.

Problem 1: If you use Intacct’s merging transactions option in Order Entry to merge multiple Sales Orders to one Sales Invoice you might have noticed any custom fields on the Sales Order don’t propagate through to the merged Sales Invoice. This was especially bad news in this case because we had a custom invoice template set up which was grouping items and subtotaling based on that field. It didn’t make sense for the person entering sales orders to then also have to edit the merged sales invoice because Intacct clears those custom fields upon merging.

Solution: A Smart Event API call which looks at the Item’s name to fill out the custom field. The solution took some creative thinking. I noticed the list of items that require this custom field to be filled out either have a name containing: MAINTENANCE – S… or MAINTENANCE – U… So I thought I could write an API call with a condition so that if the Item’s Name starts with “Maintenance – U” the custom field is filled out with Unscheduled and if it starts with “Maintenance – S” the custom field is filled out with Scheduled. Neat, right?

Continue reading