Sorting in worksheet1 affecting worksheet2

**ixpfah** · 03-08-2007, 09:03 AM

Hi,

In my workbook I have several worksheets. In worksheet1 I can plot in new users, like

Please Login or Register  to view this content.

In worksheet2 I can fill in these users' working hours, and the two first columns are linking to the user list in worksheet1.

However, here's the problem: I want the users to be sortet by numbers, from lowest to highest - this isn't a problem. But when I sort the users in worksheet1, that also affects worksheet2, obviously, but only the user number and names. The working hours that I've insertet are not affected by sorting the users in worksheet1, but they definitely should.

I've been googling around trying to find an answer for linking the working hour cells to the name they belong to, but I haven't found an answer.

This should be possible, in my opinion, but I'm not sure how, therefore I'm asking you for help.

If something's unclear, please ask, and I will try to explain better.

I've uploaded a test sheet here.

**robert111** · 03-08-2007, 10:22 AM

assuming your data is in col A and col B

highlight A1:B1000 and name the range "table"

in C1 put =small($a$1:$a$1000,e1)

in D1 put =vlookup(C1,table,2)

in E1 put 1, in E2 put E1 + 1

drag down all the formulas

this gets your list into numerical order, dynamically.

does this assist you?

**ixpfah** · 03-08-2007, 06:14 PM

You may be on to something, but that doesn't quite do what I want.

If you download the sheet i attached to my first post, Test.xls, imagine that the worksheet named "Total" is where you can insert new numbers and names, and also have the total amount of hours (summing up the hours from the worksheed called "Hours"). First off, I don't know if it's possible to have the names and the total hours in the same form like that, but that's what I'm hoping for.

Now, moving on to the "Hour" sheet, the numbers and names are repeated there (being looked up from the "Total" sheet), and if I use your method, they will end up sortet the way they should. However, the hour data plotted into the "Hours" sheet will not follow rearranging of the names. Is there a way to make those cells follow the original name?

To explain better, let's say the name is in cell B1, and this guy's hours in C1. If your sorting method transfers this guy to B7, I want his hours to end up in C7, next to it.

I hope this clearifies the issue, and thanks for any help in advance.

**Bryan Hessey** · 03-08-2007, 07:32 PM

Originally Posted by ixpfah

You may be on to something, but that doesn't quite do what I want.

If you download the sheet i attached to my first post, Test.xls, imagine that the worksheet named "Total" is where you can insert new numbers and names, and also have the total amount of hours (summing up the hours from the worksheed called "Hours"). First off, I don't know if it's possible to have the names and the total hours in the same form like that, but that's what I'm hoping for.

Now, moving on to the "Hour" sheet, the numbers and names are repeated there (being looked up from the "Total" sheet), and if I use your method, they will end up sortet the way they should. However, the hour data plotted into the "Hours" sheet will not follow rearranging of the names. Is there a way to make those cells follow the original name?

To explain better, let's say the name is in cell B1, and this guy's hours in C1. If your sorting method transfers this guy to B7, I want his hours to end up in C7, next to it.

I hope this clearifies the issue, and thanks for any help in advance.

Hi,

I think the attached is what Robert meant, don't use =Sheet!address unless you know that address will not change.

All data is entered in Hours, and the Total sheet formula can be copied down past where your data extends to (currently row 23).

column A of Hours is a named range aCol

column A of total is the formula
=IF(ROW()>COUNTA(aCol),"",SMALL(aCol,ROW()-1))

column B is
=IF(ROW()>COUNTA(aCol),"",VLOOKUP(A2,Hours!A:E,2,FALSE))

column C is
=IF(ROW()>COUNTA(aCol),"",SUM(VLOOKUP(A2,Hours!A:E,3,FALSE)+VLOOKUP(A2,Hours!A:E,4,FALSE)+VLOOKUP(A2,Hours!A:E,5,FALSE)))

or optional way of totalling (in case you get more columns) colun D is
=IF(ROW()>COUNTA(aCol),"",SUM(INDIRECT("Hours!C"&MATCH(A2,aCol,0)&":E"&MATCH(A2,aCol,0))))

hth
---

**ixpfah** · 03-11-2007, 08:43 AM

Originally Posted by Bryan Hessey

All data is entered in Hours

Yes, the hours are, but the names are supposed to be coming from the Total sheet.

Sorry, but I still don't feel I've gotten what I want here.

The sheet called "Total" is supposed to display the total amount of hours a user has been working in total. The other sheets are supposed to be months (January, February and so on), so maybe it was a bit misleading of me to just have one "Hours" sheet, when it actually should be 12 other sheets, in addition to "Total".

I feel it would be the easiest to be able to add new users in the "Total" sheet, and then have the other months look up the usernumbers and names from this sheet (if you think it's better to have a separate "Users" sheet with this information, please let me know).

However, when I add new users with new numbers, they aren't necessarily sorted by number, so I would want to group them in increasing order (I'm thinking about using a macro or something for this). But when the months are looking up the names (which also brings them up sorted, since they're just matchin the "Total" sheet, the already plotted in hours won't be sorted according to the name.

So let's say user1 and user2 are set up like this in the "January" sheet:

Please Login or Register  to view this content.

Now, remember, the usernumber and name are found in "Total".

If I sort the names in "Total" so the numbers increase, "January" would look like this:

Please Login or Register  to view this content.

But as you clearly can see, this isn't correct. User2 had been working 12 hours, not 48. So I want the "hours" cells to be linked to the number and name cells somehow. If the name "User1" changes place, I want his hours to follow to the new row.

I appologize for not being clear enough earlier, and I really apprechiate your effort to help.

Please see my new Test.xls in the included attachment. There I've shown (or tried to

) that there should be several sheets with months, where you're supposed to plot in the amount of hours each person has been working that month. Cells with a yellow background are cells where you can plot in new information.

Thanks again for your help.

**Bryan Hessey** · 03-11-2007, 09:03 AM

Originally Posted by ixpfah

Yes, the hours are, but the names are supposed to be coming from the Total sheet.

Sorry, but I still don't feel I've gotten what I want here.

The sheet called "Total" is supposed to display the total amount of hours a user has been working in total. The other sheets are supposed to be months (January, February and so on), so maybe it was a bit misleading of me to just have one "Hours" sheet, when it actually should be 12 other sheets, in addition to "Total".

I feel it would be the easiest to be able to add new users in the "Total" sheet, and then have the other months look up the usernumbers and names from this sheet (if you think it's better to have a separate "Users" sheet with this information, please let me know).

However, when I add new users with new numbers, they aren't necessarily sorted by number, so I would want to group them in increasing order (I'm thinking about using a macro or something for this). But when the months are looking up the names (which also brings them up sorted, since they're just matchin the "Total" sheet, the already plotted in hours won't be sorted according to the name.

So let's say user1 and user2 are set up like this in the "January" sheet:

Please Login or Register  to view this content.

Now, remember, the usernumber and name are found in "Total".

If I sort the names in "Total" so the numbers increase, "January" would look like this:

Please Login or Register  to view this content.

But as you clearly can see, this isn't correct. User2 had been working 12 hours, not 48. So I want the "hours" cells to be linked to the number and name cells somehow. If the name "User1" changes place, I want his hours to follow to the new row.

I appologize for not being clear enough earlier, and I really apprechiate your effort to help.

Please see my new Test.xls in the included attachment. There I've shown (or tried to

) that there should be several sheets with months, where you're supposed to plot in the amount of hours each person has been working that month. Cells with a yellow background are cells where you can plot in new information.

Thanks again for your help.

Hi,

so what you really need in C2 (and downwards) is of the form

=VLOOKUP(A2,January!A:G,7,FALSE)+VLOOKUP(A2,February!A:G,7,FALSE)+VLOOKUP(A2,March!A:G,7,FALSE)

but set correctly as an array lookup?

---

**ixpfah** · 03-13-2007, 08:16 AM

Originally Posted by Bryan Hessey

Hi,

so what you really need in C2 (and downwards) is of the form

=VLOOKUP(A2,January!A:G,7,FALSE)+VLOOKUP(A2,February!A:G,7,FALSE)+VLOOKUP(A2,March!A:G,7,FALSE)

but set correctly as an array lookup?

Do you mean in the "Januray", "February" and so on sheets? Because if so I can't get it to work.

I may not be as savvy with Excel as I first thought I was (since I had to check out what an array is, for instance), but I'm still learning.

**Bryan Hessey** · 03-13-2007, 09:35 AM

Originally Posted by ixpfah

Do you mean in the "Januray", "February" and so on sheets? Because if so I can't get it to work.

I may not be as savvy with Excel as I first thought I was (since I had to check out what an array is, for instance), but I'm still learning.

I'm confused.

from your earlier post "The sheet called "Total" is supposed to display the total amount of hours a user has been working in total. The other sheets are supposed to be months (January, February and so on), so maybe it was a bit misleading of me to just have one "Hours" sheet, when it actually should be 12 other sheets, in addition to "Total"."

Is there a need for a separate sheet for each month? - or do you use a separate column as was shown in your file Test.xls.

If you have 12 sheets then the statement to Lookup each sheet in turn will be quite lengthy. If you have 12 columns you can always Total the columns and then use a single VLookup per user number.

12 sheets?
----

**ixpfah** · 03-13-2007, 07:04 PM

All right, I'll try to be as clear as I can. I was only using Test.xls as a simple example, but sometimes I suppose the simple isn't necessarily the best.

So just forget about Test.xls and the other .xls I've uploaded, and check out the attachment to this post.

The point of the workbook is to be able to plot in how many hours each person has been working each day each month (so 30/31 days, and 12 sheets). So yes, the month sheets are necessary. The total sheet is just supposed to show the total of each user - how many hours has he or she been working so far.

The problem is this:
If I add a new user with a lower user number (index number or what you want to call it) that the highest one at the moment, I want all the month-sheets to be updated so that the new user is inserted in a new, empty row, in the correct position.

For example: If I have 4 user with the numbers 1000, 2000, 3000 and 5000, and I decide to add another one with the number 4000, I want him to be inserted before number 5000, and with the posibilities to add new worked hours-data in an empty row (thus shifting the users with a higher number one step down to make room for the new row). Manually, this would be fairly easy to do, simply insert a new row in the disired place. But if done "automatically" whenever I add a new user, I have problems making this happen. If this could be done by macros, that's fine, I just need to know how. I don't know very much VB programming, but maybe there's a way to check the user numbers to see where the new number fits in, and then insert the new row there...?

Again, I'm terribly sorry for any confusion I've made.

Thanks again.

**Bryan Hessey** · 03-13-2007, 07:30 PM

Originally Posted by ixpfah

All right, I'll try to be as clear as I can. I was only using Test.xls as a simple example, but sometimes I suppose the simple isn't necessarily the best.

So just forget about Test.xls and the other .xls I've uploaded, and check out the attachment to this post.

The point of the workbook is to be able to plot in how many hours each person has been working each day each month (so 30/31 days, and 12 sheets). So yes, the month sheets are necessary. The total sheet is just supposed to show the total of each user - how many hours has he or she been working so far.

The problem is this:
If I add a new user with a lower user number (index number or what you want to call it) that the highest one at the moment, I want all the month-sheets to be updated so that the new user is inserted in a new, empty row, in the correct position.

For example: If I have 4 user with the numbers 1000, 2000, 3000 and 5000, and I decide to add another one with the number 4000, I want him to be inserted before number 5000, and with the posibilities to add new worked hours-data in an empty row (thus shifting the users with a higher number one step down to make room for the new row). Manually, this would be fairly easy to do, simply insert a new row in the disired place. But if done "automatically" whenever I add a new user, I have problems making this happen. If this could be done by macros, that's fine, I just need to know how. I don't know very much VB programming, but maybe there's a way to check the user numbers to see where the new number fits in, and then insert the new row there...?

Again, I'm terribly sorry for any confusion I've made.

Thanks again.

Hi,

ok - you need to not use Row numbers, but do a Lookup to see which row the person is on, for each month.

thus your
=Januar!AI8+Februar!AI8+Mars!AI8+April!AI8+Mai!AI8+Juni!AI8+Juli!AI8+August!AI8+September!AI8+Oktober!AI8+November!AI8+Desember!AI8

etc all need to be VLookup, but, because VLookup returns #N/A for items not found you need to test and then VLookup.

so replace Januar!AI8 with
IF(ISERROR(VLOOKUP(A4,Januar!A:AJ,36,FALSE)),0,VLOOKUP(A4,Januar!A:AJ,36,FALSE))

and replace Februar!AI8
IF(ISERROR(VLOOKUP(A4,Februar!A:AJ,36,FALSE)),0,VLOOKUP(A4,Februar!A:AJ,36,FALSE))

and the same for each other portion.

As you see, this will make a very long formula, which you need to do twice, for columns C & D of yout Total sheet.

This is why I asked about the 12 columns (per set = 24 columns for our Total sheet), would you prefer to use 24 columns each with a separate lookup to the Month sheet, or are you happy with the single long formula?

=IF(ISERROR(VLOOKUP(A4,Januar!A:AJ,36,FALSE)),0,VLOOKUP(A4,Januar!A:AJ,36,FALSE))+IF(ISERROR(VLOOKUP(A4,Februar!A:AJ,36,FALSE)),0,VLOOKUP(A4,Februar!A:AJ,36,FALSE))+IF(ISERROR(VLOOKUP(A4,Mars!A:AJ,36,FALSE)),0,VLOOKUP(A4,Mars!A:AJ,36,FALSE))+ etc etc

hth
---
added, AI & AJ are columns 35 & 36 for each of two formula, I appear to have mixed them, use 35 for AI and 36 for AJ
---

**ixpfah** · 03-14-2007, 01:53 PM

Right. But does this also apply for the month sheets? Not only for the Total sheet? Because I want the months to be updated as well.

**Bryan Hessey** · 03-14-2007, 10:57 PM

Originally Posted by ixpfah

Right. But does this also apply for the month sheets? Not only for the Total sheet? Because I want the months to be updated as well.

Hi,

there is something very wrong with what you are trying to do.

Correct procedure is,
on a Month sheet,

Type in the id number (do not pick this from the master sheet), and type in the figures C:AG (as you are doing now.

In column B do a Lookup on the number that is in column A to get the name from the Master Sheet, viz

=VLOOKUP(A4,Totaloversikt!A:B,2,FALSE)

you can formula fill that for all rows with data in column A (by just drag the + down) or use

=IF(A4="","",VLOOKUP(A4,Totaloversikt!A:B,2,FALSE))

and formula fill that as far as you want.

This same formula can be used in all month sheets.

If you want to sort the Month sheets when you add a new name, or you want to insert a row to have the numbers in order you just do.

See the attachment total and January where amended.

Let me know how you go.

---