Matching data/columns from two seperate files

[Mike]

Can someone tell me the best way to list data that comes from two seperate
files?

Example: I have two files
1) A Plant & Store master excel file (list all store numbers and the plant)
2) Another excel file that list store numbers and a store score.

Would like to create a third file/report that shows the plant, store number,
and score (and ensure the master file poplulates the report plant & store
number). This report would be updated from the original two files (they get
saved quarterly so want this report updated automatically).

Thanks for any suggestions.

 

[Sheeloo]

Essentially you need to use VLOOKUP for the store score where you have Plant
and Store Number.

Depending upon your preferences you can write a Macro which copies the
master file and then adds VOOKUP to the third column or keep a template where
you paste the first two rows from the master and have the VLOOKUP in the
third column.

 

[Mike]

Great, but no experience with Macro's.. Can I get a description?

 

[TomPl]

You simply need to put lookup formulas in a copy of the Plant & Store file.
Something like:

=VLOOKUP(B2,[Book6]Sheet1!$A$1:$B$5,2,FALSE)

Where B2 is the cell with the store number, [Book6] is the name of the
"Store Score" file, Sheet1 is the name of the sheet in the "Store Score" file
and $A$1:$B$5 is the range on Sheet1 that has the Store Number and the Store
Score, and 2 is the number of the column in that range that has the Store
Score. The Store Number must be the first column in that range.

Let me know if I can confuse you further.

Tom

 

[Mike]

Why would I put the lookup formula in the plant & store file? I am trying to
get the information in the report file, but matching what is in the other two
files.

You simply need to put lookup formulas in a copy of the Plant & Store file.
Something like:

=VLOOKUP(B2,[Book6]Sheet1!$A$1:$B$5,2,FALSE)

Where B2 is the cell with the store number, [Book6] is the name of the
"Store Score" file, Sheet1 is the name of the sheet in the "Store Score" file
and $A$1:$B$5 is the range on Sheet1 that has the Store Number and the Store
Score, and 2 is the number of the column in that range that has the Store
Score. The Store Number must be the first column in that range.

Let me know if I can confuse you further.

Tom

Can someone tell me the best way to list data that comes from two seperate
files?

Example: I have two files
1) A Plant & Store master excel file (list all store numbers and the plant)
2) Another excel file that list store numbers and a store score.

Would like to create a third file/report that shows the plant, store number,
and score (and ensure the master file poplulates the report plant & store
number). This report would be updated from the original two files (they get
saved quarterly so want this report updated automatically).

Thanks for any suggestions.

 

[TomPl]

I assumed that the Plant & Store file is static (i.e. the list of plants and
stores does not change). So, your Report file would have a copy of the Plant
& Store data in it and you could just lookup the Score.

Does that make sense?

Why would I put the lookup formula in the plant & store file? I am trying to
get the information in the report file, but matching what is in the other two
files.

You simply need to put lookup formulas in a copy of the Plant & Store file.
Something like:

=VLOOKUP(B2,[Book6]Sheet1!$A$1:$B$5,2,FALSE)

Where B2 is the cell with the store number, [Book6] is the name of the
"Store Score" file, Sheet1 is the name of the sheet in the "Store Score" file
and $A$1:$B$5 is the range on Sheet1 that has the Store Number and the Store
Score, and 2 is the number of the column in that range that has the Store
Score. The Store Number must be the first column in that range.

Let me know if I can confuse you further.

Tom

Can someone tell me the best way to list data that comes from two seperate
files?

Example: I have two files
1) A Plant & Store master excel file (list all store numbers and the plant)
2) Another excel file that list store numbers and a store score.

Would like to create a third file/report that shows the plant, store number,
and score (and ensure the master file poplulates the report plant & store
number). This report would be updated from the original two files (they get
saved quarterly so want this report updated automatically).

Thanks for any suggestions.