How to query a spreadsheet?

[Tendresse]

Hi all,
I'm trying something completely new for me and have no idea whether i'm on
the right track or not. I'm trying to create a relational database in Excel.
I have a workbook containing 4 worksheets. Each worksheet has a table as
follows:
Sheet 1: contains a table of 'Suppliers Details' (ID, Name, Location)
Sheet 2: contains a table of 'Clients Details' (ID, Name, Location)
Sheet 3: contains a table of 'Products Details' (ID, Name, Description, Price)
Sheet 4: contains a table of 'Sales Details' (Date, Product ID, Supplier ID,
Client ID, and a lot other fields).

I know how to apply formulas to each individual table to count all sorts of
information from that table. But i don't know how to count information that
needs to be gathered from more than one table at a time. For example:
How many 'Products' with a specific 'Price' were sold to 'Clients' located
in a specific 'Location' on a certain 'Date'?

I did some readings about using MS Query but i'm not having a good start at
all. I'm just scratching the surface and i'm already getting an error message
'This data source contains no visible tables'. What am i doing wrong? and
before that, am i on the right track in the first place or what i'm trying to
do is not achievable in Excel? I know this is probably much easier in Access,
but my employer insists on using Excel.

I'm using Excel 2003.
Looking forward to any help or clues you can give me.
Thanks in advance
Tendresse

 

[FSt1]

hi
you are right. it would be easier in acces. that is what access is for.
but......you have been TOLD.....excel.
bummer.
read up more on MSQ(microsoft query). way to go. if(when) you can get into
MSQ,
you will find it remarkibly similar to access.
'This data source contains no visible tables'
oops.! MSQ recognizes Named Ranges as tables. otherwise it looks at the
whole sheet. give your "tables" names. and as you tables grow you will need
to rename them(expand them) here is an example i use in my personal check
book/ bank file.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Macro Written 5/9/2004 by FSt1
Dim rng As Range
Dim sht As Worksheet
Dim srng As Range

Set rng = ActiveCell ' notes where you're at - cell
Set sht = ActiveSheet ' notes where you're at - sheet

Sheets("ChkBook").Select
Range("A1").Select
Set srng = Range(Range("A1"), _
Range("A1").Offset(0, 1) _
.End(xlDown).Offset(0, 7))
ActiveWorkbook.Names.Add Name:="BankBal", _
RefersToR1C1:=srng
Range("A1").Offset(0, 1).End(xlDown).Select
sht.Select 'returns to last sheet
rng.Select 'returns to last cell
End Sub

"I know how to apply formulas to each individual table to count all sorts of
information from that table."
not entirely sure what you mean by this. more details. a number of formulas
may be available...sumif.....countif...lookup....other...
"How many 'Products' with a specific 'Price' were sold to 'Clients' located
in a specific 'Location' on a certain 'Date"
multiple table queries. in MSQ. go past the wizard to the last wizard
screen... past the "choose columns screen", past the "filter data screen",
past the "sort order screen", to the 'finish screen" and choose...."view data
or edit in microsoft query"
do not choose "return data to excel". here you can add tables and add
selection cirteria. looks remarkably similar to access and work almost like
access. wow.
how much do you know about access? read up on that too.
so you have 4 tables.
supplier details
client details
product details
sales detals.
WHAT!?!?!?
no receiviing details, warehouse details,
acounting.....forcasting.....MRP!?!?!
***if you are successful at your current task, it's comming.***
believe me. been there. done that. sucks.
become as access salesman. uphill battle when your boss AND peers are excel
freaks. but......

my thoughts(sigh....you have my sympathies)
regards
FSt1