Hopefully someone can help me!
I have a large amount of information on a excel sheet, sheet 1, which i use to input all items and costs, i then have information on a second sheet, sheet 2. I would like to use sheet 2 to cost a product which has a number of items from sheet 1 involved in so i want to extract information from sheet 1 onto sheet 2. To do this i paste a link from the cell i need in sheet 1 onto sheet 2 which is great, so then if i change a valve in sheet 1, ie an item value, it carries to sheet 2.
The problem is if i do a column sort on sheet 1 it alters all the values in sheet 2 which is not what i want as it doesn't carry the correct values anymore. I need the cell reference in sheet 2 to change consistently along with the sort of the values in sheet 1
Use the Index/Match functions to link the values on one sheet to another. (Study these two functions extensively)
The Match function will locate the row number the key value searched for is found in even if the rows are reordered.
=Match(ProductCode, 'Sheet 1'!ProductCodeColumn, 0) or
=match('Sheet 2':A1, 'Sheet 1'!A:A, 0)
Match produces a #N/A if not found, or the row number of the item that matches. (i.e. like a find first)
Insert the match function inside the index function for the Row number argument to get the value from the desired Row/Column combination.
Google INDEX/Match for complete examples.
Use this over Vlookup because it is more efficient.