How do I reference a cell range from one worksheet to another using excel formulas?

Question

I have a single worksheet with sheets Sheet1 and Sheet2 and I am trying to reference a range of cells from Sheet2 to Sheet1

I know how to reference worksheet cells such as =Sheet2!A1 but how can I do the same for a cell range such as A1:F1 I tried =Sheet2!A1:F1 but it does not like the syntax.

I need to use Excel Formulas for this if possible.

Answer 1

Simple ---

I have created a Sheet 2 with 4 cells and Sheet 1 with a single Cell with a Formula:

=SUM(Sheet2!B3:E3)

Note, trying as you stated, it does not make sense to assign a Single Cell a value from a range. Send it to a Formula that uses a range to do something with it.

Answer 2

The formula that you have is fine. But, after entering it, you need to hit Control + Shift + Enter in order to apply it to the range of values. Specifically:

1. Select the range of values in the destination sheet.

2. Enter into the formula panel your desired formula, e.g. =Sheet2!A1:F1

3. Hit Control + Shift + Enter to apply the formula to the range.

Answer 3

Ok Got it, I downloaded a custom concatenation function and then just referenced its cells

Code

    Function concat(useThis As Range, Optional delim As String) As String
 ' this function will concatenate a range of cells and return one string
 ' useful when you have a rather large range of cells that you need to add up
 Dim retVal, dlm As String
 retVal = ""
 If delim = Null Then
 dlm = ""
 Else
 dlm = delim
 End If
 For Each cell In useThis
 if cstr(cell.value)<>"" and cstr(cell.value)<>" " then
 retVal = retVal & cstr(cell.Value) & dlm
 end if
 Next
 If dlm <> "" Then
 retVal = Left(retVal, Len(retVal) - Len(dlm))
 End If
 concat = retVal
 End Function

Answer 4

If you wish to concatenate multiple cells from different sheets, and you also want to add a delimiter between the content of each cell, the most straightforward way to do it is:

=CONCATENATE(Sheet1!A4, ", ", Sheet2!A5)

This works only for a limited number of referenced cells, but it is fast if you have only of few of these cells that you want to map.

Answer 5

You can put an equal formula, then copy it so reference the whole range (one cell goes into one cell)

=Sheet2!A1

If you need to concatenate the results, you'll need a longer formula, or a user-defined function (i.e. macro).

=Sheet2!A1&Sheet2!B1&Sheet2!C1&Sheet2!D1&Sheet2!E1&Sheet2!F1

Answer 6

Its quite simple but not easy to discover --- Go here to read more. its from the official microsoft website

Step 1 - Click the cell or range of the source sheet (that contains the data you want to link to)

Step 2 Press Ctrl+C, or go to the Home tab, and in the Clipboard group, click Copy Button image .

Step 3 Clipboard group on the Home tab

Step 4 Press Ctrl+V, or go to the Home tab, in the Clipboard group, click Paste Link Button. By default, the Paste Options Button image button appears when you paste copied data.

Step 5 Click the Paste Options button, and then click Paste Link .

Answer 7

I rewrote the code provided by Ninja2k because I didn't like that it looped through cells. For future reference here's a version using arrays instead which works noticeably faster over lots of ranges but has the same result:

Function concat2(useThis As Range, Optional delim As String) As String
    Dim tempValues
    Dim tempString
    Dim numValues As Long
    Dim i As Long, j As Long
    tempValues = useThis
    numValues = UBound(tempValues) * UBound(tempValues, 2)
    ReDim values(1 To numValues)
    For i = UBound(tempValues) To LBound(tempValues) Step -1
        For j = UBound(tempValues, 2) To LBound(tempValues, 2) Step -1
            values(numValues) = tempValues(i, j)
            numValues = numValues - 1
        Next j
    Next i
    concat2 = Join(values, delim)
End Function

I can't help but think there's definitely a better way...

Here are steps to do it manually without VBA which only works with 1d arrays and makes static values instead of retaining the references:

1. Update cell formula to something like =Sheet2!A1:A15
2. Hit F9
3. Remove the curly braces { and }
4. Place CONCATENATE( at the front of the formula after the = sign and ) at the end of the formula.
5. Hit enter.

Answer 8

If these worksheets reside in the same workbook, a simple solution would be to name the range, and have the formula refer to the named range. To name a range, select it, right click, and provide it with a meaningful name with Workbook scope.

For example =Sheet1!$A$1:$F$1 could be named: theNamedRange. Then your formula on Sheet2! could refer to it in your formula like this: =SUM(theNamedRange).

Incidentally, it is not clear from your question how you meant to use the range. If you put what you had in a formula (e.g., =SUM(Sheet1!A1:F1)) it will work, you simply need to insert that range argument in a formula. Excel does not resolve the range reference without a related formula because it does not know what you want to do with it.

Of the two methods, I find the named range convention is easier to work with.