Need help with DGET function

B

Bob

I have a workbook with the following two worksheets and associated columns:

Sheet1
Column A = Factory ID
Column B = Department ID
Column C = Part ID

Sheet2
Column D = Factory ID
Column E = Department ID
Column F = Department Name
Column G = Part ID

I would like to use the DGET function (as opposed to using VBA) to obtain
the Part ID from Sheet1 and automatically populate the same field (column G)
on Sheet2 based on a user first inputting the Factory ID and Department ID on
Sheet2.

Can this be done? Thanks in advance for any help.

Bob
 
A

Ashish Mathur

Hi,

You may use this formula. A3:C8 contains the data on sheet 1. row 3 is the
header row. E4 has Part ID (this should be spelled the same way as the Part
ID on sheet1). Sheet2!B4:C5 contains the headers and the criteria

=DGET(Sheet1!$A$3:$C$8,Sheet2!E4,Sheet2!B4:C5)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
B

Bob

Ashish,

Thanks for your help. However, I know how to use DGET. What I was looking
for was a solution that would allow me to use "rolling" (for lack of a better
term) criteria. Your solution is for a single row of data. I deliberately
described my problem in terms of columns because I need to somehow vary the
criteria so that it points to the Factory ID and Department ID on Sheet2 for
each and every row that contains these two data elements.

I don't know if such a solution is possible, so I reached out to this
Discussion Forum to see if such a solution existed.

Bob
 
B

Bob

Ashish,

I didn't think so, but I though I would ask anyway. The Part ID is a
numeric field.

Thanks for your help,
Bob
 
A

Ashish Mathur

Hi,

You could use this formula in column G of sheet2

=sumproduct((sheet1!$A$3:$A$100=D3)*(sheet1!$B$3:$B$100=E3)*(sheet1!$C$3:$C$100))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
B

Bob

Ashish,

Your SUMPRODUCT formula did the trick. Thanks!!!

Two follow-up questions:

I read somewhere that SUMPRODUCT uses significantly more system resources
than DGET. Is that true?

I realize that SUMPRODUCT only works with numeric data. If I wanted to
retrieve alphnumeric data instead, am I out of luck or is there some other
alternative that will accomplish the same thing as your SUMPRODUCT formula
did?

Thanks again for all your help,
Bob
 
A

Ashish Mathur

Hi,

Thank you for the feedback. I am glad that the solution helped. To answer
your specific questions

1. If you have a very large dataset, say something like 40,000 rows of
data, I would not use the SUMPRODUCT() function. In that case, the Database
functions would be much faster. If you have a couple of thousand rows, the
SUMPRODUCT() should not adversely impact performance. So do let me know how
may rows you have?

2. If the column to be retrieved is non numeric, you may use the INDEX()
and MATCH() array formula (Ctrl+Shift+Enter)

=INDEX(sheet1!$C$3:$C$100,match(1,(sheet1!$A$3:$A$100=D3)*(sheet1!$B$3:$B$100=E3),0)1)

Please note that this formula would be even slower than the SUMPRODUCT().

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top