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

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.

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.

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.

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 .

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.

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.

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

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.