Linking variable values between 2 excel files

VoipDepot

New Member
Joined
Dec 30, 2009
Messages
4
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,381
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Dec 30, 2009
Messages
4
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

New Member
Joined
Dec 30, 2009
Messages
4
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,381
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Dec 30, 2009
Messages
4
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!
 

Forum statistics

Threads
1,141,864
Messages
5,709,066
Members
421,611
Latest member
Lisa W

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top