Sort Not working (Excel 97/2000)

Topic

New Reply

Hello helpers

My problem is difficult to explain, so I have attached the actual spreadsheet so that you can see what I am talking about.

My Spreadsheet is designed to keep a tally of staff and their Recreation leave entitlements and also to keep record of what leave they plan to take each month. This doubles as a roster system so that at a glance, managers can see who has requested leave in a particular month in a location. Thus ensuring adequate staffing in a location at any one time.

In my spreadsheet I have a main sheet (accruals) with links to sheets for each month of the year. (only 3 months at this stage) There are also links in each monthly worksheet back to the main sheet (Accruals).

The trouble I have is that when I sort the “Accruals” Worksheet by Location. The sort works for the linked cells, but the cells for each day of the month dont move and thus the data is in accurate.

What am I doing wrong?

Kerry

Reply | Quote

Replies

You could just add a blank column between I and J and hide it. Then when you sort the date columns (>K) will not be sorted.

Steve

Reply | Quote

It is the Apr May Sheets that are the problem. I want the data in the cells that are the days of the month 1 through 30 to move with the person, when I sort the Accruals worksheet by Location.

As it is now you can see the person Valma Ahnge at Cell C3 has 7.5 hrs in the April sheet. Go to the Accruals Sheet and Sort by Location and then come back and you will see that she moves, but the 7.5 hours are now next to Brian Dew.

Does this make more sense?

Kerry

Reply | Quote

Don’t link the names and locations on those sheets to the accrural sheets. You are rearranging the names and locations with formulas. but NOT doing anything to the other columns. keep them all “static” I would use a COMBINED name. Then use VLOKKUP to locate the name on that row in ACCRUAL to get the value of interest from that table instead of looking in a stagnant spot.

Steve

Reply | Quote

Ok Steve, I will give that a go. It sounds quite sensible. Thankyou.

Unfortunately I will have to wait till morning to do it as its midnight now and I am brain dead!

Will let you know how it turns out.

Thanks!

Kerry

Reply | Quote

Sadly I have to admit, after giving Steves suggestion a go, I have failed and have absolutely no idea how to do this.

HELP!

Reply | Quote

Steve will no doubt react, but here is a modified workbook. The names and locations in the April and May worksheets are static (not formulas), so they won’t be sorted with the Accruals worksheet.
The totals in the Accrual worksheet are taken from the April and May worksheets by means of VLookup formulas; in my version they look up the last name only, which is dangerous if you have multiple persons with the same last name; it would be safer to use a unique identier like Employee Number or SSN.

Reply | Quote

Thankyou for your response Hans! Sorry for my delay in responding. I have been absorbing what you have done.
It is doing what I wanted except for one thing, which I may have to accept.

When I complete this spreadsheet, it will have a full 12 months in the Accruals and a sheet for each of those 12 months. If I get a new staff person, I will have to add them individually to each monthly sheet.

Is there a way around this? Perhaps a macro?

Once again thankyou.
Kerry

Reply | Quote

If you select the Accruals and month worksheets (click the Accruals tab, Shift+click the last month tab), you can enter data in all selected sheets at once. This will work fine for the first and last names, since they are in the same location on all sheets. For the location and position, you can enter in all twelve months at once, but you’ll have to do Accruals separately. Don’t forget to cancel the group selection (right click a tab for this option). The formulas can simply be copied down.

A macro would be possible too: enter the relevant data in one sheet, for instance the Accruals sheet, and use a macro to copy them to the other sheets. If it doesn’t occur too often, I would probably stick to the manual procedure described above.

Reply | Quote

Hey that’s nifty! I didnt know about that technique. I’ve learnt something new. Thanks Hans!

Reply | Quote

If you’re willing to learn something more just before Easter: selecting sheet tabs works like selecting files in Windows Explorer.

Click, then Shift+Click selects a contiguous range.

Click, then Ctrl+Click allows you to select and deselect individual sheet tabs.

Reply | Quote

Excellent Hans! I am always willing to learn new tricks. It makes me look clever to my boss and one day they may think I am clever enough to pay me more!

Happy Easter

Kerry

Reply | Quote