How can I reference a range of cells from one sheet to another using excel formulas?

Question

I have one sheet with sheets Sheet1 and Sheet2 and I'm trying to reference a range of cells from Sheet2 to Sheet1

I know how to reference sheet cells, such as =Sheet2!A1 , but how can I do the same for a range of cells, for example A1:F1 I tried =Sheet2!A1:F1 , but it doesn't look like syntax.

I need to use Excel formulas for this, if possible.

Answer 1

Good. Got this, I loaded a custom concatenation function, and then just referenced its cells

the 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 2

Simple ---

I created sheet 2 with 4 cells and sheet 1 with one cell with the formula:

 =SUM(Sheet2!B3:E3) 

Note that, as you said, it makes no sense to assign a Single Cell value from a range. Send it to a Formula that uses a range to do something with it.

Answer 3

If you want to combine several cells from different sheets, and also want to add a separator between the contents of each cell, the easiest way to do this is:

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

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

Answer 4

You can put an equal formula and then copy it so that it refers to the entire range (one cell goes into one cell)

 =Sheet2!A1 

If you need to combine the results, you will need a longer formula or a custom function (i.e. a macro).

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

Answer 5

If these worksheets are in the same book, a simple solution is to name the range, and the formula refers to the named range. To name a range, select it, right-click and specify it with a descriptive name with a workbook area.

For example =Sheet1!$A$1:$F$1 can be called: theNamedRange . Then your formula is on Sheet2! can refer to it in your formula as follows: =SUM(theNamedRange) .

By the way, it is not clear from your question how you wanted to use the range. If you put what you had in the formula (e.g. =SUM(Sheet1!A1:F1) ), it will work, you just need to insert this range argument into the formula. Excel does not allow a range reference without an appropriate formula, because it does not know what you want to do with it.

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

Answer 6

I rewrote the code provided by Ninja2k because I didn’t like the fact that it was looping around the cells. For future use, the version using arrays is used here, which works noticeably faster in many 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 that is definitely the best way ...

Here are the steps for doing this manually without VBA, which only works with 1d arrays and static values ​​instead of saving links:

• Refresh cell formula for something like =Sheet2!A1:A15
• Hit F9
• Remove curly braces { and }
• Place CONCATENATE( at the beginning of the formula after the = sign ) at the end of the formula.
• Press enter .

Answer 7

The formula that you have is fine. But after entering it, you need to press Control + Shift + Enter to apply it to the range of values. In particular:

• Select a range of values ​​in the destination sheet.

• Enter your desired formula in the formula, for example. =Sheet2!A1:F1

• Press Control + Shift + Enter to apply the formula to the range.

Answer 8

Its quite simple, but not easy to find --- Go here to read more . him from the official microsoft website

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

Step 2 Press Ctrl + C or go to the "Home" tab, and in the "Clipboard" group, click "Image of the copy button".

Step 3 Clipboard on Home tab

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

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