Search for string in worksheet(s) and return results in sequential order

[gizzard]

Greetings,

I should first mention that I'm using Mac Excel 2011, but what I'm asking below needs to work in Mac Excel 2008, which means no VBA. I'd like to know if this is possible:

I'm maintaining a workbook which keeps track of the status of employees (active, inactive, etc.) in multiple departments (engineering, facilities, HR, etc.). Each department's employees are on a different worksheet and each employee has multiple bits of information associated with him/her (status, first name, last name, employee ID, date of hire, etc.).

I would like one summary worksheet which displays only ACTIVE employees and their associated bits of info. Ideally, the formula(s) would search ALL worksheets simultaneously and return only the active employees. If it makes the problem easier to solve, all the employees can placed in just one worksheet.

I've experimented with different combinations of INDEX and MATCH and I think I've reached the limits of my Excel abilities!

I will attach a sample workbook shortly as I'm sure it will make it easier for you gurus. I can't provide the original since I must keep the names confidential.

Thanks!

[zbor]

Can you upload example?

Make sure to avoid confidential data.

[gizzard]

I've created a simplified version of the workbook but with all the important elements. All the data is fictitious and has been randomly generated.

[DonkeyOte]

You can use formulae for this but it won't be pretty.

I don't know if you can run MS Query from a Mac or not but if you can it might make sense to create the Summary in a separate file.

With the data in a separate file you can use an External Source Pivot Table and use SQL to join the various sheets into one "source" such that you get standard Pivot functionality
I was doing some testing re: Multi Consolidation Pivots generating the Summary within the sample file itself, however, I couldn't get a decent layout - there are others better at Pivots on the board who might be able to however.

If you save both of the attached files to C:\EF.com folder you should be able to get an idea of what I mean re: external summary

On an aside - you need also to check that your Tables transfer correctly to 2003 (referred to natively as List Objects [both are ListObjects in VBA terms per se]).

[DonkeyOte]

If it makes the problem easier to solve, all the employees can placed in just one worksheet.

I just noticed the above (!) - should read more carefully.

Yes, definitely do that.

With a single data set things become a lot simpler - you can for ex. use a standard Pivot Table.
I'd recommend adding a further column to your single consolidated sheet that denotes Department per Ee (for sake of latter analysis)

[martindwilson]

Or with a few functions!(change ranges to suit)

[mahju]

Hi
If the data is in the same worksheet and adding a column for Department
)if you want to see department wise active members) you cam use simple pivot table (adjust range)
see attached file (sheet1)

Regards

[gizzard]

Thank you for all the helpful suggestions!

Martin, your formula is a great workaround and quite elegant. Looking down the road, I will likely be adding additional fields and departments so scaling the formula may be difficult.

DonkeyOte, you were absolutely correct about suggesting pivot tables. mahju - thank you for the example. I spent some time today learning about pivot tables and experimenting with different layouts. Creating and managing pivot tables is quite different in Mac Excel 2008 and 2011! I think I can do both now.

Thanks again!

[DonkeyOte]

I spent some time today learning about pivot tables and experimenting with different layouts. Creating and managing pivot tables is quite different in Mac Excel 2008 and 2011! I think I can do both now.

Further to the above - and just in case it's a concern (not mentioned) - PT functionality differs significantly between 2003 & 2007 and I would expect the same to hold true with Mac equivalents (2004 & 2008)

Essentially - a Pivot created in a 2007+ version can not be modified etc in a version prior to 2007.
If you also require backwards compatibility prior to XL2008 then you should create the Pivots in the earliest version (ie in 2004) or alternatively save your 2008 file as .xls, close, reopen and subsequently create the Pivots (in compatibility mode).