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

excel

Solution

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

Problem

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.

Original source