Excel – From Basic to Advanced for Advancepro Users

Synopsis:

Learn how to format and use excel files for use with advancepro, for creating pricelists, complex sku’s, and more!

 Webinar transcript

APT University: Excel – From Basic to Advanced for Advancepro Users

 

Preset notes:  use product import demo file on desktop – make sure a copy has already been completed and brought into advancepro

 

(Show Title Slide)

 

Hello and welcome to AdvancePro University.  Some advanced techniques for using AdvancePro with excel.   We’ll be discussing some formatting rules,  some tricks to avoid duplicating records, and Excel formula usage to greatly enhance the capabilities of AdvancePro.

 

While today’s presentation will be using Excel – all of these techniques can also be performed using other software such as google sheets.

 

The main topics we’ll be covering today are:

 

1. Price Formulas – How to update prices and other values
2. Concatenation, UPPER, and LEFT – how to assemble a SKU from several other fields of information.
3. Vlookup – how to fill data based on rules and seperate lookup charts
4. How to reformat a sheet with formulas to bring into AdvancePro
5. Macros and clipboard management – how to fill out forms that are not excel importable

 

—————————————–

(Show Sheet)

 

So as our example today I have created a basic spreadsheet for importing Products today,  and it’s missing some important information for many of our products,  it’s missing the SKU, product name, an image path,  the Sale Price, and a custom field that I want to make visible on my orders – which I have labelled as type.   Lets fix each of these issues independently. I’ve already fixed these issues for the first line,  I’m going to walk you through solving them for the remaining lines as well as the basics about how these solutions can be put together

 

1. Price Formulas

Starting with something simple, we’re going to run a basic math operation on a cost price to give us a selling price.   

 

If you’re an absolute beginner to excel,  to use a formula you always start with an = sign, and you can do simple meth here just using plus minus star for multiplication or backslash for division,  and you can use the value held in another cell just by typing in that cell’s coordinates,  like E2.

Let;s say we want a 250% markup – here you can see I’ve multiplied the cost price in E2 by 2.5,  which would give us a number that doesn’t work out evenly in dollars and cents – which advancepro can still take if you want.

 

  In this case though,  i wanted to round it evenly.  So I’ve wrapped that normal formula in a ROUND function, then i did the math in brackets inside that function.  This particular rounding formula also asks how many decimals it should round to, so I told it to round to two decimal places.    This gives us an even cost in dollars and cents.

 

1. Concatenation, Upper, and Left

So we’re going to use the concatenation function, along with upper, lower, and left functions to grab the information we need to build a product name,  SKU and an image file path

 

Let’s start with the Product name  – So here we want to show the vendor brand,  the category and the sub category,  so we’ve used the concatenate formula,   this tells excel to combine the contents of other cells one after another – so we’re starting with the contents of G2,  then you’ll see just a space in quotation marks – to put a space before the size of the wrench, which is the subcategory,  then finally the main category with another space – I also added a little inch marking with the space in quotation marks.

 

Now we’ll make it a little more complex and we’ll make our SKU – since SKUs are used for quick reference, I don’t want the full vendor name and categories,  I just want to create abbreviations,  So I’ve used concatenate again, but we’re doing other things to get the chunks that we are putting together with concatenate.   First,  we’re using the LEFT function, which will use the first characters on the left hand side of the cell,   we’re telling left to grab the contents of cell G2 – snap on,   and use the first four characters,  which gives us SNAP.   now next we’re going to abbreviate socket – so we’re doing the same thing to the C2 cell to get sock.

 

But you can see i chose to make this lower-case,  and we do that with the LOWER formula,   you can do the opposite and make lowercase letters into uppercase with the UPPER formula

 

Then we’ve added hyphens in between the values and just grabbed our sub category for to finish the SKU

 

Now when you’re building something this complex you can see that you use a bracket at each layer,  once for the LEFT command, once for the LOWER command, and once more for the whole Concatenate command  – this is an area where a lot of errors can pop up if you’re still learning, so if you’re having difficulty, take a close look at the brackets.

 

And for Image path,  we’re just slightly different again,  what we’re assuming here is that on the main drive of our server,  our image files are sitting in a folder called Images,   and that the images are all labelled by SKU  and end with JPG. so the beginning and end are fixed text and we’ve just sourced our SKU – which in turn is being built by the categories and vendor information.

 

Something to note here is that I have left dynamic addresses unlocked,  so if I cut and past these formulas from row 2 to row 3 – they’re now going to reference the information on the relevant row – this is really great for this type of work, but in the next portion working with lookup values, you really want to lock some values down.   To prevent changing rows, put a dollar sign in front of the row number,   likewise for columns which change if you move horizontally,  put a dollar sign in front of your column letter.

 

One big note – if you plan on making a change to customer or vendor names – such as swapping first and last names for easier lookup,  Make sure you already have  Account numbers assigned in Advancepro, or you will create duplicate  customers or vendors.

 

1. Vlookup

So in this next step we have a custom field we want to fill  based on  some other information – The best anology here is a phone book – we have a name and we want to look up some other linked piece of information

 

What I’ve done is set up a reference table on a second sheet,  so we can see how different categories will dictate different values for this field we’re calling TYPE.

 

The formula we’re going to use is Vlookup – first we tell excel what information it will use to search our reference table – in this case the category.   

 

Next we’re going to define the reference table dimensions – since these are on another sheet we’re going to put in the sheet name and use an exclamation mark – once we’ve done that to tell excel what sheet to look on, we can put in the values as normal – start with the cell on the upper left and finish with the cell on the lower right.   You always want the value you’re searching against on the left,  and the value you want to fill the cell on the right,  now the next peice of information for this formula is the index – which tells Excel which column to look on for the information you want,   I’ve filled in 2 because the response we want is on the 2nd column of the reference table.  Finally,  excel wants to know how you have sorted your table – in almost every instance, you will write false,   which means the data is not sorted and that excel should look for an exact match.

 

You can see I’ve locked the rows and columns here for the reference table, since that is always going to be the same.  But I’ve left the lookup value unlocked,  so that as I paste this on each row, it will always look for the category on that row.

 

One other tip is that you’ll see I’ve changed the name of my header,   when you do your file import, as long as you haven’t added spaces to the headers, they can be named whatever you like and you can map them to the correct field in advancePro easily.

 

1. Import rules

Now once we’ve completed our data updates,  we need to get rid of any formulas or formatting we might have done.

 

First, hold down control and press the A button to select all of your data,  then go to paste special, and paste the values – this gets rid of any formulas.   Then move to the format area on your home ribbon and remove all formatting,  you can also right click and format everything as text.

 

Also,  make sure that the data you want to import is on the first sheet, and that the first sheet is called sheet1.

 

Finally, make sure your headers have no spaces,  we recommend using underscores or hyphens instead.

 

1. Macros and clipboard management

So there is one more powerful trick I would love to show you today, and it uses some extra tools – and it has an even steeper learning curve than the formulas we touched on today

 

The first is called a clipboard Manager – the one I prefer is called M8 Free Clipboard – and you can also get a more advanced one  called Spartan Clipboard. – Essentially when you use the cut or paste commands  – this piece of software remembers what you cut so you can grab many pieces of information and use them quickly later,   Clicking the items in your history instantly makes them the active items you can paste.

 

Next is a macro scripting tool,  This is used to record clicks and keyboard commands across multiple programs,  these can be replayed easily, which essentially lets you automate any series of actions.   My preferred macro tool is AutoHotKey – which is incredibly powerful and has many accessories.  This is a free program that’s also available.

 

So the reason you might use these together is for data input that isn’t available through an excel import.   What you can do is cut various pieces of information into the clipboard manager – like this value column,   then you can record yourself inputting the data just once – and use the hotkey scripts to either automate or semi-automate the rest of the process,  doing it over and over again while you go get a coffee.

 

So for this example I might cut the SKU and the VALUE fields – you could also use a hotkey to do the cutting here.   And now that they’re stored I’m going to go to AdvancePro and look up the sku, edit the item, go to the correct tab and input the value here.  Then come back and delete the value and sku, so the next items are in the same spot that the old ones were.  Now If i recorded that,  i could run that recording, and my macro would grab the new items instead of the old ones.

 

This is a very advanced skill and will probably require quite a bit of practice  – but it is extremely useful in some situations.

 

If you are having any issues with advanced data input,  I encourage you to reach out to the AdvancePro Professional Services team  – through your Account Manager.

 

This concludes our lesson today.  if you have any questions please feel free to reach out to your AdvancePro Advisor.