You aren't going to want to hear this, but you should really, really, really change your workbook layout.
Like-data shouldn't be split apart by Name (or Part Number, Day, Week, Month, Year, etc.).
Instead, add a column for employee name. With everyone's data in a normalized master list, reporting for individuals will be very quick and simple. It will also be easy to pull data for a particular week (or month or quarter). You can look at data for the whole group, or be department or by employee.
Excel has a ton of built in reporting and analysis tools, but you need to have your data stored properly to use them.
For your current predicament, you'll need to use a macro (VBA) to get the list of all sheets in your workbook. Are you familiar with VBA?
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
Have the following on each sheet and reference the summary sheet to that cell on each sheet:
=UPPER(MID(CELL("filename", A1), FIND("]", CELL("filename", A1))+1, 100))
3rded to Skip & John and might I just say <cough>faq68-2561</cough>
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Zack Barresse kindly gave me this user defined function which I have been using ever since. Add this code to VBA module and you will find it under user defined functions.
Your formula will look like this:
=PERSONAL.XLS!Sheet(Row())
.. for the workbook the formula is entered in, change it to this ...
CODE
Public Function Sheet(wsIndex As Long) As String
Dim wb As Workbook
On Error Resume Next
Application.Volatile True
Set wb = Workbooks(Application.Caller.Parent.Parent.Name)
Sheet = "ERROR!"
Sheet = wb.Worksheets(wsIndex).Name
End Function
This will now handle an invalid sheet index number and use only the parent workbook.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.