SOLVED

[SOLVED]Retrieve data from one excel file to another.

%3CLINGO-SUB%20id%3D%22lingo-sub-3195567%22%20slang%3D%22en-US%22%3ERetrieve%20data%20from%20one%20excel%20file%20to%20another.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195567%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EHi!%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EI'm%20pretty%20green%20on%20formulas%20in%20Excel.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EUses%20Office%20365.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EI%20have%20an%20Excel%20file%20with%20a%20customer%20list%2C%20currently%20about%203300%20lines.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EI%20would%20need%20to%20be%20able%20to%20retrieve%20the%20contents%20of%20a%20cell%20in%20a%20specific%20row.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3ELet's%20say%20that%20the%20%22B%22%20column%20contains%20what%20I%20want%20and%20want%20to%20transfer%20to%20my%20other%20Excel%20file%20which%20is%20a%20Work%20Order.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EIf%20I%20put%20the%20Work%20Order%20in%20a%20sheet%20in%20the%20Customer%20List%2C%20I%20get%20everything%20to%20work%20exactly%20as%20I%20want%20with%20the%20formula.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3D%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EINDIRECT%20(%22'%22%20%26amp%3B%20W4%20%26amp%3B%20%22'!%22%20%26amp%3B%20%22B%22%20%26amp%3B%20T7)%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3E%2F%2F%20W4%20is%20the%20cell%20where%20the%20name%20of%20the%20sheet%20is%20written%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3E%2F%2F%20In%20T7%20I%20write%20which%20line%20I%20want%20to%20pick%20from.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EWhat%20I%20do%20is%20that%20I%20write%20in%20a%20Cell%20which%20line%20I%20want%20to%20get%20Tex%20%22Name%22%20from%2C%20say%20line%20%222376%22%20and%20then%20I%20get%20%22Peter%22.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EBut%20I%20can%20not%20get%20the%20connection%20together%20if%20I%20have%20%22Work%20Order%22%20in%20a%20separate%20file!%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3E%2F%2F%20Jonas%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3195567%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3195618%22%20slang%3D%22en-US%22%3ERe%3A%20Retrieve%20data%20from%20one%20excel%20file%20to%20another.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313910%22%20target%3D%22_blank%22%3E%40JonasH73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDIRECT(%22'%22%26amp%3BSheet2!W4%26amp%3B%22'!%22%26amp%3B%22B%22%26amp%3BT7)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20it%20work%20with%20this%20formula%20if%20you%20enter%26nbsp%3B%3CSPAN%3E%22Work%20Order%22%20in%20cell%20W4%20of%20%22Sheet2%22%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3195619%22%20slang%3D%22en-US%22%3ERe%3A%20Retrieve%20data%20from%20one%20excel%20file%20to%20another.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195619%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313910%22%20target%3D%22_blank%22%3E%40JonasH73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20do%20that%20but%20only%20if%20the%20other%20workbook%20is%20open%20in%20Excel%20too%2C%20otherwise%20INDIRECT%20will%20return%20%23REF!%3C%2FP%3E%0A%3CP%3ESo%20you%20might%20as%20well%20keep%20the%20sheet%20in%20the%20same%20workbook.%3C%2FP%3E%0A%3CP%3EBut%20if%20you%20want%20it%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3E%3D%3CSPAN%3EINDIRECT%20(%22'%5B%22%26nbsp%3B%20%26amp%3B%20W5%20%26amp%3B%20%22%5D%22%20%26amp%3B%20W4%20%26amp%3B%20%22'!%22%20%26amp%3B%20%22B%22%20%26amp%3B%20T7)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhere%20cell%20W5%20contains%20the%20filename%20of%20the%20other%20workbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

Hi!

I'm pretty green on formulas in Excel.

Uses Office 365.

I have an Excel file with a customer list, currently about 3300 lines.

I would need to be able to retrieve the contents of a cell in a specific row.

Let's say that the "B" column contains what I want and want to transfer to my other Excel file which is a Work Order.

If I put the Work Order in a sheet in the Customer List, I get everything to work exactly as I want with the formula.

 

=INDIRECT ("'" & W4 & "'!" & "B" & T7)

 

// W4 is the cell where the name of the sheet is written

// In T7 I write which line I want to pick from.

 

What I do is that I write in a Cell which line I want to get Tex "Name" from, say line "2376" and then I get "Peter".

But I can not get the connection together if I have "Work Order" in a separate file!

// Jonas

2 Replies
best response confirmed by JonasH73 (New Contributor)
Solution

@JonasH73 

You can do that but only if the other workbook is open in Excel too, otherwise INDIRECT will return #REF!

So you might as well keep the sheet in the same workbook.

But if you want it:

 

=INDIRECT ("'["  & W5 & "]" & W4 & "'!" & "B" & T7)

 

where cell W5 contains the filename of the other workbook.

 

THANKS!
//Jonas