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

1100 Name1
1120 Name2
3319 Name3
1410 Name4

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:

Numb Name    Hours
2200 User1   48
1100 User2   12

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:

Numb Name    Hours
1100 User2   48
2200 User1   12

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:

Numb Name    Hours
2200 User1   48
1100 User2   12

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:

Numb Name    Hours
1100 User2   48
2200 User1   12

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?

---