We have become good friends with the Ysais family of Kyle, Texas. They visited us in Colorado last summer and we all had a great time. Corrina stays in touch with Marie almost every day, our daughter Skypes and plays Minecraft with the two boys, and Michael taunts me with mouthwatering photos of his smoked brisket.
Marie runs Ysais SEO, which helps businesses optimize their search engine results. She asked me to help with an Excel workbook to create Google sitemap links. I took the file she sent and worked it into a tool that can create several links for each specified website.
I thought I would share the workbook because it makes a good example of how to use Excel’s table feature and its HYPERLINK function. With Marie’s consent, here is the workbook. Click on the image to open the file.
There are four sheets, each with its own table (note that these are not “data tables”). The first three tables provide the building blocks for the links: the Google sitemap text (the “prefix”), the domain names, and part specifiers (the “suffix”). On “SiteMaps”, the last tab, a table generates hyperlinks for all possible combinations of the three text blocks.
To use the workbook, make sure that the prefix text meets your needs. As with all inputs in this workbook, the prefix text is colored blue. You can insert rows above if you need more than one prefix. Next, enter the domain names. There is room for 500 domain names. Blanks are fine, and you can always insert more rows if you need more. The final input step is to review the suffix text. You can insert more suffix items if you need more.
Now go to the sitemap generator on the last sheet. Click on the “Good?” filter and select “TRUE” only. This will hide the blank links. Be sure that the bottom of the “Last?” column shows a “TRUE” value. This insures that the last possible combination of your text blocks has been reflected in the sitemap list. If not, you will need to insert more rows into this table.
Marie reports that the workbook also made it easy for her to create Bing sitemaps.
Note: a good way to insert rows into any of these tables is to click on the cell labelled “Count” at the bottom of the first column. Insert the number of needed rows and copy down any formulas from above as needed.
Please let me know if you have any questions.
The Well-Tempered Spreadsheet 
I posted this here because the posts have been from years ago in which I am relating this question to:
Hello, I have used your website for a great deal of research and it is an EXCELLENT resource. i have an issue I am struggling with understanding. As it relates to Weighted Average Life, I have seen this defined many ways from many sources. Some say it is the average time for a dollar of principal to be repaid/(or remain unpaid) and others say the WAL is the number of years until the principal is paid. This seems to be conflicting to me. I read on a reputable website that if you calculated WAL of a 30 year fixed rate mortgage to be 20 years, that is equivalent to a 20 year bullet. I don’t full understand how that is. The bullet will be paid off in 20 years however the principal balance on the mortgage wont be paid of in 20 years. 20 years is just the average time of time principal remains outstanding. I truly appreciate you clearing this up for me. It means a lot to have your time. I also am having trouble going from a CPR to WAL, I have been taught 1/CPR = WAL. Which seems to be true however when I use that same methodology to convert SMM in WAL and then divide by 12 months. I get a lesser WAL.
Again thank you so much for your time!
LikeLike