Querying Worksheets

[thefonz37]

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?

 

[sahafi]

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.

 

[thefonz37]

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]

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.

 

[thefonz37]

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]

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.