Horizontal to vertical formula across sheets

Typically, to transfer horizontal rows into vertical columns in Excel you would highlight the cells you want to change and copy, then right click on the cell you want to move to, select ‘paste special’, click ‘transpose’ box, and hit ok. But what if you want to accomplish this task with a formula instead so it is done automatically on the fly? In an earlier post I showed you how to do it by using the OFFSET function combined with a named range. Today, I’m going to show you a different Excel formula to transfer rows to columns.

I recently used these horizontal to vertical formulas in my 2013 NCAA college football bowl prediction pool manager, and I am going to refer to that template as an example, so if you haven’t already I recommend you download the sheet. I needed to get the list of the players from the main sheet, which were listed out horizontally, and get them into the leaderboard sheet. The players needed to be listed vertically in the leaderboard in order for my rank without ties formula to rank the players in order of who picked the most games correctly. The basic formula uses INDEX and ROWS functions and looks something like this:

=INDEX(Sheet2!D$22:H$22,ROWS(A$4:A4))

The INDEX function returns the value of the cell at the intersection of the rows and columns specified in the formula while ROWS returns the number of rows in a reference or array. The exact formula I used on the leaderboard sheet in cell A3 is shown below:

=IF((ROW()-2)<=$L$3,INDEX('Master Pool'!J$39:Z$39,ROWS(A$4:A4)),"")

The formula - notice the range

The range expands as the formula is filled down

It takes all the names in row 39 on the Master Pool sheet from column J to Z and puts them into a vertical column in the leaderboard sheet, starting in row 3. The dollar signs are very important. As you fill the formula down the range increases. I also used the IF and ROW functions to account for the number of players (if the row number minus two, because the formula started in row 3, is less than or equal to the total number of players, then transpose the data, otherwise leave blank). As you can see in the example spreadsheet, the data is transformed from a horizontal row on one sheet to a vertical column in another. This is important because the data in the horizontal column can change and the vertical column will update automatically - no manual revisions needed!

Excel Create Folder Macro Updated

Today I’m going to revisit how to create a folder in Excel. There’s been some good discussion on my earlier post about to use an Excelmacro to automatically create folders. My original version only created folders in the same file where the Excel spreadsheet was saved. After some reader questions and collaboration we’ve created a new version which allows you to browse to the directory location where you would like the VBA macro to automatically create all the folders you have listed and selected in the Excel workbook.

A reminder of how the Excel macro creates folders. Make your list of folders in any column in a worksheet (which does NOT have to be saved like in previous versions). Select the range of names you want to create. Run the macro.

To open a folder browser with an Excel macro we need to create a shell application object using this code:  

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

I put together a short video showcasing the end result and how the Excel VBA create folder macro should work. Also, if you’re looking to build your own website watch the video to get a 25% off coupon for Host Gator.

The complete code is listed below. Now you can show your bosses and coworkers how to make a folder with Excel. Please join our newsletter for more Excel tips.

Sub Create_Folders()

‘http://excelspreadsheetshelp.blogspot.com

penAt = "My computer:\"

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)

On Error Resume Next

BrowseForFolder = ShellApp.Self.Path

'create the folders where-ever the workbook is saved

Dim Rng As Range

Dim maxRows, maxCols, r, c As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count

maxCols = Rng.Columns.Count

For c = 1 To maxCols

r = 1

Do While r <= maxRows

If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then

MkDir (BrowseForFolder & "\" & Rng(r, c))

On Error Resume Next

End If

r = r + 1

Loop

Next c

End Sub

 

Create Folders.xlsm Download

Join our newsletter for more helpful macros.

How do I copy a range with an Excel macro? (And over Range macros)

To copy data from a specific range can be done with the following macro in Microsoft Excel 2007. In this example, data is copied from the current sheet to the active cell.

Sub CopyRange()
Range("A1:A3").Copy Destination:=ActiveCell
End Sub

To copy from a range in another sheet, Sheet4 in this case, to the active cell you need to change the code to:

Sheets("sheet4").Range("A1:A3").Copy Destination:=ActiveCell

To specify a macro to go to a specific range you can use the Goto method. Here I have already named a range in my worksheet called "Engineering". You may also use an alternative method like the Range select method. Naming a range in Excel is recommended rather than specifying an absolute cell reference.

Sub GoHere()
Application.Goto Reference:="Engineering" OR Range("Engineering").Select
End Sub

Assigning range names to a range of cells.

Sub RngName()
Selection.Name = "myRange"
End Sub