Naming arrays

[cmcgath]

I need to retrieve values from a monthly database and I cannot use Excel
database functions because they are too primitive. What I have come across
is using arrays like fields and criteria in 1-2-3 database functions like
this:

{=SUM((DBVAR="CRMUSCR--VA")*(DBYEAR=2007)*(DB_JAN))}

If I want to retrieve data from each month in separate cells, it seems to me
that I have to create at least 14 arrays, one for the variable, one for the
year, and one for each month's values. (In 1-2-3, you just set up a database
with field headings and put a field and criteria into a database function, a
much more sophisticated way to do this.)

Is there any way to accomplish what I want to do without having to name 14
arrays?

 

[Roger Govier]

Hi

Surely you just have 4 named ranges DBVAR, DBYEAR and DBMONTH and
Range_to_be_Summed

With 2007 in B1, 2008, C1 etc. and JAN, FEB ... DEC in A2:A13, enter in B2
=SUMPRODUCT((DBVAR="CRMUSCR--VA")*(DBYEAR=B$1)*(DBMONTH=$B2)*Range_to_be_Summed)
Copy across and down as required.

Better still, don't write any formulae, just use a Pivot Table.
Place cursor in your table>Data>Pivot Table>Finish
On the PT skeleton that appears on the new sheet
Drag column heading for DBVAR to the Row Area
Drag column heading for DBYear to the Page Area
Drag column heading for DBMONTH to the Column Area
Drag column heading for Range_to_be-Summed to the Data Area

 

[cmcgath]

Yes, I know how to use constant and relative cell references. That's not the
problem.

But thanks for the tip on pivot tables. I finally figured out how to
create one. However, I can't use a pivot table in the structure of my model.

 

[Roger Govier]

Can you provide a sample of your data, and explain why Pivot Tables won't
work.
If the Sumproduct formulae won't work, why?