concatenation of two ranges into one so that the resultant r

[Avi]

Hi,

I have an interesting problem for which I have been trying to find a solution to no avail. I will be greatly appreciative if you could help me to do so. Here is the problem:

I would like to include in the criteria argument of the DSUM function an area that is a combination of two physical areas that are physically located in two distinct sheets, say sheet1 and sheet2. sheet1 may include a row of the field names to appear In the criteria, and sheet2 may include the row specifying the constraints corresonding to the field names -- that is, the values by which DSUM determines whether a record meets the criteria with repect to the field names in sheet1. Do you think it is possible to concatenate the two ranges into one and place it in the criteria range of the DSUM function?

To solve that problem, I tried to express a range as an array (i.e., values between curly braces such that comma denotes a move to next column and semi column a move to next row) -- but it did not work, since Excel requires that the criteria in the DSUM function be a reference (e.g., A1:H2) and not in an array represenatation form ( as described above) of the values in that range.

Once again, if you have any idea how to solve that problem, or if that problem could not be overcome, I would be greatly appreciative to learn about the solution.

Thank you in advance

Avi


4/12/2002

Hi guys,

Thank you all for the effort in trying to help me. I'm sorry I did not provide enough details regarding the problem that I am facing.

The problem revolves around the apparent difficulty to create a range out of two non contiguous ranges and place the resultant range in the criteria parameter of the DSUM function. For example, suppose I have a table with field names NAME, CITY, and ANUAL SALARY. I want to find the annual income per a given city using the DSUM function. It sounds simple if I use different criteria range per each city. So for the city of NY, I might have a criteria range consisting of two cells; one cell would have the value CITY and the other BENEATH it would be NY. As such the DSUM functiuon will include the entire table with the fields and their records; the field parameter which will be SALARY; and the criteria which will be a reference to the range I have just mentioned above. Now if I wanted to do the same with respect to another city, I will create another identical DSUM formula, but this time the criteria reference will include a different range where the name of the city would be Albany, for example.

However, what I try to do -- and this is not an exercise -- is use in both cases (NY and Albany) a criteria range that will consist of one common cell, the field name (i.e., CITY), and one other cell corresponding in each of mthese cases to the value of the CITY. In other words, I would like to create MULTIPLE DSUM functions, using in each of the relating criterias the SAME reference to a cell with the value CITY in it and then concatenating (so to speak) that particular cell to another cell that will bear the name of the city. In that respect, DSUM function to find the annual salary for the city of NY might include the cell $A$1 and the cell $A2, assuming $A$1 has the value CITY anf $A2 NY. DSUM function to find the annual salary for Albany will include a criteria range that would consist of the SAME cell $A$1 and another cell that is NOT CONTIGUOUS to $A$1 -- that cell might be $A3.

If you think it's possible to do this or not, I will be glad to learn how you do it.

Thank you all


Avi

[ This Message was edited by: avi on 2002-04-12 12:00 ]

[Yogi Anand]

Hi Avi:
Welcome to the Board!
I tried using information from another worksheet to make up part of the crireria in a DSUM function -- I did not have any problem with that. Can you post part of your table and the criteria you are using and what results you are geting ... and let us take it from there.

[Al Chara]

I tried to do what you want, but I don't think DSUM will accept that syntax.

[Al Chara]

Yogi,
I think Avi is trying to use criteria from two different worksheets at the same time, which I can't get to work.

[ This Message was edited by: Al Chara on 2002-04-11 07:57 ]

[Yogi Anand]

On 2002-04-11 07:56, Al Chara wrote:
Yogi,
I think Avi is trying to use criteria from two different worksheets at the same time, which I can't get to work.

[ This Message was edited by: Al Chara on 2002-04-11 07:57 ]

Hi Al:
I may not have fully understood what Avi is trying to do -- but I did try a simple example, where one of my criterion input was linked to a cell in another worksheet. In any event, why should this be a concern, as long as the D-function works -- at the most he may have to transfer the values that constitute the criteria into the worksheet where the D-function is to be used.
How do you gage the situation?

Regards!

[Al Chara]

at the most he may have to transfer the values that constitute the criteria into the worksheet where the D-function is to be used.

I agree with this.