Great #Excel Technique: August 2017 Total Eclipse Info by David Hager

 

In case you have not heard, on August 21, 2017, a total eclipse shadow is going to stretch across the entire United States. And, the rest of the U.S. will experience a partial eclipse of vaying degree based on the specific location.

This workbook will direct you to information for a specified location at timeanddate.com for the August 2017 total eclipse of the sun.

It is important to note that this workbook does not in any way purport to access or create any information about the eclipse directly. Rather, it uses a link to the web site following the guidelines of the disclaimer to open a web page with the specified eclipse information.

https://www.timeanddate.com/information/disclaimer.html

The creation of the list lookups used in this example were discussed in this recent article.

https://dhexcel1.wordpress.com/2017/06/22/creating-dependent-lists-from-a-column-lookup-in-an-excel-list-by-david-hager/

Please review that article to see how the control cells C3 and D3 work. When the state is selected in C3, the list in D3 is populated with the desired city. When both are selected, in cell D1 (named The DesiredLink) the array formula:

=INDEX(EclipseLink,MATCH(TheCityName&TheStateName,City&State,0))

which looks up the eclipse link for that state and city.

Eclipse1

When the CommandButton is clicked, the following procedure in the Control worksheet module is run.

 

Private Sub GetEclipseInfo_Click()

ActiveWorkbook.FollowHyperlink Address:=Range(“TheDesiredLink”).Value

End Sub

 

This procedure uses the correct link to access the eclipse web site for the specified statye and city, which is opened in your browser. Hoper that you enjoy this, and the eclipse.

You can download the file here.

2017_Eclipse

#Excel Super Links #76 – shared by David Hager

# Excel Super Links #76 – shared by David Hager

Unpivot by number of columns and rows in PowerBI and PowerQuery in Excel

http://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-powerquery-in-excel/

Dynamic Charts using Excel Filters

https://alesandrab.wordpress.com/2013/11/22/dynamic-charts-using-excel-filters/

Highlight specific bars in an Excel bar chart

https://exceloffthegrid.com/highlight-specific-bars-in-a-bar-chart/

What you need to know about Power BI now

http://www.infoworld.com/article/3201824/analytics/what-you-need-to-know-about-power-bi-now.html?utm_source=t.co&utm_medium=referral

#Excel Sparklines: Display Last N Values by David Hager

https://dhexcel1.wordpress.com/2017/06/20/excel-sparklines-display-last-n-values-by-david-hager/

Creating Dependent Lists from a Column Lookup in an #Excel List by David Hager

When you have columns in a list that contain (for instance) state names and their corresponding cities, you might find it useful to create lists from the internal range of the subordinate column. For example, in the list shown here you might want to return the range from the City column corresponding to a specified state in the State column shown below.

 LookupFromTable1

The key to generating lists from the City column is the following formula.

TheCity=OFFSET(State,MATCH(TheStateName,State,0)-1,-1,MATCH(TheStateName,State,1)-MATCH(TheStateName,State,0)+1)

where State is the column in the list on the LookupSheet tab containing state names and

where TheStateName is the cell containing a data validation list of unique states (Control!C3).

where TheCityName cell (Control!D4) contains a data validation list of cities corresponding to the return value of TheCity.

Basically, what this formula does is return a range from the City column, given a specified state.

The following figure shows the cells with the data validation drop down lists.

 LookupFromTable2

The information from these cells can be used for further lookups, which is the subject of an upcoming post. You can download the example file here.

LookupFromTable

 

#Excel Super Links #75 – shared by David Hager

 

Getting A Handle On Userforms [VBA]

https://colinlegg.wordpress.com/2016/05/06/getting-a-handle-on-userforms-vba/

Excel VBE Options

http://www.excelgaard.dk/Lib/VBE/Options/

Excel Solution: Who Should Sit Where?

http://datascopic.net/seating/?doing_wp_cron=1497911478.3255810737609863281250

Basket Analysis in DAX

http://www.daxpatterns.com/basket-analysis/

Excel Short & Sweet Tip #6 (Shuffling a String) by David Hager

https://dhexcel1.wordpress.com/2017/04/25/excel-short-sweet-tip-6-shuffling-a-string-by-david-hager/

 

#Excel Sparklines: Display Last N Values by David Hager

Sparklines are a relatively recent addition to the myriad of tools in Excel (version 2010). They are very useful in creating a number of different ways to visualize data. In this example, creating sparklines from a vertical range of data in a table reveals some interesting options. If you make a data table and create a sparkline from it, the process goes smoothly, generating a sparkline that will auto-expand its chart data along with added data to the table as shown in the figure.

sline1a

But, what if you want to display only the last N values in the table. In that case, you need to use OFFSET formulas as ranges, as first discussed in

https://dhexcel1.wordpress.com/2014/05/04/excel-did-you-know-tip5/

The formula needed to do this (with input from cell F5) is defined as:

LastN1=OFFSET(Sheet1!$A$4,COUNT(TrendData)-Sheet1!$F$5+1,,Sheet1!$F$5)

The method I used to apply this formula range was to create a sparkline from a random selection in the table body and then go back and Edit the sparkline source data as shown below (initially, I had to enter as =LastN1).

sline2

A similar formula was made accepting input from cell F6.

I hope that this technique adds to your data visualization portfolio.

You can download the example file here.

sline1

# Excel Super Links #74 – shared by David Hager

 

Interactive Decision Tree Visualization in Excel [Trump vs. Hillary in Swing States]

http://chandoo.org/wp/2016/10/11/interactive-decision-tree-chart/

Running a Native Query Within an M project

https://seddryck.wordpress.com/2017/06/11/running-a-native-query-within-an-m-project/

Top Tips for Working with Complex Excel Formulas

https://exceloffthegrid.com/tips-for-complex-formulas/

Excel VBA Monopoly

http://www.dustinormond.com/blog/vba-monopoly/

#Excel Short and Sweet Tip #17 (Returning a Letter Grade Based on a Normal Grading Scale Without Lookup Table) by David Hager

https://dhexcel1.wordpress.com/2017/05/26/excel-short-and-sweet-tip-16-returning-a-letter-grade-based-on-a-normal-grading-scale-without-lookup-table-by-david-hager/

#Excel Short and Sweet Tip #23 (Open Windows File Explorer with Worksheet UDF) by David Hager

 

When collecting new links to publish in my Excel Super Links series, I try not to reuse the link in another article. I have been using Windows Explorer (with Windows Indexing enabled) to search the folder where I store these files and look for any files that might contain that link in order to prevent this from occurring. What I wanted was a way to access the File Explorer from the Excel environment. The following procedure performs this task very nicely.

Function WinExplore(TheFolder As String)

On Error Resume Next

Shell “Explorer.exe ” & TheFolder, vbNormalFocus

End Function

This function can be entered in a worksheet cell and when recalculated will open Explorer at the desired folder.

I hope that you find this useful.