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

Hi Tendresse,

You have my sympathies. Check out my tutorial on using Excel as a database
at
http://www.edferrero.com/ExcelTutori...0/Default.aspx

HTH

Ed Ferrero
www.edferrero.com

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

--
Biff
Microsoft Excel MVP


"Tendresse" wrote in message
...
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

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


"Ed Ferrero" wrote:

Hi Tendresse,

You have my sympathies. Check out my tutorial on using Excel as a database
at
http://www.edferrero.com/ExcelTutori...0/Default.aspx

HTH

Ed Ferrero
www.edferrero.com

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Tendresse" wrote in message
...
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,
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


"Ed Ferrero" wrote:

Hi Tendresse,

You have my sympathies. Check out my tutorial on using Excel as a database
at
http://www.edferrero.com/ExcelTutori...0/Default.aspx

HTH

Ed Ferrero
www.edferrero.com

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Tendresse" wrote in message
...
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

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.

"Tendresse" wrote:

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Tendresse" wrote in message
...
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

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

"JLatham" wrote:

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.

"Tendresse" wrote:

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Tendresse" wrote in message
...
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