Excel function help

[LGMan]

Hi,
I've got an excel workbook with 2 worksheets.
Sheet 2 has 2 columns. Col A contains the Company Unit code, such as 'ABC123' while Col B holds the employee IDs, there usually between 20 and 25 employees per Company Unit code.

Is there any function that can be used on sheet 1 to obtain all the employee codes for whatever Company Unit Code I choose?

I need to grab all the Employee codes to appear in a data validtion list that then drives a host of other things.

 

[xlbo]

Best bet is to create a dynamic range over your list of employee numbers:

In Insert>Name>Define, enter a name for your range (I used dr_EmpList) and then in the "Refers To" box rather than selecting a range enter the following:

=OFFSET(Sheet2!$B$1,MATCH(Sheet1!$A$1,Sheet2!$A:$A,0)-1,,COUNTIF(Sheet2!$A:$A,Sheet1!$A$1),1)

Where your Company Unit Code is entered into Sheet1 A1

In your data Validation, choose a type of "List" and set the reference to =dr_EmpList

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gruuuu]

For a purely formula driven approach:

I will assume these things:
Company Unit Code is typed into cell A1
The sheets are named Sheet1 and Sheet2
The Data Validation list starts at cell $O$1
If your setup differs, you will have to modify the formulas.

In a cell N1:

=COUNTIF(Sheet2!$A:$A,$A$1)

In cell O1:

=IF(ROW()>$N$1,"",INDEX(Sheet2!$B:$B,MATCH($A$1,Sheet2!$A:$A,0)+ROW()-1))

And copy the formula down to, say, O40 (over the expected maximum results)
you may need to change the reference to "$A$1" in cell N1, the reference to the sheet names in column O, and the final "1" in those formulas to equal the FIRST ROW that your data validation list occupies.
(if your list starts at O2, it would be ...ROW()-2... )

 

[Gruuuu]

Ah, Geoff wins.
At first I was thinking that this would not work, thinking that dynamic ranges can only refer to the active sheet. I was incorrect.

Use his method.

 

[LGMan]

thanks Geoff, however I get an error saying
'The source currently evaluates to an error'

not sure what I've got wrong.

Sheet2 has the relevant company codes in cells A1:A869 while the employee code is in cells B1:B869
so cells A1:A23 contain 'ABC123' while A24:A49 contain 'ABC567' and so on. The employee codes will all be unique.

On Sheet1 I have the employee code that I want in A1 (such as 'ABC123', and have defined a name as you describe.

Data validation is built using the 'list' type and using the defined name within the Source box.

hope all that makes sense.

 

[Gruuuu]

Ah.
it's
[blue]OFFSET(rows,cols,[height],[width])[/blue]
height and width are optional, rows and cols are not.
Try this:

=OFFSET(Sheet2!$B$1,MATCH(Sheet1!$A$1,Sheet2!$A:$A,0)-1,[blue][b]0[/b][/blue],COUNTIF(Sheet2!$A:$A,Sheet1!$A$1),1)

 

[xlbo]

Based on your setup, the list should work

Sheet1 A1 = Company code

Sheet1 B1 (or wherever) = data validation list with list set to defined name range as described

If A1 is empty you will get the error message as you describe...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Should be ok to go through the error - as soon as you enter a valid company code into A1, the list should work...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[LGMan]

Sorry, I had to take some unscheduled holiday at short notice, hence taking a while to reply.
Thanks for the help thus far, however I'm still getting the error 'The source currently evaluates to an error'

To clarify
Sheet 1, cell A1 has the Company Code, the value of which can be overtyped to any of the Company Code values.
Cell E9 has the validation which I am trying to use to list all the employee codes.

Sheet 2 holds all the base data, i.e Employee codes Col B) and their company codes Col A.

 

[SkipVought]

Is the Company Code a NUMBER?

You need to post the value you are entering in Sheet1 A1 and a representative portion of the lookup table in sheet2, that includes your lookup value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Does A1 have the company code when you are creating the range name? If not, that's fine - I would expect that error - you can ignore it and choose ok - it's just because the cell that is acting as a reference (A1) doesn;t have an appropriate reference item in it so the range definition is not complete.....it will still work though

If A1 has the company code in it before you create the range name then that is a different issue and it means that eithe rht eformula you are using is not quite right or the value in A1 does not exist in your list of company codes

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kjv1611]

Is this Excel 2007? If so, you can format your range as a table, and put a filter on whatever field/column you choose.

Then copy the visible range, and go to your new sheet, and paste special, values.

But that's a one off approach. If the data will be doing this regularly, the formula methods will probably work best.