Enter a value in wks1-Find that value on wks2-Auto fill cells in w

[danno-c]

I don't know if I can clearly explain what I want to accomplish because I am
not well versed in Excel terminology. But here is what I'm trying to do using
Excel 2000.

When I enter a data value into a cell (i.e a product code #) on my "active"
worksheet; I want Excel to look for that data value on any one of three other
worksheets (i.e products1, products2, products3) and have multiple cells on
the active worksheet automatically populated with product data from the
products worksheet that holds the data associated with the product code
entered.

My active worksheet is dynamic, in that it will receive a data entry value
of "product code" in any row but always within the same column.

The product worksheets from which I wish to retrieve data from are set up
with product code in column A and data related to the product are contained
on the same row as the product code but within multiple columns. (i.e price
in column B, description in column C, weight in column D, etc.)

If you understand what I have described God bless you and if you can show me
and easy way to do this in Excel God bless me.

I have a limited technical knowledge of both Access and Excel but I'm
inclined to believe that what I'm describing is something that should be done
using Access. I know more about Excel than I do about Access so I'm hoping
there is an easy way to do it in Excel.

Thank you for your indulgence, understanding and any help you can provide.

Thanks,
Dan

 

[Pete_UK]

Hi Dan,

try something like this:

=IF(ISNA(MATCH($A1,products1!$A:$A,0)),IF(ISNA(MATCH($A1,products2!$A:
$A,0)),IF(ISNA(MATCH($A1,products3!$A:$A,0)),"not
found",VLOOKUP($A1,products3!$A:$G,COLUMN(B1),
0)),VLOOKUP($A1,products2!$A:$G,COLUMN(B1),0)),VLOOKUP($A1,products1!
$A:$G,COLUMN(B1),0))

I have assumed that the product code you are looking for is entered in
A1 of your active sheet, so that this formula would be put into B1. If
it is not in A1, then you will need to change all references to $A1 (6
of them) to suit your set up. I've also assumed that you have data in
columns A to G of your products sheet - if more then you will need to
adjust $A:$G (3 times).

Once you have it set up correctly to suit your data, copy it across
from B1 into C1:G1, and then you can copy B1:G1 down for as many rows
as you are using.

Note that the formula is all one long formula - be wary of spurious
line breaks that the newsgroup reader might insert.

Hope this helps.

Pete

 

[danno-c]

Pete,
Thanks for your response, it was very timely and very helpful. You have
given me enough information to be even more dangerous than I have been.
thanks, Dan

 

[Pete_UK]

You're welcome, Dan - thanks for feeding back.

Pete