North Port Solutions, LLC
North Port Solutions enables success through effective software, including integration of software p
13/11/2023
Using Excel for data entry? Adding drop-down lists keeps your data clean and consistent. Or, better yet, try dynamic drop-down lists.
The Power of Dynamic Drop Down Lists in Excel: Streamlining Data Entry and Analysis Discover three types of dynamic drop-down lists in Excel, and how to create them.
02/11/2023
Don't let the names fool you: VB Script and VBA are two different things.
VB Script vs. VBA VB Script and VBA are two different things - do you know which is which? What will happen now that VB Script is deprecated?
Not sure if you should use Microsoft Access or not? A lot of small and mid-sized businesses aren't sure. Here are some reasons why you might want to.https://www.northportsolutionsllc.com/post/why-choose-microsoft-access
23/10/2023
Who are your best three customer? Your best three sales reps? Best three products?
Here are some simple Excel techniques to find out.
Your Best Three Three ways to see your Top 3 in Excel using conditional formatting, the RANK function, and Pivot Table filters.
20/10/2023
There's always more to learn in Excel! I just stumbled on this video covering the SUMPRODUCT function. Wow - what an amazing function! Disclaimer: I don't know the creator, and haven't tried her classes. But couldn't resist sharing such a great video!
Master Excel's SUMPRODUCT Formula Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/Quickly learn how Excel's SUMPRODUCT formulas works.Download the workbook: http:/...
19/10/2023
Excel users: here are 3 good reasons to use named ranges. These are sanity savers when you've got complex formulas!
Three Reasons to use Named Ranges in Excel Here are 3 good reasons to use named ranges in Excel.
19/10/2023
EOS Boston Strong in Six - A Business Traction Event Get ready to level up your business game with this Strong in Six Business Traction Event, powered by The New England EOS Community
Ever see those pesky /A cells in Excel? Here's how to stop them from causing issues with your VBA code. https://www.northportsolutionsllc.com/post/detecting-n-a-with-vba
14/09/2023
Ever miss your alarm? Here's how to make Excel really annoying, so that you can't ignore it!
BEEP! An Audible Alarm in Excel Create an audible alarm in Excel that beeps when a timer expires.
07/09/2023
Not sure if you need a subform in Microsoft Access? Here's how to decide.
Do I need a subform on my Access form? When to use a subforms in a Microsoft Access database
08/08/2023
Have you ever wished that Excel had a timer function, so that you could schedule things to run on a regular basis? It can be done!
Scheduling Excel Macros with a Timer A while back, I wrote about creating a timer in Excel. In that post, I went into detail about my failed attempts to create one, and how I eventually solved the problem. You can catch up on the blog here. If you'd rather not read all that detail, though, I'm going to give a short version here. Why? F...
A single Excel file can include live links to multiple databases. Got data in SQL server and Oracle, for example? Use Excel to bring them together. https://www.northportsolutionsllc.com/post/connecting-data-sources-in-excel
26/01/2023
Did you know that you can link your Excel sheet to your SQL data? Then you've got full access to Excel's charting, filtering and pivoting options, giving you great visibility to your key data points!
Linking Excel to SQL Server Add a link to SQL server data to your Excel worksheet.
Did you know that you can create a live link to your database in Excel? This allows you to use the outstanding graphing and charting features of Excel to analyze data in it's native source. The best of both worlds! https://www.northportsolutionsllc.com/post/linking-excel-to-your-data
05/01/2023
Most businesses have data scattered over several different sources. One option to bring them together is Microsoft Access. In the first post in this series, we'll see how to link your Excel data.
Linking Access to Excel Data Create a Microsoft Access table that is linked to an Excel file.
03/01/2023
Happy New Year! Ready to dive deeper in VBA this year? Here's a good place to start. Users will make mistakes, and some of those will impact your VBA code. Here are three ways to handle them.
VBA: Three Ways to Handle Text in Numeric Fields Users will make mistakes, and some of those will impact your VBA code. Here are three ways you can handle that.
22/12/2022
New Years Resolution: Keep Your Data Clean!
Today I wrapped up my series on using MS Access tools to clean your Excel data. If you have both tools, this method allows you to keep your data in the Excel format that you know and love, while also leveraging the power of Access. In this final chapter, I talk about getting your cleansed data out of Access and back into Excel.
Using Access to Clean Excel Data, Part 5 I’ve been covering the use of Microsoft Access tools to clean your Excel data for the past few weeks. Today is the final entry in that series, and our topic is getting the cleaned data back to Excel. To recap, my premise is this: Microsoft Access has lots of tools that can help you clean up your E...
TWINS! Today's VBA topic is very similar to my Excel post from the other day. Both are discussing different ways to prevent duplicates in your Excel data. This time, I look at using VBA code to catch nearly-identical entries.https://www.northportsolutionsllc.com/post/doubles-but-no-troubles
16/12/2022
Capturing data in Excel? Prevent duplicate entries with an easy formula.
Double Trouble Today we’ll be looking at preventing duplicate values from being entered into your Excel sheet. This is the final entry in a series that looked at several ways to ensure that data being entered into your Excel sheet is valid. In this case, we’re going to use a custom validation rule, which we’...
12/12/2022
In this final blog on using MS Access tools to clean your Excel data, I look at finding duplicates.
Using Access to Clean Excel Data, Part 4 Use MS Access to find duplicates in your Excel data
In this final blog on using MS Access tools to clean your Excel data, I look at finding duplicates.
https://www.northportsolutionsllc.com/post/using-access-to-clean-excel-data-part-4
06/12/2022
Validating that a cell contains numeric data in Excel is easy... usually. Sometimes your business rules make it a little more complicated. In this example, I allow the user to type in any valid number, or the word "Pending". Any other text is disallowed.
Validating Numeric Fields in Excel Use Excel's Data Validation feature, in conjunction with Excel functions, to validate numeric data.
01/12/2022
I've been talking about phone numbers in Excel, and today I've got one last blog on that topic. This time, I'm looking at using VBA to parse out a phone number and extension in the same cell, even when they are inconsistently formatted. I've written my sample code in Access, but it'll work in Excel too.
Parse Phone and Extension with VBA Last week, I wrote a blog post about creating a validation harness in Excel VBA. I laid out a structure you can add to your Excel file that allows you to add custom validation as users are entering data. Now that we’ve laid that framework, we can work on adding some useful validation. Phone Number...
28/11/2022
Capturing phone numbers in Excel? Here are 3 methods of formatting and validating those numbers.
Validate Phone Numbers in Excel Use Excel's formatting and data validation features to validate phone numbers
To each his own! If you've got multiple people entering data into your Excel sheet, you may end up with phone numbers in a variety of formats. Here are 2 excel tools you can use to keep them clean.
21/11/2022
Special hours for this special week ahead!
Regular hours Tuesday, shorter hours Wednesday, then come see us on Small Business Saturday - we'll be ready for you!! 🥳
Got phone numbers in Excel? I'll bet they're a mess, right? Here's one method of fixing them.
https://www.northportsolutionsllc.com/post/using-access-to-clean-excel-data-part-2
18/11/2022
KEEPING YOUR EXCEL DATA CLEAN:
Continuing with my previous blog on validating U.S. states in Excel, today's blog talks about setting up a structure in VBA that will allow you to perform advanced validations.
Validation Harness in Excel VBA Adding a Visual Basic structure to your Excel file for validating data.
Do you have addresses in Excel? Here are some methods of validating a U.S. state.
Is there a quick Excel or Access question I can answer for my fellow small-business owners?
Small Business IT Support
Does your software work for you, or do you have to jump through hoops to appease it?
Software should be a tool that makes your job easier and allows you to work more efficiently. If that’s not the case, there are solutions. Integration. Enhancement. Utilities. You’ve got options.
An evaluation of your needs is always free. Let’s brain-storm ideas for improving your software situation.