how to update data from one datasheet to a master sheet, automatically

I had another post but it was shut down, i wasn't aware of all of the rules for posting and wasn't to familiar with excel.

I was told to use vlookup and after that i read up on it but i just can't get the hang of it as far as the complexity of my situation.

I am NOT looking to expand my knowledge of excel, i am just looking for a solution to a particular problem for my small business. rather i have to pay someone in the end or i have to pay for software to do this, it does not matter. actually i would prefer software if anyone knows of any to perform the functions i need.

in the 2 attached files, i have a work sheet i named "mysheet" and a work sheet i named "sheettoupdatefrom" {sheet to update from}.

basically i have a list of a few hundred items, within these in one of the columns, i need to have a unique column or a master column to where the information in the individual cells on this column will match up to the 2cd data sheet and auto update info in the row of my data sheet.

the new updated sheets comes out at least once a week. the columns are always the same but the rows change, meaning i cannot link the sheets to update by row number.

i need my unique name, in the example file i uploaded i named the unique file names by sports. in the example, i need to take the unique name of football, match it up to the new data sheet to update from and update the info, in the example, the updated info i would need would be the "number" column and the "color" column.

if this is confusing, in a summary, i need this:

auto update information according to unique name in a column, this will update certain info in the unique names same row.


I tried my best to explain this, if i did anything wrong please advice me via personal message instead of replies. i will update what i need to do from there.

off for next 12 hours, if someone knows of a solution, please consider i would prefer software for data management, found a product called "SYKRONIZER", anyone know of that

no one? can someone help if they know?

For your Color column

Please Login or Register  to view this content.

For the Number

Please Login or Register  to view this content.

The formula, broken down:

=IFERROR(VLOOKUP($A2,[sheettoupdatefrom.xlsx]Sheet1!$A$2:$C$5,2,0),"Not Found")

IFERROR( Formula , Result if Error)
Used in case there is a record on you list that is not on the distributor's list. If there is no match the VLOOKUP function returns the #N/A error.

VLOOKUP(What,Where (table of Data,Column Number in Where Table,Range_Lookup?)
From the above formula
VLOOKUP(
What:

A2

Where - the reference needs to be expanded to capture all data. You can use the full column notation of A:C

[sheettoupdatefrom.xlsx]Sheet1!$A$2:$C$5

Column Number

2

Range_Lookup? - 0 (zero) or FALSE results in the formula looking for an exact match. See the help file on the use of 1 (one) or TRUE

0

**end of VLOOKUP**
,"Not Found) <Value returned if VLOOKUP did not find an exact match.

Paste it in Row 2, copy that cell then hi-light the rest of the column you want to use the formula then paste the data. Adjust the formula as necessary.

awsome, it works so far, i am going to mess around with it and make sure that it works in the data i need. i will update later on.

1 more question, this does recognize data by the unique name in column A and NOT by row, correct, so on the "sheettoupdatefrom" the rows don't have to be in any particular order? just as long as corresponding unique i.d. is in column A, correct?

thanks again,

later

okay, i found a small problem, should it be A:A instead of $A2?

=IFERROR(VLOOKUP($A2,[sheettoupdatefrom.xlsx]Sheet1!$A$2:$C$5,2,0),"Not Found")

=IFERROR(VLOOKUP

this would be on "myproduct" column A correct, does a2 mean row 2 and down, or do i have to put in A:A?
($A2,

this links it to the sheettoupdatefrom, sheet 1, just plug in the name and open the document, correct? now as far as the $A$2 and C$5$, i don't quite understand what this is for, i understand it is the table, but what is it looking for?

[sheettoupdatefrom.xlsx]Sheet1!$A$2:$C$5,

column 2, from the left, from the right? from the A2?, what column is this exactly on the "sheettoupdatefrom?"
2,

exact match.
0

),"Not Found")


_____

I guess i am just not understand how it links the information. do the column headers have to be the exact same for every column?

okay i figured it out for the most part, appreciate the help, my hopefully last question is this.

to have the cell be blank instead of "not found" do i just leave it blank in between the " ", i know it sounds common sense but with computer programming, nothing is really common sense.

and if i wanted to leave the cell how it originally was, is there a way to do that?

to have the cell be blank instead of "not found" do i just leave it blank in between the " ", i know it sounds common sense but with computer programming, nothing is really common sense.

Correct

and if i wanted to leave the cell how it originally was, is there a way to do that?

Since the data is formula driven, once you change the distributor's sheet, the formulas will update the data.

An option would be to have a sheet containing a list of the items whose data you want to carry over. You could then adjust the formula to check that sheet for the item, and if it is there, use that data instead of the new sheet. Something like:

Please Login or Register  to view this content.