Accessing Other Sheet Tabs from within Excel

E

Eric

Is there any easy way to refer to sheets without knowing the name of the sheet?

e.g. A summary sheet that collates details for the company from all other
sheets within the workbook which may be different branches and/or personnel.

These extra tabs may be added or have their tab name changed at any time,
and I do not wish to redo the summary sheet each time.
Ideally I would like to know how many other sheets there are are and to be
able to reference instead of 'Freds Sheet'!.$A$7 to be something like
WorkSheet[3]!.$A$7, with some method of checking the range of sheets - the
summary could always be the first sheet.

The sheets, other than the summary, would all be in the same format, so that
the data required would be accessed from 'fixed' cells.
 
E

Eric

Thanks for your info, I haven't had chance to try it yet, but I was hoping to
be able to code a cell with something of the order:-
=if(Row()>Worksheets.Count,"",Worksheet[Row()]!$A$7)
instead of
='Freds Sheet'!$A$7

Is this a possibility?
 
T

Tom Hutchins

If I understand correctly, you want a function you can enter in, say, A2 on
your summary sheet and copy down. On row 2, it would pull the value in A7
from the second sheet in the workbook. On row 3, it would pull the value in
A7 fromthe third sheet, etc.

If you are always pulling numeric values from the other sheets, you can use
this used-defined function (UDF):

Public Function GetShtVal(CurrRow As Long, GetRng As String) As Variant
On Error GoTo GSVerr
If CurrRow > ThisWorkbook.Sheets.Count Then
GetShtVal = vbNullString
Else
GetShtVal = ThisWorkbook.Sheets(CurrRow).Range(GetRng).Value
End If
Exit Function
GSVerr:
GetShtVal = Error()
End Function

In A2 (for example) on your summary sheet, you would enter
=GetShtVal(ROW(),"A7")+(NOW()*0)

The Now()*0 is necessary to force Excel to recalculate the formula (the UDF
won't automatically recalc).

If you are always pulling text values, you can use the same UDF, but the
fomula on the worksheet will have an ampersand instead of the plus sign:
=GetShtVal(ROW(),"A7")&TEXT(NOW()*0,"")

If you have a mix of values & text, we can handle that with a second UDF:

Public Function TxtOrVal(InTxt As String) As Variant
If Len(InTxt) = 0 Then
TxtOrVal = vbNullString
Exit Function
End If
If Len(Str(Val(InTxt))) < Len(InTxt) Then
TxtOrVal = InTxt$
Exit Function
End If
TxtOrVal = Val(InTxt)
End Function

In A2 (for example) on your summary sheet, you would enter
=TxtOrVal(GetShtVal(ROW(),"A7")&TEXT(NOW()*0,""))

The user-defined functions need to be in a general VBA module in the
workbook where you want this to work. If you are new to user-defined
functions (macros), this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch

Eric said:
Thanks for your info, I haven't had chance to try it yet, but I was hoping to
be able to code a cell with something of the order:-
=if(Row()>Worksheets.Count,"",Worksheet[Row()]!$A$7)
instead of
='Freds Sheet'!$A$7

Is this a possibility?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top