VLOOKUP issues - Looking up specific number from seperate spreadsheet to populate new one

[Karroog]

Bear with me as this is even a tad complicated for me to get my head wrapped around. (Guess that's what happens when people get laid off!)

I have one spreadsheet with text in English, we'll call it Formatted_Translations.xlsx. Column A contains a short description of what is being translated, Column B has the text in English that went out for translations, and Column C through M needs to show the text in the specific language called out within the column header.

I have completely separate spreadsheet, call it Supplied_Translations.xlsx, with text in multiple other languages. I am trying to grab the specific translations supplied and pull them into their specific locations on my formatted translations spreadsheet. The problem of course is there is no uniformity between the two spreadsheets, and I do not wish to go one cell by one cell populating each language for each statement.

One of the great things though that can match up is that both spreadsheets have a reference code.

For our purposes, the short example will be that the Formatted Translations spreadsheet has a reference number 123ABC. This spreadsheet will contain a workbook for each reference number, so the reference number within this example is found on [Formatted_Translations.xlsx]123ABC'$B$2

The Supplied Translations spreadsheet has multiple workbooks that contain specific information that I'm trying to pull by reference number. Each workbook though has the reference codes by a huge list with one specific phrase being translated

For this example I am trying to pull the phrase "The Cat is underneath the table.", located on [Supplied_Translations.xlsx]'$C2, the reference code is on B2. D2 has the translation, "Le chat est sous la table." D2 if you can tell is French, E2 has german, F2 has italian and so on and so forth.

I had thought a VLOOKUP would work well, but I am at a brick wall it seems on how to even start this thing. What I currently have:

=VLOOKUP(B2,[Supplied_Translations.xlsx]'$B,1,2)

I know, not that much, but that's why I'm here! So I had thought the VLOOKUP would view the reference code column, go to the other spreadsheet I have, yes they are in the same folder, and within the Description workbook look up column B to find that reference code, in turn pulling in the translation that it is tied to in the D column.

I might be missing some steps on making this thing work, but that's why I like this forum so much, you guys and gals are helpful! This also might be completely confusing as I'm re-reading this, but any help would be much appreciated. I'll try to answer questions as they come.

[Ace_XL]

Modify to

Please Login or Register  to view this content.

This will return value in Column D
Increment the highlighted cell by 1 for Col E, F, G and so on