Using Excel as a Database?

[Sunjinsak]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’ll try to explain this as best I can…
<o></o>
I need to create a database of individuals and their associated team names, location, positions, contact details etc using Excel 2003. Unfortunately we do not have access to any database programs in work so Excel is the only option.
<o></o>
What I need is fairly complex and I’m not even sure Excel is up to the job but here goes anyway…
<o></o>
Basically, an individual can be a member of one or more of a specific number of teams. Also, there are two possible geographic locations for these teams, then further location-specific info within the geographic locations (e.g. floor, wing, internal telephone extension and e-mail address).
<o></o>
I need to create one “control sheet” with everybody’s details on – sorted in alphabetical order – but then have these people appear on separate sheets within the same workbook based on their location and the different teams they’re a member of.
<o></o>
The geographic locations are LH and TR, and they could be a member of any of the following teams; IR, HS, ULR, BEC or PCW.
<o></o>
I’m hoping to have a page each for the two locations (LH and TR) and on each page have buttons linking to lists for the individual teams in that site (IR, HS, ULR, BEC and PCW).
<o></o>
Ideally I’d like to only have to update people’s details on the control sheet and have those changes reflected across the other pages (as there are 200+ people on the list).
<o></o>
For example:
Person A, based in LH, member of IR, HS and PCW
Person B, based in LH, member of IR, HS, ULR, BEC, PCW
Person C, based in TR, member of HS
<o></o>
Person A would show up on the IR, HS and PCW pages under the LH location
Person B would show up on the IR, HS, ULR, BEC and PCW pages under LH
Person C would only show up on the HS page under TR.
<o></o>
If Person C then becomes a member of BEC I can “set” BEC against their name in the control sheet and they will then show up on both the HS and BEC pages under TR automatically.
<o></o>
No one will show up on a list for a team of which they are not a member, or in a location they are not based.
<o></o>
Does that make sense? Is it possible to achieve in Excel? If so how do you suggest I go about it?
<o></o>
I can use VBA if necessary and – at least as far as I know – there are no restrictions in place in terms of Excel functionality.
<o></o>
The only restriction is that this whole “database” needs to be kept in one workbook.
<o></o>
Thanks.

 

[Sunjinsak]

Cheeky bump

 

[Kyle123]

I would normally suggest a database, however as you've explained, you can't. However this should be reasonably easy to complete in Excel.

Conceptually, you'd have a master list with the names running down and then each group would be a separate column where you marked whether each person is a member of that group, on the the other sheets you would then use something like INDEX MATCH to pull in the details of the people in that group.

 

[Sunjinsak]

I would normally suggest a database, however as you've explained, you can't. However this should be reasonably easy to complete in Excel.

Conceptually, you'd have a master list with the names running down and then each group would be a separate column where you marked whether each person is a member of that group, on the the other sheets you would then use something like INDEX MATCH to pull in the details of the people in that group.

Thanks Kyle,

Yes, unfortunately we have no access to a database

I have already created a master sheet with everyones' names and details etc - including splitting the groups into seperate columns and marking whether or not they're a member with a Y or N.

Is this the type of thing you were getting at?

I'm not familiar with INDEX MATCH. I'll have a little scout around Google and the Excel help files and see what I can come up with.

Thanks for getting back to me.

 

[Kyle123]

Yes, but you might find it easier having the group name or nothing rather than yes,no

 

[Sunjinsak]

Okay I need help again!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’ve done some research on the INDEX and MATCH functions and I get the principle of how they work… but I can’t for the life of me bend my head around how to implement them in practice for what I want to achieve.
<o> </o>
Can anyone give me a working example perhaps? If so I’m sure I can examine it, learn from it and adapt it to my needs.
<o> </o>
On my control sheet I have the following:
<o> </o>
From left to right, starting in cell A4 with each piece of data in a new cell (B4, C4 etc)…
<o> </o>
Surname, Forename, Location, Extension, Building (with a cell each for either TR or LH), Teams (again a cell for each of the possible teams listed in my first post – left blank if they are not a member of a particular team), E-mail address, Notes
<o> </o>
Obviously Person B’s info starts in A5 and Person C’s in A6 etc…
<o> </o>
Let’s say for example I have another sheet that lists only those people in teams PCW, IR and BEC in the LH location.
<o> </o>
How would I use INDEX and MATCH to make only those people listed on the control sheet that meet the criteria show up on the second sheet?
<o> </o>
I’m sorry for the complex n00b questions but I’m very confused!
<o> </o>
Please let me know if you need any more information or any clarification about anything.
<o> </o>
Thanks again.

 

[PaddyD]

You can set up a relational database in excel by structuring your data right, then using the MS query tool.

Good info here:

http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

 

[Kyle123]

Ok, to do it with formulas, I'd have an ID column in the A column of the Master Sheet.
In the other sheets, you can use the below to return the ID of each person who is in the relevant location and in the relevant group, I've hardcoded the group/location for the example, I've also used fixed ranges, it would be better to make these dynamic named ranges.

So this would go wherever you want to return the IDs. This is an array formula so needs to be entered with Ctrl+Shift+Enter. It should then be dragged down:

=IFERROR(INDEX(Sheet1!$A$5:$A$7,SMALL(IF(IF(Sheet1!$E$5:$E$7="IR",1,0)*IF(Sheet1!$D$5:$D$7="TR",1,0),ROW($B$2:$B$4)),ROW(1:1))-1),"")

You can then use VLookup on the ID to return any further data, does this help?

@Paddy, that's an interesting read. I've never seen doing that before, where users don't have access installed I generally use Excel as a front end to the Jet database with DAO/ADO

 

[Sunjinsak]

Ok, to do it with formulas, I'd have an ID column in the A column of the Master Sheet.
In the other sheets, you can use the below to return the ID of each person who is in the relevant location and in the relevant group, I've hardcoded the group/location for the example, I've also used fixed ranges, it would be better to make these dynamic named ranges.

So this would go wherever you want to return the IDs. This is an array formula so needs to be entered with Ctrl+Shift+Enter. It should then be dragged down:

=IFERROR(INDEX(Sheet1!$A$5:$A$7,SMALL(IF(IF(Sheet1!$E$5:$E$7="IR",1,0)*IF(Sheet1!$D$5:$D$7="TR",1,0),ROW($B$2:$B$4)),ROW(1:1))-1),"")

You can then use VLookup on the ID to return any further data, does this help?

Ah man! Thanks again Kyle - I appreciate your efforts to help (and everyone else's too).

Unfortunately I just can't get my head around how this is supposed to work

I've put the formula in my workbook on the page I want the results to be returned on and it gives me a #NAME? error. I've tweaked it a bit to point to the ranges I have on the control sheet and to also reference the control sheet itself (I renamed it from "Sheet1") but nothing seems to work (I even tried renaming the control sheet to fit the formula).

I've poured over the formula too to try and understand it but it doesn't make any sense to me

I thought I was reasonably good with Excel but clearly not lol. Sorry to be a pain!

 

[Kyle123]

No problem are you running Excel 2003 by any chance?