Lauren Lummus

Author's posts

Feb 21

The Quickbooks Consultant’s Reference Guide

 
If you use QuickBooks in any capacity, then you need The QuickBooks Consultant's Reference Guide published by The Sleeter Group. If you offer consulting services related to QuickBooks, be sure to join The Sleeter Group's Consulting Network.

Feb 13

How to Resolve Duplicate Data within Excel Pivot Tables

By David Ringstrom, CPA

 

An attendee from my recent pivot table webinar posed a question that I hadn’t encountered before.
Pamela had an issue where some, but not all, items within her pivot table were being duplicated, with two different totals. If you’re new to pivot tables, you can catch up by watching a free recording of the webinar.

 

Continue reading on www.accountingweb.com

 

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jan 20

Add/Edit Users

Instructions on using the Budget Master Tool to set up users.

 

[S3VIDEO file='Melissa/Set-up_Users.mp4′ width='608′ height='461′]

Jan 20

Add/Edit Functions

Instructions for using the Budget Master Tool to set up functions.

 

[S3VIDEO file='Melissa/Set-up_Functions.mp4′ width='608′ height='462′]

Jan 20

Employee Allocation

Instructions on using the Budget Master Tool to allocate employee salariees.

 

[S3VIDEO file='Melissa/Allocate_Employees.mp4′ width='608′ height='466′]

Jan 20

Budget Request

Instructions for using the Budget Master Tool to create a budget request.

 

[S3VIDEO file='Melissa/Budget_Request.mp4′ width='608′ height='457′]

Jan 20

Budget Reports

Instructions on using the Budget Master Tool to preview and print reports.

 

[S3VIDEO file='Melissa/Reports.mp4′ width='608′ height='456′]

Jan 15

Microsoft Excel Text Extraction Techniques

by David Ringstrom,CPA
 
Reports exported to Excel from QuickBooks often contain data aggregated into a single column that you may want to pare down. I’ve seen any number of folks over the years resort to manually retyping the data they want into a new column. In my experience retyping data is typically only required in special circumstances, as in most cases you can easily transform the data as needed. In this article I’ll share several Microsoft Excel text extraction techniques, including the rarely used InStrRev function.
Continue reading on www.sleeter.com.
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Jan 13

Spreadsheet-Based Form 1040 Available at No Cost for 2013 Tax Year

By David Ringstrom, CPA

Although the IRS is still madly preparing for the 2013 filing season, one man remains ahead of the curve.

Glenn Reeves of Burlington, Kansas, has released his seventeenth spreadsheet-based version of the US Individual Income Tax Return, commonly known as Form 1040. Since 1997, Mr. Reeves has pursued this “labor of love,” which means he allows any taxpayer to download and use the spreadsheet for free.

 

Continue reading on www.accountingweb.com

 

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Dec 12

The Curious Case of Strikethrough in Word and Excel

By David Ringstrom, CPA
I often find myself using the strikethrough feature in both Word and Excel to mark items as completed. This feature is fairly straightforward in Word, as a strikethrough icon appears prominently on the Home tab in Word 2007 and later. Conversely, in Excel this feature doesn't have its own icon, but it does have a keyboard shortcut, Ctrl-5. Yet there's no built-in shortcut for strikethrough in Word. In this article, I'll describe a couple of ways that you can streamline access to this – and pretty much any feature – in both Word and Excel.
As shown in Figure 1, the strikethrough feature is a font setting that allows you to draw a line through text. In Word 2007 and later, you can select a block of text and then toggle strikethrough on or off with a mouse click. You can also access this feature from the Effects section of the Fonts dialog box shown in Figure 1. The traditional way to apply strikethrough in Excel involves carrying out steps A through C by way of the Format Cells dialog box.
Continue reading on www.accountingweb.com

 

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Nov 23

How to Conditionally Display Decimal Places in Excel: Part 2

 
by David Ringstrom,CPA
 
In Part 1 of this series I showed how to use a custom number format to conditionally display decimal places. Although the technique is simple, the downside is it may not work in every situation. For instance, the number formats shown in Part 1 would display 0.75 with two decimal places, but would round 4,200.75 up to 4,201 since 4,200.75 is greater than 1. In this article, I'll describe how to use Excel's Conditional Formatting feature to handle just about every imaginable situation.
 
Excel's Conditional Formatting feature is available on the Home tab of Excel 2007 and later for Windows as well as Excel 2011 for Mac, or the Format menu of Excel 2003 and earlier. You can establish up to 64 levels of Conditional Formatting in Excel 2007 and later, or 3 levels in Excel 2003 and earlier.
 

 

Related article:

A previous version of this article first appeared on www.accountingweb.com .
 
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

Nov 13

How to Conditionally Display Decimal Places in Excel: Part 1

 
by David Ringstrom,CPA
 
 
The simple task of displaying decimal places sometimes causes angst for spreadsheet users. If you have a list of both large and small numbers, there's tension between rounding the small numbers to whole values and making the large numbers harder to read by adding two trailing zeros.
 
In this article, I'll describe how to add decimal places on demand by way of using a custom number format. In Part 2 of this series, I'll demonstrate an alternative that uses the MOD function along with Excel's Conditional Formatting feature.
 

 

Related article:
 Continue reading on www.accountingweb.com .
 
 
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

Oct 31

Reinstalling Microsoft Office When You Don’t Have a Disc or Download

 
by David Ringstrom,CPA
 
 
From time to time, you may need to reinstall Microsoft Office because you purchased a new computer or you're trying to resolve a program error. Or, in Excel 2010 and later, you may need to move between the 32-bit and 64-bit versions. In this article, I'll describe how to download the installation files on demand as well as recover a lost product key for Office.
 

 

Continue reading on www.accountingweb.com .
 
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Oct 24

How to Restore “Shrink One Page” in Word 2010 and 2013

by David Ringstrom,CPA

 

Long-time Word users may recall a handy feature in Word 2007 and earlier called Shrink One Page. This highly useful feature enables you to condense the font size of your document just enough so that you don't end up with a mostly blank page at the end. This feature may appear to have vanished from Word 2010 and 2013, but you can easily resurrect it.

 

Continue reading on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

Sep 10

Convert .snag files to .jpg

The following code converts .snag files into .jpg formats. This isn't directly supported by the SnagIt COM API, but I was able to hack it by way of SendKeys to get an image onto the clipboard. In a broader sense, I'm using this to convert .snag files to .jpg on the file, which I can then insert into blank PowerPoint slides for dynamically building presentations. SnagIt code appears to be scarce on the Internet, so I'm offering this as thanks to others that posted various aspects that I pieced together to accomplish this.

Sub SnagItAutomation()

    'Creates a SnagIt object (SnagIt 8 and later)
    Dim myImage As SNAGITLib.IImageCapture2
    Set myImage = CreateObject("SnagIt.ImageCapture")
    
    'List of .snag file names
    Dim myFiles(4) As String
    myFiles(0) = "image1"
    myFiles(1) = "image2"
    myFiles(2) = "image3"
    myFiles(3) = "image4"
    myFiles(4) = "image5"
    
    
    'Path to SnagIt exe file
    strProgramPath = "C:\Program Files (x86)\techsmith\Snagit 10\SnagitEditor.exe"
    
    'Path to image (.snag) files
    strInputPath = "I:\"
    
    'Path to where resulting .jpg files should be saved
    strOutputPath = "T:\pending\"
    
    For i = LBound(myFiles) To UBound(myFiles)
 
        'Opens image file in SnagIt editor
        Shell strProgramPath & " """ & strInputPath & myFiles(i) & ".snag"""
 
        'Forces Excel to finish the process before moving on
        DoEvents
 
        'Activates SnagIt Editor
        AppActivate "SnagIt Editor"
        DoEvents
        
        'Sends Ctrl-C to copy image to the clipboard
        SendKeys "^c"
        
        'Generates capture
        With myImage
            
            'Tells SnagIt to grab capture from the clipboard
           .Input = siiClipboard
       
            'Tells SnagIt that we want our output to be a file
           .Output = sioFile
           
           'Sets output path for file
           .OutputImageFile.Directory = strOutputPath
           
           'Sets specifies name for output file
           .OutputImageFile.Filename = myFiles(i)
           
           'Sets file type for output file
           .OutputImageFile.FileType = siftJPEG
           
           'Tells SnagIt to use our fixed file name as specified above
           .OutputImageFile.FileNamingMethod = sofnmFixed
               
            'Intiates the capture
           .Capture
           
       End With

    Next

    Set myImage = Nothing

End Sub