Hi,
I am trying to create a function that will take a Sheet Name and
location "A2" and return the contents of that cell.

Secondly I would like to create a function that will take a Sheet Name
and location "A2" and return the height of the cell.

Thanks - mike

Function WhatsInIt(r1 As Range, r2 As Range) As Variant
WhatsIt = Sheets(r1.Value).Range(r2.Value).Value
End Function


Function HowTall(r1 As Range, r2 As Range) As Variant
HowTall = Sheets(r1.Value).Range(r2.Value).RowHeight
End Function

In an arbitrary worksheet, put:
Sheet1 in A1 and
Z100 in cell A2

Then =WhatsInIt(A1,A2) will return the contents of cell Z100 in Sheet1
and
=HowTall(A1,A2) will return the height of cell Z100 in Sheet1

--
Gary''s Student - gsnu2007g


"mike" wrote:

Hi,
I am trying to create a function that will take a Sheet Name and
location "A2" and return the contents of that cell.

Secondly I would like to create a function that will take a Sheet Name
and location "A2" and return the height of the cell.

Thanks - mike

On Apr 1, 12:07*pm, Gary''s Student
wrote:
Function WhatsInIt(r1 As Range, r2 As Range) As Variant
WhatsIt = Sheets(r1.Value).Range(r2.Value).Value
End Function

Function HowTall(r1 As Range, r2 As Range) As Variant
HowTall = Sheets(r1.Value).Range(r2.Value).RowHeight
End Function

In an arbitrary worksheet, put:
Sheet1 in A1 and
Z100 in cell A2

Then =WhatsInIt(A1,A2) will return the contents of cell Z100 in Sheet1
and
=HowTall(A1,A2) will return the height of cell Z100 in Sheet1

--
Gary''s Student - gsnu2007g



Thanks!!
Is there a way to modify these functions so I can pass in the r1 and
r2 values directly instead of from a table? Thanks - mike

Function WhatsInIt(r1 As Range, r2 As Range) As Variant
WhatsIt =
Sheets(r1.Value).Range(r2.Value).Value <---
End Function

Also how do you handle filenames with spaces. Thanks

We can make versions of the functions that use data directly rather than
getting the data from cells. For example:

=WhatsInIt2("Sheet3","Z100") instead of the original forms.

Check back tomorow for an update to this post.
--
Gary''s Student - gsnu2007g


"mike" wrote:

Also how do you handle filenames with spaces. Thanks