database functions in excel

[Guest]

Why do DGET, DSUM, etc require a range as the criteria, and one that must be
at least 2 cells high, at that.

I've got a whole chart of things that need to call on these functions and
the one-row-mapping-into-two thing is NOT helpful.

Yes, yes, I get it, I can stick side-by-side columns off to the side
somewhere and get around it, but it's unbelievably cumbersome.

Why can't I, instead of having to use a range as criteria, just specify it
right in the function?

 

[Peo Sjoblom]

You can use other formulas to get the same result as most of the D
functions, I assume the D functions are there because Lotus had them

 

[Guest]

You can use other formulas to get the same result as most of the D functions

I know I can use vlookup in some situations and sumif in others, but neither
of them allows me to refer to a column header by name. I guess I could get
around it with named ranges and the indirect() function I just learned about,
but that's pretty cumbersome too. VLOOKUP doesn't seem to work if the
information I need is to the left of the identifier column.

Or did you mean other formulas? If so, which?

I assume the D functions are there because Lotus had them

Yes, I need to convert a whole bunch of spreadsheets from 1-2-3 to Excel

 

[Peo Sjoblom]

DSUM can be replaced by SUMPRODUCT, assume we have a table A4:C13, A4 has a
header called Names, B4 one called Cars and C4 Amounts, in E1 we can put
Names and in F1 Cars, in E2 Mr X and in F2 BMW, not to get the sum of Mr X
and BMW we would use

=DSUM(A4:C13,"Numbers",E1:F2)

(I assume E1:F2 irritates you) a formula could look like

=SUMPRODUCT(--(A4:A13="Mr X"),--(B4:B13="BMW"),C4:C13)

if we named the ranges we could use

=SUMPRODUCT(--(Names="Mr X"),--(Cars="BMW"),Numbers)

this formula also works when a workbook is closed will D formulas won't


--
Regards,

Peo Sjoblom

(No private emails please)

 

[Steve McBride]

Try Chip Pearson's left lookup formula at

http://www.cpearson.com/excel/lookups.htm#LeftLookup