Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Querying Worksheets
Is there a function or functions to query Excel worksheets like you would a
database? Maybe using SQL? Here's the issue I'm having - I need to design a scorecard type of spreadsheet with a separate sheet for each employee evaluated. Rather than have each worksheet contain 3+ queries, I would prefer to link directly to the Access data tables I'm pulling the info from (into a "Data" worksheet) and then query the "Data" worksheet from the other worksheets. Is this sounding crazy? Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Querying Worksheets
Not sure what exactly you are trying to accomplish. But you can use a Lookup
(VLookup/Hlookup) formula from each employee sheet to pull desired data from your main 'Data' sheet. You could also use a SUMPRODUCT or INDEX/MATCH depending on your data. Or you could use pivot tables. So there are many ways of populating individual sheets from main sheet. H.T.H. -- when u change the way u look @ things, the things u look at change. "thefonz37" wrote: Is there a function or functions to query Excel worksheets like you would a database? Maybe using SQL? Here's the issue I'm having - I need to design a scorecard type of spreadsheet with a separate sheet for each employee evaluated. Rather than have each worksheet contain 3+ queries, I would prefer to link directly to the Access data tables I'm pulling the info from (into a "Data" worksheet) and then query the "Data" worksheet from the other worksheets. Is this sounding crazy? Can anyone help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Querying Worksheets
Ok, let me be a little more specific.
I have 3 queries on my "Data" worksheet. The first query gives me results for manually entered data (i.e., things I cannot generate reports for and have to rely on the employees to manually enter into an Access table). This has the fields employee name, ID number, task (what they were doing), hours spent doing that task, number completed, and date. T The second query gives me results for system-generated data, with the fields employee name, ID numbers, task, hours, and date. The third is related to the second in that it gives employee name, ID number, task, number completed, and date. The thought I was having was to create a separate worksheet for each employee to link back to this data, but the problem is that the key for each is the employee name, task, and date, so unless I'm mistaken, I don't think v/hlookup will help. If I'm wrong, please correct me. Also, I don't know much about sumproduct or index/match, so if they can help, I'd appreciate more information. "sahafi" wrote: Not sure what exactly you are trying to accomplish. But you can use a Lookup (VLookup/Hlookup) formula from each employee sheet to pull desired data from your main 'Data' sheet. You could also use a SUMPRODUCT or INDEX/MATCH depending on your data. Or you could use pivot tables. So there are many ways of populating individual sheets from main sheet. H.T.H. -- when u change the way u look @ things, the things u look at change. "thefonz37" wrote: Is there a function or functions to query Excel worksheets like you would a database? Maybe using SQL? Here's the issue I'm having - I need to design a scorecard type of spreadsheet with a separate sheet for each employee evaluated. Rather than have each worksheet contain 3+ queries, I would prefer to link directly to the Access data tables I'm pulling the info from (into a "Data" worksheet) and then query the "Data" worksheet from the other worksheets. Is this sounding crazy? Can anyone help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Querying Worksheets
Using SUMPRODUCT:
say in your 'Data' sheet your data is arranged from A1:F13 (date, ID, Name, Task_Code, Num_of_Task, Hrs_On_Task). Copy the A2:A13 (dates) into the same range in each sheet. On Emp1 sheet type ID for that employee on cell A1. Type all the tasks codes on B1, C1, D1, ... say you have a total of 6 different tasks. On B2 type this formula: SUMPRODUCT(--(Data!$A$2:$A$13=Emp1!$A2),--(Data!$B$2:$B$13=Emp1!$A$1),--(Data!$D$2:$D$13=Emp1!B1),--(Data!$G$2:$G$13)) This is an array formula, so you must commit by pressing Shift+Ctrl+Enter keys at once. Copy across to col G and down to end of range (adjust according to your range). Another way: create one query in Access out of your 2 tables. So you will have: Date, ID, Name, Task_Code, Num_of_Tasks (from manual tbl), Num_Tasks (from Sys gen tbl), Hours (tbl1), Total_Hours (tab2). Then create multiple copies of that main query, one for each employee (if you have manageable number of employees) then link each sheet to its corresponding query. All you have to do in Excel is refresh the workbook to update your data. H.T.H. Os. -- when u change the way u look @ things, the things u look at change. "thefonz37" wrote: Ok, let me be a little more specific. I have 3 queries on my "Data" worksheet. The first query gives me results for manually entered data (i.e., things I cannot generate reports for and have to rely on the employees to manually enter into an Access table). This has the fields employee name, ID number, task (what they were doing), hours spent doing that task, number completed, and date. T The second query gives me results for system-generated data, with the fields employee name, ID numbers, task, hours, and date. The third is related to the second in that it gives employee name, ID number, task, number completed, and date. The thought I was having was to create a separate worksheet for each employee to link back to this data, but the problem is that the key for each is the employee name, task, and date, so unless I'm mistaken, I don't think v/hlookup will help. If I'm wrong, please correct me. Also, I don't know much about sumproduct or index/match, so if they can help, I'd appreciate more information. "sahafi" wrote: Not sure what exactly you are trying to accomplish. But you can use a Lookup (VLookup/Hlookup) formula from each employee sheet to pull desired data from your main 'Data' sheet. You could also use a SUMPRODUCT or INDEX/MATCH depending on your data. Or you could use pivot tables. So there are many ways of populating individual sheets from main sheet. H.T.H. -- when u change the way u look @ things, the things u look at change. "thefonz37" wrote: Is there a function or functions to query Excel worksheets like you would a database? Maybe using SQL? Here's the issue I'm having - I need to design a scorecard type of spreadsheet with a separate sheet for each employee evaluated. Rather than have each worksheet contain 3+ queries, I would prefer to link directly to the Access data tables I'm pulling the info from (into a "Data" worksheet) and then query the "Data" worksheet from the other worksheets. Is this sounding crazy? Can anyone help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Querying Worksheets
What type of result does SUMPRODUCT return?
The problem is that the query won't always return the same amount of records, depending on how many different tasks folks are completing. There could be many different tasks listed per employee per day even, so I that's why I can't lookup based on name and date even. I'd have to lookup on task, name, and date, I suppose. I wanted to avoid having queries on every page of the worksheet. With even a small sample of employees, say 20, that becomes 60 queries, which is cumbersome to refresh (I actually think there's a limit to the number of queries/worksheet you can have as well). "sahafi" wrote: Using SUMPRODUCT: say in your 'Data' sheet your data is arranged from A1:F13 (date, ID, Name, Task_Code, Num_of_Task, Hrs_On_Task). Copy the A2:A13 (dates) into the same range in each sheet. On Emp1 sheet type ID for that employee on cell A1. Type all the tasks codes on B1, C1, D1, ... say you have a total of 6 different tasks. On B2 type this formula: SUMPRODUCT(--(Data!$A$2:$A$13=Emp1!$A2),--(Data!$B$2:$B$13=Emp1!$A$1),--(Data!$D$2:$D$13=Emp1!B1),--(Data!$G$2:$G$13)) This is an array formula, so you must commit by pressing Shift+Ctrl+Enter keys at once. Copy across to col G and down to end of range (adjust according to your range). Another way: create one query in Access out of your 2 tables. So you will have: Date, ID, Name, Task_Code, Num_of_Tasks (from manual tbl), Num_Tasks (from Sys gen tbl), Hours (tbl1), Total_Hours (tab2). Then create multiple copies of that main query, one for each employee (if you have manageable number of employees) then link each sheet to its corresponding query. All you have to do in Excel is refresh the workbook to update your data. H.T.H. Os. -- when u change the way u look @ things, the things u look at change. "thefonz37" wrote: Ok, let me be a little more specific. I have 3 queries on my "Data" worksheet. The first query gives me results for manually entered data (i.e., things I cannot generate reports for and have to rely on the employees to manually enter into an Access table). This has the fields employee name, ID number, task (what they were doing), hours spent doing that task, number completed, and date. T The second query gives me results for system-generated data, with the fields employee name, ID numbers, task, hours, and date. The third is related to the second in that it gives employee name, ID number, task, number completed, and date. The thought I was having was to create a separate worksheet for each employee to link back to this data, but the problem is that the key for each is the employee name, task, and date, so unless I'm mistaken, I don't think v/hlookup will help. If I'm wrong, please correct me. Also, I don't know much about sumproduct or index/match, so if they can help, I'd appreciate more information. "sahafi" wrote: Not sure what exactly you are trying to accomplish. But you can use a Lookup (VLookup/Hlookup) formula from each employee sheet to pull desired data from your main 'Data' sheet. You could also use a SUMPRODUCT or INDEX/MATCH depending on your data. Or you could use pivot tables. So there are many ways of populating individual sheets from main sheet. H.T.H. -- when u change the way u look @ things, the things u look at change. "thefonz37" wrote: Is there a function or functions to query Excel worksheets like you would a database? Maybe using SQL? Here's the issue I'm having - I need to design a scorecard type of spreadsheet with a separate sheet for each employee evaluated. Rather than have each worksheet contain 3+ queries, I would prefer to link directly to the Access data tables I'm pulling the info from (into a "Data" worksheet) and then query the "Data" worksheet from the other worksheets. Is this sounding crazy? Can anyone help? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Querying Worksheets
It will give you the amount of time (hours/minutes) spent on each task by
each employee at any given date (I think this is what you asked). As I stated previously list all your possible tasks across from the B1 to sya Z1 (this is fixed, meaning you do it once, unless you have a new task). It doesn't matter if a certain employee did only a few tasks on one day. The formula will spit out zero where the task is not applicable. Then at the far right, say in col AA you can sum the time for that employee on that date. Try it first and see the result. The second option, I have already suggested to have only one query with data coming from both the manual input tbl and the sys generated tbl. You probably need to rework your Access query first - think relational dB. H.T.H. -- when u change the way u look @ things, the things u look at change. "thefonz37" wrote: What type of result does SUMPRODUCT return? The problem is that the query won't always return the same amount of records, depending on how many different tasks folks are completing. There could be many different tasks listed per employee per day even, so I that's why I can't lookup based on name and date even. I'd have to lookup on task, name, and date, I suppose. I wanted to avoid having queries on every page of the worksheet. With even a small sample of employees, say 20, that becomes 60 queries, which is cumbersome to refresh (I actually think there's a limit to the number of queries/worksheet you can have as well). "sahafi" wrote: Using SUMPRODUCT: say in your 'Data' sheet your data is arranged from A1:F13 (date, ID, Name, Task_Code, Num_of_Task, Hrs_On_Task). Copy the A2:A13 (dates) into the same range in each sheet. On Emp1 sheet type ID for that employee on cell A1. Type all the tasks codes on B1, C1, D1, ... say you have a total of 6 different tasks. On B2 type this formula: SUMPRODUCT(--(Data!$A$2:$A$13=Emp1!$A2),--(Data!$B$2:$B$13=Emp1!$A$1),--(Data!$D$2:$D$13=Emp1!B1),--(Data!$G$2:$G$13)) This is an array formula, so you must commit by pressing Shift+Ctrl+Enter keys at once. Copy across to col G and down to end of range (adjust according to your range). Another way: create one query in Access out of your 2 tables. So you will have: Date, ID, Name, Task_Code, Num_of_Tasks (from manual tbl), Num_Tasks (from Sys gen tbl), Hours (tbl1), Total_Hours (tab2). Then create multiple copies of that main query, one for each employee (if you have manageable number of employees) then link each sheet to its corresponding query. All you have to do in Excel is refresh the workbook to update your data. H.T.H. Os. -- when u change the way u look @ things, the things u look at change. "thefonz37" wrote: Ok, let me be a little more specific. I have 3 queries on my "Data" worksheet. The first query gives me results for manually entered data (i.e., things I cannot generate reports for and have to rely on the employees to manually enter into an Access table). This has the fields employee name, ID number, task (what they were doing), hours spent doing that task, number completed, and date. T The second query gives me results for system-generated data, with the fields employee name, ID numbers, task, hours, and date. The third is related to the second in that it gives employee name, ID number, task, number completed, and date. The thought I was having was to create a separate worksheet for each employee to link back to this data, but the problem is that the key for each is the employee name, task, and date, so unless I'm mistaken, I don't think v/hlookup will help. If I'm wrong, please correct me. Also, I don't know much about sumproduct or index/match, so if they can help, I'd appreciate more information. "sahafi" wrote: Not sure what exactly you are trying to accomplish. But you can use a Lookup (VLookup/Hlookup) formula from each employee sheet to pull desired data from your main 'Data' sheet. You could also use a SUMPRODUCT or INDEX/MATCH depending on your data. Or you could use pivot tables. So there are many ways of populating individual sheets from main sheet. H.T.H. -- when u change the way u look @ things, the things u look at change. "thefonz37" wrote: Is there a function or functions to query Excel worksheets like you would a database? Maybe using SQL? Here's the issue I'm having - I need to design a scorecard type of spreadsheet with a separate sheet for each employee evaluated. Rather than have each worksheet contain 3+ queries, I would prefer to link directly to the Access data tables I'm pulling the info from (into a "Data" worksheet) and then query the "Data" worksheet from the other worksheets. Is this sounding crazy? Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Querying data | Excel Discussion (Misc queries) | |||
Querying Internal Data | Excel Discussion (Misc queries) | |||
Querying a range within Excel | Excel Worksheet Functions | |||
QUERYING ACCESS | Excel Discussion (Misc queries) | |||
Querying Data from Access | Excel Discussion (Misc queries) |