Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to query a spreadsheet?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to query a spreadsheet?
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 "Tendresse" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query causing #ref error in spreadsheet | Excel Discussion (Misc queries) | |||
Web Query for Large Spreadsheet | Excel Discussion (Misc queries) | |||
query a spreadsheet | Excel Discussion (Misc queries) | |||
Excel spreadsheet Query | Excel Discussion (Misc queries) | |||
Trying to format the spreadsheet to fit on one page from a query | Excel Discussion (Misc queries) |