How do you merge multiple spreadsheets in Microsoft Excel?

Answer 1

In the destination spreadsheet (the one that you'd like values/formulae to be brought in to) select the cell that will receive the link, hit the "=" button, go to the origin spreadsheet and select the cell that you're linking to, and finally hit the "Enter" key. An alternative to this approach is to go to the origin spreadsheet, highlight and copy the cells you'd like to link to the destination spreadsheet, go to the destination spreadsheet and paste special link cells.

Answer 2

Here's a little macro that makes merging sheets easy. Just select the sheets you want merged making sure the active sheet (visible one) is the one you want to merge to. The rows from all the other selected sheets will be copied to end of the active sheet in left-to-right order of worksheet tabs. It allows you to declare a number of header lines (NHR value) so that you don't copy the worksheet's headers to the first sheet.

This macro should be put into a macro module. If you don't know how to install/run a macro, just follow up and I'll give you instructions.

Damon

Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS

Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End Sub

Answer 3

You use the name of the sheet, followed by an exclamation mark and then the cell reference. So to link cell C45 on Sheet1 into a cell on Sheet2, you would enter the following into the cell on Sheet2:

=Sheet1!C45

Answer 4

That depends on what you mean by joining two worksheets. There are different ways that could be interpreted. You can take a worksheet from one workbook and move or copy it to another workbook. Right click on a sheet tab and pick the option from the menu to do that. You can copy and paste data from one worksheet to another. You can use the Consolidate facility to bring values from different worksheets together. You can use formulas to do calculations with numbers on different worksheets and have the total appear on the same or other worksheets. To do that you incude the worksheet name and an exclamation mark before the cell reference. So if you wanted to add the values in the cell A2 on Sheet1 and the cell F5 on Sheet2 and have the total appear on Sheet3, then in a cell on Sheet3 you would have the following formula:

=Sheet1!A2 + Sheet2!F5

Answer 5

Use the sheet name in the formula.

=SUM(Sheet1!A1:Sheet1!A23)+SUM(Sheet2!A1:Sheet2!A23)