[mkral]

Forgive me if this is an elementary question, but I'm a long-time appleworks user, making the jump to Microsoft office. I searched the archives, but could not find an answer to this question.

I am trying to create a spreadsheet with 2 sheets. The first sheet would be an address book, with rows like: first name, last name, address, phone, etc
I'd like the second sheet to be an attendance sheet, with the first two columbs being the same as the address sheet (first & last names)& then the rest being a chart where I can place an x if that person was in attendance that week. So far so good. What I'm trying to do & where the problem is coming in is that I would like the attendance sheet to auto-update when I enter a new name address into the address sheet. So if I enter a new name & alphabetically it is the 15th name on the list, I would like the attendance sheet to put this name in the 15th slot & move all of the rest of the names down one slot (15th becomes 16th, etc), but I would also like the rest of the rows to shift with it, so that the attendance sheet is accurate (for example, if there is a new name in row 15, I don't want the new person to have the old person's attendance record & everyone from that 16 on down to have incorrect attendance information.

Thanks in advance for any help you can offer.

[philm]

This seems non-trivial to me. Why have a separate sheet for their attendance record? You might be better trying to do this on one sheet. You could do what you request using LOOKUP functions, but I would tend to opt for simpler solutions unless you need to use two sheets for some reason.

[mkral]

I would use both the address sheet & the attendance sheet fairly regularly. I was trying to use two sheets so that I don't have to scroll a lot to the left or right to access data (or hide columns-as in hiding the addresses, so that I can see the names when I do the attendance & don't have to continue to page laft to right to see the name, then the week to place the x) (If any of this makes any sense)

[philm]

You need to 'freeze panes'. Grab the little blue widget at the bottom right corner of the window and drag to the left until the line goes between columns B and C - this assumes your name information is in A and B. Then, select Windows...Freeze Panes from the menu. You can then scroll around in the right hand pane, leaving the name information always in view.

http://homepage.mac.com/philmor/general/excel1.jpg


http://homepage.mac.com/philmor/general/excel2.jpg

[NO OVERSIZE INLINE IMAGES! -- tooki]

[mkral]

Thank you. I've frozen the panes. But I'm not sure what exactly this does. I have frozen them on both the attendance & address eheets (everything has been frozen except for the first & last names)

Will this allow me to update names on the address sheet & have them updated on the attendance sheet, or will this protect the attendance data, allowing it to move whenever a new name is manualy entered.

Thanks again for your help.

[mkral]

please disregard the last post, I now understand it perfectly & that is a great way to do it, now everything is on one sheet. Thanks again for your kind help.

[Shades of Gray]

Also, if you want to automate the sorting (to make sure all data is retained in proper row, you can use the following code. This assumes that you have defined a dynamic named range (myData), and it will automatically enlarge in both directions as you add names (rows) or attendance records (columns). And this assumes that the FirstName heading is in cell B4 and LastName heading is in C4.

On the menu bar, go to Insert > Name > Define:

Then in the name box, type: myData

In the Refers to box, put this:

=OFFSET(Sheet2!$B$4,0,0,COUNTA(Sheet2!$B:$B),COUNT A(Sheet2!$4:$4))

Click ADD, then OK.

Then put this code into the VBE window (ALT + F11).

Code:
Sub myData()
    Range("myData").Sort Key1:=Range("myData").Cells(2, 1), _
    Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

This will sort on the LastName column (column C).

Note that this leaves Rows 1-3 and Column A blank. Such a design will allow even better utilitization of Excel's functions/capabilities.

If this macro works, then you could put a Forms button on the sheet and attach this macro to it. That way you could just hit the button to complete the sort.

[mkral]

Thanks! I'll add that in

[Shades of Gray]

Just a note: in my experience, it is best to work on a copy and thoroughly test, especially with VBA code, since once it runs you can't (easily) undo it.

[larkost]

This is something that you should be using a database like Filemaker for. The spreadsheet idea has to be stretched to the breaking point (where maintenance is constant) to accommodate this, while it would be a trivial program to make in FileMaker Developer, and someone with that version could give you the runtime for free.