I want to extract data from a list that I have in excel.
I don't want to use pivit table method. I want to skip
the query idea and have data linked to other sheets. Is
there anyone experienced with the database functions of
excel?
Yes, but that experience makes me use other functions to accomplish the same
result. If you have a table (including field names) like
Foo Bar XYZ
1 A 9
2 A 8
1 B 7
2 B 6
and a criteria range like
Foo Bar
Then =DGET(TableRange,3,CriteriaRange) would return 6. If you have only one set
of criteria you need to use at a time, DSUM, DCOUNT and the other database
functions would be OK. DGET, on the other hand, will only return a value if
there is one and *ONLY* one record in the table matching the criteria.
The main alternative to DGET is an array formula like
=INDEX(TableRange,
MATCH(1,(INDEX(TableRange,0,1)>1)*(INDEX(TableRange,0,2)="B"),0),3)
if you don't mind hard-coding column indexes, or more generally
=INDEX(TableRange,
MATCH(1,(INDEX(TableRange,0,MATCH("Foo",INDEX(TableRange,1,0),0))>1)
*(INDEX(TableRange,0,MATCH("Bar",INDEX(TableRange,1,0),0))="B"),0),
MATCH("XYZ",INDEX(TableRange,1,0),0))
You could also use the SQL.REQUEST function if you name your TableRange.
DCOUNT would be accomplished by
=SUMPRODUCT((INDEX(TableRange,0,1)>1)*(INDEX(TableRange,0,2)="B"))
DSUM would be accomplished by
=SUMPRODUCT((INDEX(TableRange,0,1)>1)*(INDEX(TableRange,0,2)="B"),
INDEX(TableRange,0,3))
both using hard-coded column indexes.
