I need a formula that will search a worksheet "Source" for specific data
"account#"
and return data in the same row but in a different column "balance". The
data would populate a different worksheet (destination).

Example Source:
Account Balance
123 100
152 150
942 99

I need the formula to search through the account numbers for a specific
number and return the balance to the other sheet.
Notes:
The source data sheet has blank rows.
The source data is updated regularly.
New accounts may be added to the source data.


The offset function has worked but requires extra work as the source data
sheet must be open to make the destination fields fill in. Once the source
data sheet is closed or deleted the destination fields have error values.

I believe the DGET function may have the answer but can not figure out the
syntax.

DGET does not work with closed workbooks either, you can use VLOOKUP if it is
only a single value you need, if you need some sort of summary you can use
sumproduct, tips create the formula when all workbooks are open, that way you
can switch workbooks and use the mouse and click to get the address part,
then you can close the source and excel will put in the path for you.

=VLOOKUP(A2,[test.xls]Sheet3!$A$1:$B$50,2,0)

where A2 holds the lookup vaue (account #) and Test.xls Sheet3 A1:A50 holds
the account # in the table and B1:B50 the balance


Regards,

Peo Sjoblom

"Jordan" wrote:

I need a formula that will search a worksheet "Source" for specific data
"account#"
and return data in the same row but in a different column "balance". The
data would populate a different worksheet (destination).

Example Source:
Account Balance
123 100
152 150
942 99

I need the formula to search through the account numbers for a specific
number and return the balance to the other sheet.
Notes:
The source data sheet has blank rows.
The source data is updated regularly.
New accounts may be added to the source data.


The offset function has worked but requires extra work as the source data
sheet must be open to make the destination fields fill in. Once the source
data sheet is closed or deleted the destination fields have error values.

I believe the DGET function may have the answer but can not figure out the
syntax.

Thank you. Unfortunatly VLookup doesn't work as it stops searching the
column for the reference value as soon as there is an empty row.

Perhaps it would be easier to modify the source data than to find a function
that will work with closed source worksheets.

"Peo Sjoblom" wrote:

DGET does not work with closed workbooks either, you can use VLOOKUP if it is
only a single value you need, if you need some sort of summary you can use
sumproduct, tips create the formula when all workbooks are open, that way you
can switch workbooks and use the mouse and click to get the address part,
then you can close the source and excel will put in the path for you.

=VLOOKUP(A2,[test.xls]Sheet3!$A$1:$B$50,2,0)

where A2 holds the lookup vaue (account #) and Test.xls Sheet3 A1:A50 holds
the account # in the table and B1:B50 the balance


Regards,

Peo Sjoblom

"Jordan" wrote:

I need a formula that will search a worksheet "Source" for specific data
"account#"
and return data in the same row but in a different column "balance". The
data would populate a different worksheet (destination).

Example Source:
Account Balance
123 100
152 150
942 99

I need the formula to search through the account numbers for a specific
number and return the balance to the other sheet.
Notes:
The source data sheet has blank rows.
The source data is updated regularly.
New accounts may be added to the source data.


The offset function has worked but requires extra work as the source data
sheet must be open to make the destination fields fill in. Once the source
data sheet is closed or deleted the destination fields have error values.

I believe the DGET function may have the answer but can not figure out the
syntax.