NFL Playoff Schedule 2013: Format

Showing posts with label Format. Show all posts

Sunday, October 20, 2013

Newborn Checklist for New Parents

Sorry for the lack of posts recently (besides sports spreadsheets) but the reason is because I’ve been getting ready for something big, something exciting, something life-altering: my wife and I are expecting our first child next month! We’re super stoked to be having a son but anxious at the same time. We’ve been working hard at getting everything ready but I can’t help thinking, “what if we’ve forgotten something important?” There have been so many things to do and there are so many things yet to do. How does a new parent keep it all organized? My solution is to use an Excel spreadsheet of course!

baby announcement picture team rivalry jersey

In order to keep myself organized and to help other future parents I’ve put together what I hope is the ultimate newborn checklist in Excel. This master file is composed of four separate sheets (I imagine I'll probably add more in the future too):

Things to do BEFORE the Baby is Born

The first sheet is a getting ready for baby checklist. It includes a list of things you should do before your baby is born and how soon before the due date they should be completed by.

Baby Registry Checklist

The next sheet in the file is the baby registry checklist. You can use this for a baby shower checklist or for a list of things you need before the baby arrives.

Hospital Bag Checklist

A list of what to pack for the hospital that you can easily print off if you would like.

Things to Do After the Baby is Born checklist

Another checklist of things that need to be done after the baby is born.

Behind the scenes so to speak, the newborn checklist spreadsheet uses named ranges, data validation to create lists, conditional formatting to color cells based on text, IF formulas, concatenate formulas, data filtering, and more. You’re free to poke around the spreadsheet if you want to see how it works or you don’t have to deal with any of that if you don’t want to and simply fill in the information or print it off.

My goal is to make this the ultimate resource for new parents. I’ve tried to include everything such as what to pack in hospital bag for mom. Tasks can be assigned to husband or wife. I also included some links to some services that can help you save money, like Amazon Mom. Believe me guys, your wives will be very impressed and happy if you use this list and actually help her prepare (yay brownies points). Download the spreadsheet for free using the link below.

Newborn checklist.xls download

Please let me know in the comments if you enjoy this newborn checklist and if you would like to see more parenting Excel templates like it.

Tuesday, April 9, 2013

My Excel 2016 Wishlist

Today, I started thinking about my Excel 2016 wishlist. Microsoft Excel has been updated every three years beginning in 2007. Knowing this, we can only assume the next edition of Excel will come out in three years from now in 2016. The latest update, Excel 2013, includes new tools like FlashFill, PowerView, Timeline Slicer, and over 50 new functions. Don’t get me wrong, Excel is a very powerful tool but it’s not completely perfect yet.

Thus, it’s never too early to start thinking about the next edition of Excel and what improvements can be made. There are a number of features that I think would make creating spreadsheets even easier. A couple of simple improvements could help save users even more time. Here are some of the things I’d like to see in Excel 2016:

A FIXTODAY() function that holds the date of the day the formula was first created or entered into the spreadsheet.
I don’t know if this will ever be possible outside of Google Docs but allowing multiple users to edit the same document at the same time would be wonderful.This would eliminate all those extra copies of spreadsheets floating around. This option is already available!
I don’t know why you can’t do this already but it would be great to be able to unhide multiple worksheets at the same time. Currently, you can only unhide one sheet at a time which can be a major headache and quite inconvenient.
Along this same line, I would like the ability to unprotect multiple sheets at once. Something like a global password may be the solution.
An easy way to be able to see exact revision history.
More options when protecting a sheet, like being able to use CustomViews.
More text manipulation functions and options.

That’s what I think. What about you? What would you like to see in future versions of Excel? What’s on your Excel 2016 wishlist? Please let me know in the comments below. Who knows, maybe Microsoft will even be taking notes!

Tuesday, March 5, 2013

Macro to Export Hyperlinks from Excel to Word

In this tutorial I am going to show you how to write a VBA macro to export hyperlinks from Excel to Word. I’m all about automation and efficiency and this is another real world example. I used this macro to quickly create a table of the world’s observation wheels for my latest website.

What you will learn by reading through this tutorial:

How to create a VBA macro to send data from Excel to Word
How to export hyperlinks from Excel
How to find the last row of data in an Excel sheet using a macro
How to paste hyperlink into Word using a macro
How to make a webpage from an Excel file

To begin, we have an Excel sheet with names in column A, some of them are hyperlinks to webpages and some of them are not. Hit Alt + F8 and create a new macro, I named mined Tables. It’s time to begin coding.

The first thing we need to do is create a new instance of Microsoft Word and make it visible:

Dim appWD As Word.Application

Set appWD = CreateObject("Word.Application")

appWD.Visible = True

Next, let’s find the last row that contains data within our active Excel spreadsheet (my sheet is called “Data”):

Sheets("Data").Select

Dim FinalRow As Integer

FinalRow = Range("A9999").End(xlUp).Row

Now we add some error handling - if there is no data then quit the program, otherwise continue on:

If FinalRow = 0 Then

Exit Sub

Else

As a check, I like to have a message box pop-up displaying the total number of rows with data:

MsgBox "Number of rows is " & FinalRow

Now it’s time to tell Word to create a new document. We’ll also add our header text by using TypeText. TypeParagraph inserts a new paragraph by going to the next line:

appWD.Documents.Add

appWD.Selection.TypeParagraph

appWD.Selection.TypeText Text:="[table caption= List of Observation Wheels]"

appWD.Selection.TypeParagraph

appWD.Selection.TypeText Text:="Name,Height(m)"

Next, we’ll create a For...Next loop to cycle through every row and look to see if there is a hyperlink in column A. If there is a hyperlink, we want to copy it, if not then we still want to copy any text in the cell.

Dim hyperlink1 As String

Dim i As Integer

For i = 2 To FinalRow

If Range("A" & i).Hyperlinks.Count > 0 Then

'if there is a hyperlink

appWD.Selection.TypeParagraph

hyperlink1 = Range("A" & i).Hyperlinks(1).Address

appWD.ActiveDocument.Hyperlinks.Add Anchor:=appWD.Selection.Range, Address:=hyperlink1, SubAddress:="", ScreenTip:="", TextToDisplay:=Range("A" & i)

appWD.Selection.TypeText Text:="," & Range("B" & i)

Else

'If no hyperlink

appWD.Selection.TypeParagraph

appWD.Selection.TypeText Text:=Range("A" & i) & "," & Range("B" & i)

End If

Next 'i

Finally, we can add any text at the bottom of the document and close the if and sub statements.

'end the table

appWD.Selection.TypeParagraph

appWD.Selection.TypeText Text:="[/table]"

End If

End Sub

You may have noticed I began and ended with [table] and [/table]. I installed a Wordpress plugin on my website that enables me to easily insert sortable tables into my webpage without any major html coding involved. So now I can run my macro on my spreadsheet, copy the result it spits out into Word, and paste the text into my website. Here is the end result, a sortable table of all large observation wheels found throughout the world! Pretty cool huh?