Linking variable values between 2 excel files

[VoipDepot]

Hi guys

I have a though question and my search hasn't paid anything off, so this is my last resort let's say.

I have 2 excel files.

Excel A : excel file from our supplier
Excel B : excel file containing our products we sell

The point is having values where our purchase price from our supplier is located in Excel A, linked to a certain fixed column/row in our Excel B.

This would give us a very clear overview since our supplier provides us with daily .csv files. However this .csv mentions thousands of products not relevant for us.

I managed to do this, but I used absolute formulas. Now products are being added into that excel A from our supplier, so my formule is wrong and shows other purchase prices since a product has been added and the location is wrong now.

An example : ='[voipdepot-supplier.xlsx]voipdepot-supplier'!$J$15928

Concrete :

How can I link the CORRECT purchase price to my Excel B eventhough the row changes?

In the above example the correct row now is J15936 since products are added.

Column will always remain the same, it's only for the row this is the problem..

Suggestion :

Every product has a UNIQUE code in its row.

I would think I can do the following in Excel B: search for that unique code through a formula in which the result is the row number, in this case 15936.

Then add another formula that tells my Excel B to search for that value in column J and row 15936.

I hope this is somewhat clear and someone can provide me with some useful information.

Thank you sooo very much for helping me out on this one, because this would save a lot of time!!

Hope to hear from you guys soon!

Friendly greetings



Frederik Maertens
VoipDepot

 

[RoryA]

Assuming A2 in workbook B contains your unique product ID, and you are looking for that in column A on workbook A, then you could use this formula:
=INDEX('[voipdepot-supplier.xlsx]voipdepot-supplier'!$J:$J,match(A2,'[voipdepot-supplier.xlsx]voipdepot-supplier'!$A:$A,0))

 

[VoipDepot]

Assuming A2 in workbook B contains your unique product ID, and you are looking for that in column A on workbook A, then you could use this formula:
=INDEX('[voipdepot-supplier.xlsx]voipdepot-supplier'!$J:$J,match(A2,'[voipdepot-supplier.xlsx]voipdepot-supplier'!$A:$A,0))

I'll try this it in a few moments, thank you for your very very swift reply Rorya!

Keep you updated in a few moments if it works or not.

 

[VoipDepot]

Assuming A2 in workbook B contains your unique product ID, and you are looking for that in column A on workbook A, then you could use this formula:
=INDEX('[voipdepot-supplier.xlsx]voipdepot-supplier'!$J:$J,match(A2,'[voipdepot-supplier.xlsx]voipdepot-supplier'!$A:$A,0))

Hi

This is the formula with the correct values and references :

=INDEX('[voipdepot-allnet.xlsx]voipdepot-allnet'!$J:$J,match(65762,'[voipdepot-allnet.xlsx]voipdepot-allnet'!$B:$B,0))

However he gives me an error on this exact part :

$J,match

This is highlighted.

Any idea?

Thanks again!

 

[RoryA]

I guess you use different separators in your formulas? Try this:

=INDEX('[voipdepot-allnet.xlsx]voipdepot-allnet'!$J:$J;match(65762;'[voipdepot-allnet.xlsx]voipdepot-allnet'!$B:$B;0))

 

[VoipDepot]

Assuming A2 in workbook B contains your unique product ID, and you are looking for that in column A on workbook A, then you could use this formula:
=INDEX('[voipdepot-supplier.xlsx]voipdepot-supplier'!$J:$J,match(A2,'[voipdepot-supplier.xlsx]voipdepot-supplier'!$A:$A,0))

I was experimenting with your formula. I should have mentioned i'm using Excel 2010.

And it works!!!! :D

The correct formula is however :

=INDEX('[voipdepot-allnet.xlsx]voipdepot-allnet'!$J:$J;MATCH(60608;'[voipdepot-allnet.xlsx]voipdepot-allnet'!$B:$B;0))

the commas need to be ;

You single handedly saved me soooooooooooooo much work, thanks a million!

If you are in need of Voip hardware, just drop by at www.voipdepot.be and I get some special pricing to you!

This thread can be closed now.

Thanks!