Linking a List to page 1

[DrewV]

I have a multi page employee hours spreadsheet with other misc info. I have approx 80 employees and I have set up my main page as lookups to keep the cells working together but I need a way of having the first list return all of the non duplicate names. Each name will only appear once on any given spreadsheet but people will be added and I need a way (preferably a formula not a macro) to return one list in alphabetical to my Employee Spreadsheet.

I am using Excel 2003

Thank you

Drew

 

[Smitty]

Welcome to the Board!

Are the employee names somewhere consistent, like the sheet name or a cell? If so you can loop through each employee worksheet and build a list of names. Here's an example:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> ws1 <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>        <SPAN style="color:#00007F">Set</SPAN> ws1 = Sheets("Master")<br>        <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Master" <SPAN style="color:#00007F">Then</SPAN><br>                ws1.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = ws.Name<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

If need be you can easily sort the list once it's been built.

HTH,

 

[DrewV]

Is there a way to post up my spreadsheet here for an Idea? I am well versed with a lot of functions, is that a macro?

How do I edit it?

 

[Smitty]

If you follow the HTML maker link in my sig you'll be able to post a shot of your sheet.

As for what I posted, that's VBA (Visual Basic for Applications) code. It's a subset of the progragamming language that's used to create Office, and it's included with most Office applications.

To use it, hit ALT+F11 to open the VBE (Visual Basic Editor), then Insert-->Module and paste the code in the new window that opens on the right. You can use F5 to test it, but you'll want to do it on a copy of your workbook, as code can't be undone once it's been run. ALT+Q will take you back to Excel.

If you have two monitors, I use one for the VBE and the other for Excel, that way you can see what's going on in Excel when you run your code.