Sorting lists

[JoaoRamos]

I have a list of staff working in teams:

Team Name Sun Mon Tue Wed
A Name1 0 8 8 8
A Name2 8 8 8 8
B Name3 0 8 8 8
B Name4 0 8 8 8

Using a "validation" list I want users to choose Team and Day. Ie, Team = A and Day = Mon, would produce the list:

Name1 8
Name2 8

A diferent user chooses Team=B and Day=Sun and gets
Name3 0
Name4 0

I want the results to be imported onto a spreadsheet in which I don't want rows to be hidden (ie using Filter). The page would look:

Spreadsheet

Team validation
Day validation

Info List of names list of hours
info " " " " " "
info " " " " " "


Does it make sense??

[davesexcel]

checkout the attatchment at this thread, it maybe what you are referring to

http://www.excelforum.com/showthread.php?t=579129

also

http://www.excelforum.com/attachment...6&d=1163069750

[JoaoRamos]

Hi Dave,

thanks for your help. It's not so much the "tidying up" the asnwers, but the return value of the lookup. Lookup returns one value, but what I'm after is a lookup that returns a list. And not one that has to be manually sorted (as in Filter, because Filter hides rows and I don't want that).

[VBA Noob]

Hi,

You could try this record code

The user filter's the criteria then run#s the acro which makes a copy from Sheet 1 to Sheet 2

Please Login or Register  to view this content.

VBA Noob

[davesexcel]

Hi Dave,

thanks for your help. It's not so much the "tidying up" the asnwers, but the return value of the lookup. Lookup returns one value, but what I'm after is a lookup that returns a list. And not one that has to be manually sorted (as in Filter, because Filter hides rows and I don't want that).

Vlookup returns one value in each cell just change the column number in the formula

same as sumproduct just change the the references

Here's an example workbook the uses VBA to search for a value and pastes all the rows that have that value into another sheet, click on the button that reads click here for po number, when the user form opens it will read enter name, I just noticed that, just type in a number from the po column

[JoaoRamos]

Cheers chaps. I've gone with the macro approach, created a button for each team which Autofilters the team members. This macro then copy/pastes the team to the front sheet and next to it there's a vlookup that selects the number of hours based on the week day. (Used a validation window and a nested If statement, ie =IF(b1="SUN",1,IF(b1="MON",2,IF(b1="TUE",3 etc) that gives me the column for the vlookup).

Thanks for the help,
Joao

[VBA Noob]

Glad to sorted your problem


PS. If the Sun etc are dates formatted as day you could use this to return your number

=CHOOSE(WEEKDAY(B2),1,2,3,4,5,6,7)

VBA Noob