Syncronise

Purchase · £2.99

Introduction

Introducing Syncronise for Excel

Syncronise · Filter, Slice and Sort Multiple Tables on separate Sheets in Excel

Syncronise allows you to maintain 2 or more Tables on separate Sheets in Excel that align when you apply a Filter or a Sort including Dates (Dates are changed to the old style, single line). Filters and Sorts can be applied for all of the different types ie. by Value, Cell Colour, Font Colour and Icon. Sets of Sheets and Tables need to be specified in Setup, 1 Sheet and 1 Table per set. A Formula must be added to each Sheet to enable a Workbook Calculation Event to be triggered ie. =SUBTOTAL(3,Table1[id]). Specific Fields can be maintained to Filter and Sort. Specific AutoFilter Drop-down Field Buttons can be switched on leaving the other Buttons hidden

Prerequisites

  • Excel 2013 (Version 5) & 2016 (32bit or 64bit)
  • Basic to moderate Excel Skills with the ability to Copy Code into the Worksheet Code Module and use Tables in Excel
  • Windows PC · NOT a Mac!
  • No support is provided for customization of this Software

Features

  • Easy setup - Copy the Code, adjust the single variable for the sets of Sheets and Tables
  • Specify the exact Fields that you want to Filter or Sort (separate Code example)
  • Turn on only the specific AutoFilter Drop-down Buttons for the Fields that you want to Filter or Sort (separate Code example)
  • Apply Filters by Top Ten Items, Above and Below Average etc.
  • Filter by Font Colour, Cell Colour and Icon or by Dates
  • All normal Filtering and Sorting can be applied including Sorting on multiple levels
  • The Code uses my special method for identifying which Column Field Name has a Sort and the Sort direction applied
  • The Code checks and identify's a psuedo Sort Event on a Table
  • Code Inspection - RubberDucked! (2.5.0.5244)

Limitations

  • 1 Table per 1 Sheet. Multiple Tables on the same Sheet cannot be maintained (Code is being developed to release later to accompany this File to allow you to do this)
  • Using the new Sort where you can add any number of Keys to a Sort, the drawback is that Tables remain selected on each Sheet which isn't the Sheet with the Active Table. This is quite good though as you can instantly know what Tables are not the Active Table but it is a slight annoyance
  • For Dates, the old-style single Dates must be used and not the multiple, Hierarchal Selection Dates (multiple Dates aggregated by Month). A one line Code is already in situ to set this automatically

Installation

Syncronise_v1.zip is ready to use. Download the File after purchase from the link provided. Right-click->Extract all... and extract the Zip Archive to a Folder. Open and test the Tables on the example Worksheets for each Workbook

Usage

Playing around with the Examples

There are 3 Workbooks, Syncronise_v1.xlsm, Syncronise_v1 (specific fields).xlsm and Syncronise_v1 (specific fields & turn off buttons).xlsm. Each Workbook has 4 Sheets with 3 Tables and some dummy data. Sheet1 and Sheet2 contain the linked Tables. Sheet3 contains exactly the same Table as Sheet1 and Sheet2 but it is not syncronised by the Code. Sheet4 contains some dummy data which if Filtered or Sorted will not trigger the Code. Play around with the Tables and Slicers on Sheet1 and Sheet2 to see how Syncronise works. Try using the different available Filters and Sorts and switch between the Tables to view the syncronicity. Try a multiple level Sort. Try Filtering by Icons. The last 2 Workbooks demonstrate how to pick specific Fields when syncronising Tables and also how to just turn on the same AutoFilter Drop-down Field Buttons that correspond to the specific Fields chosen

Setting up Syncronise

[1] First of all setup all of your Sheets and Tables as you want them to be syncronised in Excel, saving the File as .XLSM or .XLSB. Ensure that they are aligned to start with

[2] Press Alt+F11 or View Macros to go into the VBE Code Editor. Click on the ThisWorkbook Code Module. Select all of the Code and then Copy and Paste this Code into your own ThisWorkbook Code Module

[3] Configure the editable variable at the top of the Code Module. Each Set of Sheet and Table must be specified as a comma-delimited String with the Sheet and Table also delimited by a pipe '|', for example: Private Const SyncronisedSheetsAndTables As String = "Sheet1|Table1,Sheet2|Table2" (2 Sets of Sheets and Tables). If you have additional Tables on Sheets but don't want them syncronised, simply omit them from this variable

[4] Now add a Formula on every Sheet where you want to use Syncronise on your Tables to ensure Events are captured for the Tables. Use the Table Name and one Column Name for example: =SUBTOTAL(3,Table1[id]) - my Table is called Table1 and my Column is called 'id'. You can use other Formula, but this one is the way to go!

If you want to use Dates for Filtering and Sorting and Excel picks them up as multiple Dates aggregated by Month, there is a tiny piece of Code that needs to run when the Workbook is opened in order to allow you to do this. This Code changes the Dates from the multiple, Hierarchal Selection Picker to the old-style Date Picker

That's it. Now start Filtering and Sorting your Tables

Screen Shots from Syncronise

A Table using Syncronise linking the Tables on Sheet1 and Sheet2. The Table will be filtered by Field 'Gender' =Female and any other linked Table will have the same Filter applied along with the current Sort applied to the Field 'id'

Part of the Code used by Syncronise in the ThisWorkbook Code Module to syncronise Tables for Filtering and Sorting across separate Sheets

A Table using Syncronise linking the Tables on Sheet1 and Sheet2 for specific Fields and only allowing those Fields to have a Drop-down Field Button

FAQ

Questions and Answers for this Software

Q. I have a large Table and Syncronise works just dandy on 1 Sheet but Slicing on another Sheet doesn't Filter the Table on that Sheet or the Table on the other Sheet?

A. This has happened to me on 2 Sheets with very large Tables and lots of Slicers. For some Tables, sometimes when setting up Syncronise, after adding the Code the Slicers do not appear to trigger the Code on 1 Sheet even with the 2 Formula to trigger the Workbook Calculate Event. Filtering and Sorting individual Fields manually still work okay. Actually the Code is triggered, but it is a bit of a bug either in Excel or with Syncronise. The fix is a bit of a pain if you have lots of Slicers already setup in situ, but simply deleting and then adding back the Slicers to the Sheet that is not working will fix the issue

Q. I have a large Table with over 300 Columns. What is the best way to use Syncronise on these Table sizes?

A. For extremely large Tables it is best to use the Specific Code examples to tell Syncronise the precise Fields that you want to Filter, Slice and Sort. It is often favourable to also hide any Drop-down Field Buttons that are not used so that users cannot use them

Syncronise

This is a video of creating 2 Tables on 2 Sheets and then adding the Code to syncronise them. I also want to only allow 2 Fields to be Sorted and Filtered with AutoFilter Drop-down Field Buttons. Notice that after adding all of the Code, I pause for a moment as it doen't seem to be working and then I remember, you must add a Formul in order to trigger the Code for a Workbook Calculate Event! · view this Syncronise video on YouTube



This is a video of Sorting and Filtering 2 syncronised Tables on 2 Sheets. I do a Level Sort and then some Sorting and Filtering by Cell Colour and Font Colour. Remember Formats must be the same in both Tables in order for everything to align correctly when Sorting or Filtering using Cell or Font Colours · view this Syncronise video on YouTube

Support

Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support (limited) for my Software includes

  • Responding to questions or problems regarding the Software and its features
  • Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include

  • Customization and installation services
  • Support for third party software or ANY kind of development whatsoever

Before seeking support

  • Make sure your question is a valid Software Issue and not a customization request
  • Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
  • Ensure that you access to the VBOM is allowed and that Macros can run in Excel
  • Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
...or Contact Mark Kubiszyn via our Facebook Page - remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary or via Messenger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Future Builds

* Further Code optimisation

Future Builds

* There are currently no bugs identified

Changelog

You can find the version history in the Code Module for any Macro-enabled Software or read more information on the status of each release
- the latest Version including a description of any changes made is always shown first

01.11.2020 - (Version 1) Initial Release