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

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

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

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

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

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

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

Also how do you handle filenames with spaces. Thanks

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

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