___ Macro runs query and and creates table .. now needing to do a look

[Lynux]

I execute a query through a macro.. data is returned to one wooksheet.... Now I am needing a specific cells to point to a graph template I have set up.

Example... query returns to sheet1:
|__Gender__|__Age__|__Score__|
|____M_____|___20__|___45____|
|____F_____|___19__|___47____|
|____M_____|___19__|___46____|

I am needing to bring in the contentents of the "Score" column in sheet1 into cell A1 Sheet2 where and only where Gender=M and Age=20

Is it possible to generate an Array and do multiple if statements like this in Excel?

Thanks in advance ! =================================
Imagination is more important than knowledge.
(A.E.)

 

[jackal63]

I've been doing something alot like this.

Maybe you could try doing a for each loop to get the data you want to be contiguous then do the chart on that. So in your example

dim XX as integer
XX = 0
for each myObject in range(a2:a4)
if myObject = "M" then

worksheets("Sheet1&quot.range("F1&quot.offset(XX, 0) = worksheets("Sheet1&quot.range(myObject.address).offset(0, 1)

worksheets("Sheet1&quot.range("G1&quot.offset(XX, 0) = worksheets("Sheet1&quot.range(myObject.address).offset(0, 2)

XX = XX + 1
end if
next myObject

The result of this should be that for all entries with an M in the first column of your example, the last two columns should be duplicated in the range F1:G?, where ? is the number of hits you got for "M" (2 in your sample) and you can generate your chart on these columns.

Well, hope that's helpful. Writting for clarity has never been one of my skills, so it was probably more of a hinderance. Sorry.

 

[euskadi]

Let's say you name the range with sex "Sex", the name with ages is "age" and the name with the score is "Score".

If you want the average of scores for 20 y.o. males in cell a1 on sheet2, enter this:

=SUM(IF(sex="m",IF(age=20,score,0),0)/SUM(IF(sex="m",IF(age=20,1,0),0)))

It's an array formula, do CTRL+SHIFT+ENTER instead of ENTER after entering the formula.

One warning. If you do a query that gathers data, it may hose your named ranges when you refresh the query. You should be able to get around that by creating a dynamic named range... we can walk you through that if you haven't done it before.

 

[gem28]

Yes, it is possible to insert multiple array in Excel. But with the table that you have. I doubt if you even need one.