CODEWITHSUNDEEP
excelexcel-formula
Jeff H
Jeff H

Reputation: 15

Get the value from a cell in various sheets in another workbook

I am attempting to do the following using Excel formulas and not VBA. I have two workbooks (say WB1 and WB2) and both are open in Excel. In WB1 I have a sheet with a cell (say A1) that can have different codes, part of which is the name of one of the sheets in WB2. The codes are of the form DOD8.2x1.9, DOD8.2x2.4...DOD9.2x1.9, DOD9.2x2.4.., DOD10.2x4.9 etc where the characters up to the x refer to the sheets in WB2 (i.e. DOD8.2, DOD9.2, DOD10.2 etc). I need a formula for another cell in WB1 (say B1)that can get the value of a specific cell in the sheet in WB2 that is defined by this code. I know that a formula in B1 of the form =[WB2.xlsx]DOD8.2!$EB$6 or =[WB2.xlsx]DOD10.2!$EB$6 is how to get cell values from specific sheets in WB2 and that the formula =LEFT(A1, FIND("x",A1)-1) will extract the relevant part of cell A1 to give me the name of a specific worksheet in WB2 but I cannot work out how to use this to construct a formula that combines the two to automatically select the worksheet defined by the code in A1. I've tried replacing the sheet name (e.g DOD10.2) in the general formula with LEFT(A1, FIND("x",A1)-1) so I end up with =[WB2.xlsx]LEFT(A1, FIND("x",A1)-1)!$EB$6 using different concatenation methods but none work. Can anyone suggest a way of achieving this?

Upvotes: 0

Views: 77

Answers (1)

andrewJames
andrewJames

Reputation: 22042

You are very close.

Assume you have workbook WB2 containing three worksheets as you describe: DOD8.2, DOD9.2, and DOD10.2. In cell A1 of each sheet there are the text values foo, bar, and baz, respectively.

In spreadsheet WB1 you can access these three values as follows:

=INDIRECT(CONCAT("[WB2.xlsx]", LEFT(A1, FIND("x",A1)-1), "!$A$1"))

For example:

enter image description here

The concat function builds a string representing the referent to the required sheet in WB2. The indirect function then interprets that string as an actual cell reference, and displays the contents of the cell.

If you prefer using & instead of concat, then you can use this:

=INDIRECT("[WB2.xlsx]" & LEFT(A1, FIND("x",A1)-1) & "!$A$1")

Upvotes: 0

Related Questions