Copy/Paste Top n Visible Rows in Excel

November 27, 2016 0

I recently had a question regarding copying data after a filter had been
applied.  What if we wanted the top n items from a list to be copied to
another place.  The problem to address here is that a filtered list can
have cells which are hundreds or thousands of rows apart. Read More

Dependent and Non Dependent Comboboxes in Excel

August 24, 2016 0

Cascading combo boxes have been done online and I had a problem recently
which knocked me for 6 as we say here in Australia.  Anyone who deals in
cascading combo boxes will know only too well that if you are using a list
and the Indirect formula to manage the combo boxes then the process can get
very unruly very quickly.  One of the issues with comboboxes is that they
are set up to work in sequence.  Combo box 1 feeds into combobox 2 which
feeds combo box 3.  The combo boxes need to be filled out in that order. Read More

Advanced Filter a List Automatically in Excel

July 16, 2016 0

I was recently working on an automated procedure to generate data in a list
based on drop down (data validation) selection.  The idea is to change
items in a drop down (data validation) and have the corresponding data
table filter to the specific items chosen in your drop downs.  I have shown
a formula based solution for this method (a long time ago) so if a more
simplistic solution hits the mark – the link can be found here. Read More

Auto-Highlight the Active Row When a Cell is Selected

June 19, 2016 0

Something different on theSmallman today – actually something different all
together.  Since starting my Excel website and subsequent blog over 3 and a
half years ago I have created over 300 pages from financial modelling to
dashboard design to unlocking Excel’s scripting dictionary.  I have writen
every word myself and have drawn extensively on my posts on Ozgrid and
Chandoo forums.  Today for the first time someone else is going to do the
instructing.  The two handsome gentleman above are Kasper and Mikel. Read More

Water Statistics Infographic in Excel

June 18, 2016 0

The above infographic is a look at how we use water and what is expected to
happen if there is a 2 degrees increase in global temperatures . 
Governments are good for changing long term policy for change in our world
but if you want to enact real change you have to make it yourself.  If
everyone collectively agrees to change – change happens in our world.  Our
world will continue to grow in population.  If 7 billion people agreed to
enact change as a collective – climate change, world poverty etc, would end
very quickly in my opinion. Read More

Excel Create or Remove a Table with VBA

June 15, 2016 0

There are times in Excel when you may wish to create a table on the fly
with the assistance of VBA code.  I was in this position recently and
needed to this with VBA.  A file was uploaded into a sheet and the task was
to create a table and then use that table for more data manipulation.  The
following Excel VBA procedure was what I ended up with. Read More

Jump Directly to a Cell with Formula

June 14, 2016 0

I was involved in a post to trace the precedents and dependents of a cell. 
My answer while accurate was a bit long winded as it turns out (happens
more often than I like to admit).  Here is a link to the thread on the
Chandoo forum. Chandoo Jump to Cell Thread 

The question was along the lines of how do I jump directly to a cell with
formula? I was all about the “click on Formula – Trace Precedents – then
click OK and it jumps to the sheet and cell which the formula references.”  Read More

1 2