Automatically keeping rows sorted in alphabetical order

**damiensmith212** · 04-23-2009, 06:17 PM

Hi,

I work for a company supplying staff to railway engineering sites and have been trying to work out how to have our planning spreadsheet provide management info such as number of each competency used per client, profit per client, etc.

I have a tab for management info, then 15 tabs named Sheet01-15 (one per client, the idea being that our consultants rename the tab to the client's names) which have our planning sheets on.

Cell A2 of every client tab has the name of the client. On the management info tab i have the following:

Column A (Names of the clients)
='Sheet01'!$A$2
='Sheet02'!$A$2
='Sheet03'!$A$2
='Sheet04'!$A$2
='Sheet05'!$A$2

Column B (# of each competency, in this case a Trackman aka PTS)
=COUNTIF('Sheet01'!B:B,"TM*")+COUNTIF('Sheet01'!B:B,"pts*")
=COUNTIF('Sheet02'!B:B,"TM*")+COUNTIF('Sheet02'!B:B,"pts*")
=COUNTIF('Sheet03'!B:B,"TM*")+COUNTIF('Sheet03'!B:B,"pts*")
=COUNTIF('Sheet04'!B:B,"TM*")+COUNTIF('Sheet04'!B:B,"pts*")
=COUNTIF('Sheet05'!B:B,"TM*")+COUNTIF('Sheet05'!B:B,"pts*")

And so on for the various competencies that I need to total up. The idea is that I have a table on the management info page with each row having columns A-O showing 'Client Name, # of competency 1, # of competency 2' and so on.

My problem is that while 'Sheet01', 'Sheet02' in the formulas change when the tabs are renamed to match the client name, if after renaming 'Sheet01' to 'A Client', 'Sheet02' to 'B Client' and 'Sheet03' to 'D Client', they then need to make 'C Client' they have no choice but use 'Sheet04'. This means that Column A on the management info tab will no longer have the clients in alphabetical order.

I have an example spreadsheet with some fake data in to show what I mean but I can't seem to upload it. If someone could tell me why not I'll try and fix it and show you the spreadsheet.

If anyone knows a way of keeping the clients on the management info page alphabetical I'd appreciate knowing.

The reason I use premade Sheet01-Sheet15 tabs is because I couldn't find a way to have rows on the management info tab be automatically created and filled with the formulas whenever a new client tab was created. If there is a way then that might make this problem irrelevant.

Thanks in advance for your help.

**protonLeah** · 04-23-2009, 10:35 PM

Hopefully the sample file is less than 1 MB in size. What steps did you attempt? What kind of error message did you get while uploading?

**Mallycat** · 04-23-2009, 10:36 PM

It's a bit hard to know exactly what you are doing without seeing a sample of the client sheets you have built. But my intuition tells me that you may be better off to create a single list of client data on one sheet, and then use a pivot table over that list to get the data you want. If you post a sample of the client sheet and PM me, I will take a look

**damiensmith212** · 04-24-2009, 04:51 AM

Thanks for getting back to me.

The original planner design with the tab per client system was one that I was told to use when I started here. My job consists of just filling jobs, nothing to do with the systems used so I have very little input on the layout and design of the spreadsheets. I've just taken this on as a side project after seeing that the higher ups were manually calculating all their needed info by hand, and working through each clients planners manually. It just seemed ridiculous to me so I thought I'd have a go at improving it.

Any changes I make that are approved by the directors will be rolled out across all our offices but I have to make any changes as transparent as possible to the end user. The single tab per client system I'll be unlikely to have changed as each tab is printed out seperately for usage by payroll, by on call managers, etc.

My main focus really is to have everything calculated and available to the management on a protected single tab (MGNT INFO) while allowing the consultants to freely edit (within reason) the client planner tabs without screwing up anything on the planner tabs that would affect the formulas on MGMT INFO.

The layout of the planners on the client tabs are identical for all clients but the number of rows will be adjusted depending how many operatives are used on a particular job. You'll see what I mean on my example sheet, which now that I'm at work seems to be uploadable.

Thanks again for your help.

Edit:

Almost forgot to add - the sample spreadsheet has been set up as if 3 clients with names beginning with A, B and D have been added to the client sheets and used (using Sheets 01, 02 and 03.) The problem then being that if a client whose name begins with C is created, using Sheet04 and the tab dragged between client B and client D's tabs, although the tabs will be in alphabetical order along the bottom of Excel's screen (Client A, B, C, D), their entries on MGNT INFO will be in Sheet 01-04's original order. This means that they'll be in Client A, B, D, C order.

The sample spreadsheet is as it would be after using clients A, B and D, and just after creating client C, but before client C was used in any way. I'm using Excel 2003 if that makes any difference to any potential solution.

**Mallycat** · 04-24-2009, 07:10 AM

Let me have a look over the weekend. I have a long flight and this may give me something to do :-)

**damiensmith212** · 04-24-2009, 07:31 AM

Thanks, it's very appreciated.

I'm starting to think I've bitten off more than I chew at the moment though because now I've started, I'm being asked to include even more information!

I'm being asked if it's possible to add things such as shifts ordered within a reasonable time of the shift commencing, shifts ordered last minute, shifts cancelled outside 24hrs, inside 24hrs, number of shifts offered by client, number of shifts we were able to fill, how many times the client supplied a purchase order late (after the shift commenced) and so on. Most of that information isn't even available without a redesign of the client planners and the consultants populating the planners with said information.

The alphabetically ordered rows seems to be the least of my worries now!

I'm sure I'll muddle through though, but I expect I'll have to ask a fair few questions along the way.

Thanks again!

**Mallycat** · 04-24-2009, 04:27 PM

Given you want to keep the structure similar to what you have already got, and now that I have a sample, here are my suggestions (well what I have done for you actually).

1. I have changed cell A2 in the first 4 client sheets so that they are a custom formula. When you change the tab name, this cell will change automatically to match the tab name.

2. on the management info sheet
I have added a hidden column C. Take a look at it. It creates part of the formula needed to do the calculations on this page

I have added 2 hidden rows 6 and 7. These are also used to do the calculations.

I then used the =indirect() formula to refer the the hidden column, and other parts of the formula to point to the hidden rows. Using the =indirect() you can create a formula, but get some of the cell references in that fromula you are writing from a cell, rather than manually add them in the formula.

Have a look at the formula in D8. It references the hidden rows and columns but basically is exactly the same as you had before. The big advantage now is that you can copy the formula from this cell into the entire range D8:P22. Try it.

Now here's the trick. You now have to type the client names into the range B8:B22, but you only have to add them once you create the tab. Just make sure the client names are identical in these cells and on the tabs. Then click the sort button I have added. This should resort them for you. Now be careful here. All the sort button does is sort the range B8:B22 without sorting the surrounding cells. I created this sort macro in Excel 2007 and you are using Excel 2003. So I don't know if it will work as expected. Just make sure you check it. Just change the clients names manually, then try to resort it.

Another word of warning, do not drag and drop the clients names in range B8:B22. There are formulae pointing to these cells, and if you drag and drop the contents to another cell, it will create havoc.

Finally, you may want to remove the footer that has been added (Classified - Internal). The PC I am using adds that automatically.

Let me know if this is what you were after. Sorry to do it for you rather than coach you, but there were quite a few layers of things to do.

**Mallycat** · 04-24-2009, 04:56 PM

The indirect function is a bit hard to explain. So here is a sample sheet if you are interested

**damiensmith212** · 04-24-2009, 06:50 PM

Thanks for taking the time to look at this. I can appreciate that it would have taken a lot longer to walk me though the process than to just do it. The indirect function example sheet was very helpful. I'd seen examples of it before but I hadn't been able to get my head around it. Your sheet was very easy to follow.

To be honest though, the clients being in alphabetical order is less of an issue than having the management info tab completely off limits to the end users.

Having the need to input the client names on to the management info tab as well as on the planners makes it far too easy for them to just ignore the management info tab and make the whole system pointless. This is why I'd worked out the premade Sheet01-15 system with the client names automatically pulling through from cell A2 of each planner tab. There probably is a better way of achieving this but I couldn't think of another way. Having Cell A2 autofill with the tab name is nice though.

I didn't actually get chance to try out your macro sort function because when I click the sort button I get '"Run-time error '438': Object doesn't support this property or method". This might be the problem between 2003 & 2007 you anticipated.

Is it possible to have the spreadsheet laid out like yours, including the sort macro (although it can be ommitted if it has to for the client names to be auto-filled based on the client name on the tabs) yet need no input whatsoever directly on the management info tab?

Apologies if this makes it more complicated but I'm trying to make it as idiot-proof as possible. If I could make it so the only fields that needed to be/were able to be edited were the tab/client name and the empty cells within the planner I would.

I really appreciate you taking the time to help me out. Thanks again.