Vlookup and multiple data

[Josuha]

this is in my 1st work sheet
(a1) 500 (b1)Bob (c1)Yes (d1)hants
(A2) 500 (b2)Dave (c2)No (d2)surrey
(A3) 500 (b3)Rob (c3)Yes (d3)york



this is the formula i am using in the next worksheet for a drop down lis
=IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE))

It will show the 500 seperately but what i would like to do is when you
select the 500 is to have all of the information for 500 shown in the next
sheet.I don't want to have to select it individually.
Hope this makes sense.

 

[Tom]

Do you want the data for all rows with 500 to show? Or just one row of data?
Not sure which row of data you want.

 

[Josuha]

Hi Tom,
I would like all the rows for 500 to show really
my frop down list currently looks like this > 500
500
500
What i would like is to select any 500 and for all the information to be
shown.
Many thanks

 

[Pokey]

The only way I can think of to use the Vlookup and your drop down box with
the 500's all being the same is change your 500's to like 501, 502, and 503.
The vlookup is only going to lookup your first 500.

 

[Tom]

I think I can write a formula for you, but I need to know something:
Will the drop down box only ever list the numbers in the A column? For
example, will you need to sort by all the Robs, or No's?

If all we're sorting by are the numbers, I think it can be done.
Also, are the only three ways you group them:
<500
500

500

Is that correct?

 

[Josuha]

Appreciate the help Guys.

It will only need to list the numbers.
Bascially i have a list of cost centres 500, 600, 750, 800 etc
but i have numerous people assigned to each cost centre, what i would like
to do is when i select the 500 from a drop down list it pulls all the
information for every 500 into a seperate worksheet.
I would use a piv table but the second work sheet is a form being sent out
to users and im not savy with piv tables atm