Worksheet Name as argument in Custom Function
 

Thanks, but sorry to say that made it worse. It doesn't
work with either the string name or a cell reference when
I use the "Evaluate" function.
-----Original Message-----
Use Evaluate for your sheet name parameter:

Function Get_Data(rname, sname, row)
Get_Data = Sheets(Evaluate(sname)).Range(rname).Cells
(row)
End Function

Regards,

Dag Johansen

-----Original Message-----
I created a simple function to retrieve data out of
various one-dimensional ranges. The three arguments
passed are the range name, the worksheet name, and the
row
number. When using this function in a spreadsheet I can
use either "range_name" or a cell reference to a cell
containing that string for the range name, and likewise
can use a number or a reference to a cell containing the
number for the row-number argument, but for the
worksheet
name argument it will let me use "worksheet_name" as a
string, but won't let me use a cell-reference to a cell
containing that string. Have tried several different
times thinking maybe it's a simple syntax error on my
part. Maybe it is but I cannot find it. Here is the
function. Again, whenever I use a cell reference to a
cell containing the worksheet name for the "sname"
argument, it returns #VALUE! Help!, and of course many
thanks!


Function Get_Data(rname, sname, row)
Get_Data = Sheets(sname).Range(rname).Cells(row)
End Function

This works
=Get_Data($C4,"Data_Sheet",$E4)
This doesn't
=Get_Data($C4,$D4,$E4)


.

.

Worksheet Name as argument in Custom Function
 

YOu need to check what you have

Function Get_Data(rname, sname, row)
If TypeName(sname) = "Range" Then
sNm = sname.Value
Else
sNm = sname
End If
Get_Data = Sheets(sNm).Range(rname).Cells(row)
End Function


both of these worked for me:
=Get_Data($C4,"Data_Sheet",$E4)

=Get_Data($C4,$D4,$E4)

--
Regards,
Tom Ogilvy


"Larry D" wrote in message
...
Thanks, but sorry to say that made it worse. It doesn't
work with either the string name or a cell reference when
I use the "Evaluate" function.
-----Original Message-----
Use Evaluate for your sheet name parameter:

Function Get_Data(rname, sname, row)
Get_Data = Sheets(Evaluate(sname)).Range(rname).Cells
(row)
End Function

Regards,

Dag Johansen

-----Original Message-----
I created a simple function to retrieve data out of
various one-dimensional ranges. The three arguments
passed are the range name, the worksheet name, and the
row
number. When using this function in a spreadsheet I can
use either "range_name" or a cell reference to a cell
containing that string for the range name, and likewise
can use a number or a reference to a cell containing the
number for the row-number argument, but for the
worksheet
name argument it will let me use "worksheet_name" as a
string, but won't let me use a cell-reference to a cell
containing that string. Have tried several different
times thinking maybe it's a simple syntax error on my
part. Maybe it is but I cannot find it. Here is the
function. Again, whenever I use a cell reference to a
cell containing the worksheet name for the "sname"
argument, it returns #VALUE! Help!, and of course many
thanks!


Function Get_Data(rname, sname, row)
Get_Data = Sheets(sname).Range(rname).Cells(row)
End Function

This works
=Get_Data($C4,"Data_Sheet",$E4)
This doesn't
=Get_Data($C4,$D4,$E4)


.

.