How do I retrieve the contents of a Cell from different sheets?

[mike]

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

 

[Gary''s Student]

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

 

[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

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

 

[mike]

Also how do you handle filenames with spaces. Thanks

 

[Gary''s Student]

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.