# Excel 2003 returning multiple values from one cell reference



## Raddy (Nov 10, 2008)

Hi all,

Having an issue trying to create a summary page for a work project. I require a formula that will draw multiple values from a seperate worksheet using a single cell reference from the summary page. I've been through the forums in search of similar problems in the hopes of finding a resolution but couldn't assertain an answer.

Im required to make a summary page for multiple departments. I would like to draw out Name/Home/Mobile using Department.

Data:

Worksheet = Database


Date, Name, Home, Mobile, Department being columns A, B, C, D, E respectively

-- - -- -- -- -- -- -- -

Cell reference = Summary!$A$1 

Thanks in advance!ray:

Sam


----------



## ratcat73 (Oct 17, 2008)

G'day Raddy,

I'd like to help you, but I'm unsure the layout.

Is it possible to upload an example of your layout and a result that you'd expect.

The other reason why I ask for a example workbook, some layouts and the information you have to retrieve can alter the formula you have to apply. Especially multiple citeria ones.

Cheers


----------



## Raddy (Nov 10, 2008)

Hi ratcat,

Thanks for the reply.
I've attached an example below (it's PDF sorry).


Cheers again :smile:


----------



## ratcat73 (Oct 17, 2008)

G'day Sam,

I have supplied an example. 
In Sheet 1 at A1 is a down down box (data validation) so just select the dept name you wish to list. Then the names and numbers will appear below. 

I have used two formulas. Both in Sheet 1.

In column A is an array formula. Went you edit an array formula you exit the formula bar by *Ctrl Shift Enter*.

And in col B and C is a VLOOKUP formula with a IF statement wrap around it. Btw the all names must be unique for the VLOOKUP formula to work 100%. If not, can re edited the array formula, but that can make the workbook large in size and can small down the calculation of the workbook.

If you do not understand any of it, just like me know and will go from there.

Cheers


----------



## Raddy (Nov 10, 2008)

Hi again ratcat!

Thanks for the formula's, they work a treat. However I've ran into another issue. I now have to use a formula to bring up each department's roster sheet. Only people who have a shift are to be shown. 

Can you even have more than one criteria?

Cheers.:wave:


----------



## Raddy (Nov 10, 2008)

Hello again... sorry to double post but could you also post the array formula for non-unique names?

Cheers.



> G'day Sam,
> 
> I have supplied an example.
> In Sheet 1 at A1 is a down down box (data validation) so just select the dept name you wish to list. Then the names and numbers will appear below.
> ...


----------



## ratcat73 (Oct 17, 2008)

Raddy said:


> .........also post the array formula for non-unique names?
> 
> Cheers.


Can you please post an example of what you would like to achieve.

See example for the Roster request

Cheers


----------



## Raddy (Nov 10, 2008)

Hello again, ran into another problem.

I want to pull only the people who are working in Sales and have a reason for not being available.

This is what I have for the formula:

{=IF(ROW()-2>SUMPRODUCT(--(ISTEXT(Data!$F$2:$F2000)*(Data!$F$2:$F2000<>"")),--(Data!$D$2:$D2000="Sales")),"",INDEX(Data!$A$2:$A2000,SMALL(IF(Data!$D$1:$D2000="Sales",ROW(Data!$F$1:$F2000),""),ROW()-2)))}

Problem being I dont know how to set a criteria for cells with text. 

i.e. IF(A1:A4=_text_,"","")


----------



## Raddy (Nov 10, 2008)

Hello again, ran into another problem.

I want to pull only the people who are working in Sales and have a reason for not being available.

This is what I have for the formula:

{=IF(ROW()-2>SUMPRODUCT(--(ISTEXT(Data!$F$2:$F2000)*(Data!$F$2:$F2000<>"")),--(Data!$D$2:$D2000="Sales")),"",INDEX(Data!$A$2:$A2000,SMALL(IF(Data!$D$1:$D2000="Sales",ROW(Data!$F$1:$F2000),""),ROW()-2)))}

Problem being I dont know how to set a criteria for cells with text. 

i.e. IF(A1=_text_,"","")

Any help would be greatly appreciated. 
Thanks in advance,
Sam


----------



## ratcat73 (Oct 17, 2008)

Does this help ????


----------



## Raddy (Nov 10, 2008)

Thanks again Ratcat, tweaked your formula a tiny amount and it works perfectly. 

Cheers again. :grin:

Sam


----------

