Robust Tasks In Excel Using Dependent Droplists, Tables & A Little VBA

Building Solutions Using: Tables, VLOOKUP, INDIRECT & VBA

/in /by

Today we weave together a few Excel features to build out a dynamic solution.
We have Offices, Reps and their Percent of Quota.
Using dependent dropdown lists, we solve a problem of changing the Office selection but the Rep name stays next to the wrong Office.

With the help of Excel MVP Jordan Goldmeier, we use VBA to solve that.

And then we look at the ease of adding data from a new City. By using tables, INDIRECT and VLOOKUP, this is very easy to do.

The blogpost and download link are here:

Thanks for watching the video. Please send any questions. Please subscribe to the channel and let’s work together to keep our data clean!

Excel App for Randomizing Cafeteria Specials

/in /by

Here’s a mini app that I developed based on a challenge that Mr. Excel answered:
How do I randomize an alphabetical list of cafeteria specials?

Kevin Lehrbass also took this on with a different spin

http://www.myspreadsheetlab.com/2014/01/helping-dean-pelton-with-his-menu-in-excel/
This whole thing was intriguing to me as I thought about the various problems that would come up as a real Cafeteria Head manages a meal schedule:
– How to make a solution in Excel that doesn’t go overboard?
– What about meals showing up more than 1x within 5 days?
– What about meals that are complicated for the staff to prepare?
– How do I add new specials?

In this video I give you a tour of the what’s what in the app. I don’t go into the fine details because the thought process is what I really want to share.

I am writing a related blogpost. That should be up on Monday, and I’ll also make the file available for download.

Please comment, ask questions, and let me know if this is at all interesting or helpful.

Be good, yawl!

calculating time with the company & paid time off

/in /by

Sharon asked: how do I assign people the correct number of days off?

Employees receive a number of Paid Days Off based on how long they’ve been with the company. In this tutorial we use the functions:
YEAR
YEARFRAC and
VLOOKUP with True

The Importance of Keeping Spreadsheet Data In One Place

/in /by

(NOTE: the features in this video are not available to Mac users or PC users with Excel 2003 or 2007)

A common blunder in spreadsheet development is the separation of data into categories.
Stop it!
When you have 12 tabs for each month and want to know how Q2 went for a particular office, you’ve got a lot of formulas to write, test, troubleshoot, tweak and manage. HOWEVER …

When the source data is centralized in one location, we can access anything we need.

This video is long, yes it is. But you get to see the value in keeping your data contiguous. Over these 20 minutes we build a simple interactive dashboard, choreographing multiple Excel features into the most beautiful thing you’ve ever seen. Features included:

Pivot Table
Pivot Chart
Logarithmic Axis
Slicers
Slicer settings
SUMIFS
VLOOKUP
Table
Find/Replace

If you have massive spreadsheets and separated categories, spend the 20 minutes and see if you can save some time.

Presenting data that has a wide range between your largest & smallest entries

/in /by

Logarithmic, Scales, Combo Charts & Secondary Axes.

Often we get data that is hard to analyze because the range between the data is huge. Here, we look at 2 examples:
1. City populations that range from 300 to 614,000
2. Volunteer hours that range from 1.5 to 91

To create meaningful views, we use logarithmic scale, combo charts and a secondary axis. All are in excel and ready for your use.

Getting Excel To Treat 3 Reports Like 1 Master Report

/in /by

This video accompanies the blogpost: http://datascopic.net/excel-data-model/

Today we have 3 sets of data and 7 data fields related to website users and their activity..

1. User ID, User Type
2. Personal ID, Name, Profession
3. Log-in Date, ID, Time Spent Logged In

What do we do when we want to look at correlations between 2 or even 3 of the datasets? It’d be nice to know if certain professions weren’t using the site much. But Time Spent and Profession are in 2 different datasets.

VLOOKUP would help us stitch this together. But let’s try something new (and easier) in Excel 2013:
– Table Relationships
– Data Models

Check out the video and see how. Afterward, you’ll be a Data Superhero wearing your underwear outside of your pants!

VLOOKUP w/True does not mean “approximate”

/in /by

It’s always a student who asks a question that I hadn’t thought to ask.
“So, Oz, since True is approximate can I use it to match Ann Giamatti and Anne Giamatti?”

No! That’s a good intuition but VLOOKUP w/True is used for categories and tiers. Let’s look at it this way:

We’re grading students where 90+ is an A and 80 to 89 is a B.
89 is “approximately” 90 so, shouldn’t an 89 be an A instead of a B?

With True, we help VLOOKUP by sorting our list in order and then making clear categories, tiers, strata, compartments, sections, etc.

Details on the VLOOKUP w/True and the Binary Search Algorithm:

Please subscribe and leave comments, requests or questions. Also, check out my blog at: http://datascopic.net/

Excel’s AGGREGATE function for Managing RSVPs and un-RSVPs

/in /by

In this example we see the AGGREGATE function in this video and use it for managing a list of members and guests.

Challenges: we have the list of members and number of guests, and then people start saying they aren’t attending. GEE WHIZ! And then our row numbers get screwed up.
Solution: use AGGREGATE and hide the rows when people un-RSVP.

Thanks to Mr Excel, Bill Jelen, for his video on numbering un-hidden rows. (http://www.youtube.com/watch?v=M8jEX3HFxus) That pulled me out of a fire.

Please subscribe to this channel. I’ll be putting up more videos on Excel and an occasional video on sriracha. Life isn’t right if you’ve got messy data and no sriracha.

Ask questions, leave comments, let me know if you’ve got cool stuff to share, and visit my blog. :-) http://datascopic.net

Excel’s Data Explorer, Pivot Chart & Grouping of Dates

/in /by

NOTE: Data Explorer is now called Power Query

We want a simple bar chart showing U.S. Governors, by party, and the year their terms end.

To do this, we use
1. Excel’s Data Explorer Preview
(a free Microsoft AddIn for Excel 2010, 2013. Sorry, Mac users. This is another feature that’s only on the PC side. Download it here:http://www.microsoft.com/en-us/download/details.aspx?id=36803 )
2. Pivot Chart
3. Group details in the Pivot Table

Please subscribe, leave comments and check out my blog at http://www.DataScopic.net