Excel vs Access

[Guest]

Hi all, I desperately need help.
I'm in the process of creating a large database for our office. Due to
bureaucratic reasons, my employer will not be able to install Microsoft
Access on our computers, and I was told to use Excel instead! For the last
couple of days i have been like someone trying to use a screw driver as
hammer! I know Excel is not the best tool for that job but i have no choice
but to try to go around things. I hope that someone in the community would be
able to help me with one particular problem.

In the first worksheet, i have a table of our offices (branches) details, as
follows:
A B C D E
Branch Branch
ID# Name Address Phone Country .... etc

In the second worksheet, i have a table of employees (Emp.), as follows:
A B C D E
Emp. Emp.
ID# Name Position Salary Branch ID# .. etc

(Notice that column E in table 2 is the Branch ID#, which is the same as
Column A in table 1).

Now, out of these 2 tables, how can i get a list of:
All EMPLOYEES who have a certain POSITION (eg. supervisors) and their
correspondent BRANCH NAME and BRANCH ADDRESS.

Is this sort of queries possible in Excel 2003? Can this be achieved through
filtering, pivottables or VBA?
Any help would be greatly appreciated.

Tendresse

 

[Ed Ferrero]

Hi Tendresse,

You have my sympathies. Check out my tutorial on using Excel as a database
at
http://www.edferrero.com/ExcelTutorials/ExcelDatabaseTutorialPart1/tabid/90/Default.aspx

HTH

Ed Ferrero
www.edferrero.com

 

[T. Valko]

You could add 2 columns to your table on sheet2 that lookup the branch name
and address from the table on sheet1 and then just filter the table on
sheet2 by position.

Here's a small sample file that demonstrates this:

delete1.xls 19kb

http://cjoint.com/?iFiiRHFTwy

 

[Guest]

G'Day Ed
Thanks heaps for your reply. I'll start reading the tutorial now.
And thanks for the sympathies .. i do need them .
Tendresse

 

[Guest]

Brilliant and simple idea .. YES. This will make my life much easier.
Thanks a million, Biff.
Tendresse

 

[Guest]

Ed,
Your tutorial is fantastic. I have learnt so much from it already. I never
knew this bit about dynamic drop down lists. This is so handy. And of course
the VLOOKUP function that would answer my original question.
I can't wait until you publish Part 2 of the tutorial. Please hurry
Thanks again
Tendresse

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Guest]

As T. Valko has pointed out, don't be afraid to use 'helper' cells to make
life easier for you. Nothing says they have to be visible, Format | Column |
Hide is your friend at times like that.

I suspect that sooner or later you will also need to get familiar with some
of the inventive ways that the SUMPRODUCT() function can be used to provide
multiple criteria selection from tables. Just looking at current data, I can
imagine someone asking you to provide the total salary cost in Branch ID# for
people occupying Position such-and-such, and that's the kind of situation
that SUMPRODUCT() can be helpful. Not going into detail now, you already
have enough to deal with, just keep it in mind as a potential solution down
the road. When the time comes, you'll ask the question and someone here will
provide the answer.

 

[Guest]

JLatham, thanks a lot for the hint. Yes, you are absolutely right. I'm pretty
sure one day i'll be asked to get all sorts of reports, stats, charts and
information from the spreadsheet. I'll keep your advice in mind, that's for
sure.
Thanks again. Have a lovely day.
Tendresse