Use cell value for file name in excel formula

Question

I am trying to create a formula that will reference a cell in another workbook in a different folder. The bit I'm getting stuck on is using a cell value in place of the file name.

='C:\Users\Damien\Documents\Personal\Coffee\Workcards\[UGAG003.xlsx]Checklist'!$B$4

In the code above, I want UWEG003 to be replaced with the value of cell B3 in the current workbook and return the value of cell $B$4 in UGAG003.xlsx. I want to use the cell reference because i need to autofill the whole column. Essentially what I am trying to get is;

='C:\Users\Damien\Documents\Personal\Coffee\Workcards\[ & $B3 & .xlsx]Checklist'!$B$4

That way I'll be able to just drag it down the whole column.

I hope I've worded that more clearly this time...

I appreciate the help.

I have tried this...

=INDIRECT("'C:\Users\Damien\Documents\Personal\Coffee\Workcards\[" & $B13 & ".xlsx]Checklist'!$B$4")

and it works (sort of). However, it only works if the document is open. How can I make it visible regardless of whether the document is open or not?

Perhaps you need to use indirect() to build the name form the contents of cell B3. — Solar Mike, Feb 08 '23 at 14:15
I tried that for ages and I couldn't get it to work. I looked at a heap of different examples but I kept getting errors. — Schming, Feb 08 '23 at 14:25
So many examples of indirect() on here, this is just one: https://stackoverflow.com/a/63934822/4961700 — Solar Mike, Feb 08 '23 at 14:55

score 0 · Answer 1 · answered Feb 08 '23 at 15:45

The INDIRECT() function won't work here, because:

I need it to be visible even if the other worksheet isn't open

If you are really sure that having the workbook name in a cell for reference is the best way to go - I'd suggest going with a parameter query.

score 0 · Answer 2 · answered Feb 08 '23 at 16:06

I recommend using powerquery just like ttobe said. You have not shown much so I will not show much either but I will guess at what you have. Look at this for an example.

Your current sheet has a table with columns and one value in the column is "Hours Spent On Job". Turn this (all columns) data into a Table (ctrl + t). Under Data you will need to Get & Transform From Table/Range. This is one table you can load and close.

Go back to Get Data > From File now choose file you will need to do the same with [UWEG002.xlsx]Checklist'.

Next you will merge the tables together then expand the columns that you want in power query.

score 0 · Answer 3 · answered Feb 14 '23 at 20:25

I don't think you will be able to achieve what you want with Excel, because, as you said, the workbook you're trying to access the information from is not open. The easiest way is probably Power Query.

Since you did not post a sample data set, I made one up.

Table 1 (contained in a file I called Book2.xlsx):

+---------+
| MyData1 |
+---------+
|    1000 |
|    1005 |
|    1010 |
|    1015 |
|    1020 |
|    1025 |
|    1030 |
|    1035 |
|    1040 |
|    1045 |
|    1050 |
|    1055 |
|    1060 |
|    1065 |
+---------+

Table 2 (contained in a file I called Book1.xlsx):

+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+

The goal is to pull the value of "4" into a second column in Book2.xlsx.

For this, I navigated to the "Data" tab in Book1.xlsx and pulled the table into Power Query as a connection only. I called the query "Book1". (In Excel, go to Data > Get Data > From File, then navigate to the file you want to access.)

I then pulled in the data from Book2.xlsx. When you do this with data that has a header and you mark it as such, Excel automatically converts your range to a table object. Here it's called "Table1", and I left it at that for the query name.

As a last step, I added a custom column as follows in the Table1 query:

= Book1[Column1]{3}

The {3} is the zero-based position of the value that you want from the other query.

The result is a column that automatically fills each row with the value you specified from the other file, and it does it without the issues you were experiencing due to the other file not being open.

This is the M code for the Book1 query:

let
    Source = Excel.Workbook(File.Contents("PATHTOFILE\Book1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", Int64.Type}})
in
    #"Changed Type"

Here is the M code for the Table1 query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MyData1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FromBook1", each Book1[Column1]{3})
in
    #"Added Custom"

A caveat: the path to the first file (the one you want to extract the information from) is hard-coded into the M code, which can cause issues if the file is later moved. There are work-arounds for this, but that goes beyond of the scope of your question.

The result:

Van · Answer 4 · 2023-02-18T01:41:46.277

0

I have encountered this problem. Your B4 cell of Excel maybe contains the unrealized functions of the poi. you must register the excel of function in your project first. you can use it. Maybe this website can help you : code and register function

edited Feb 18 '23 at 01:41

answered Feb 18 '23 at 01:35

Van

1
1

Adam Kovari · Answer 5 · 2023-02-18T08:30:45.430

Simply use powerquery as mentioned above.

Establish a connection tothe 2nd workbook "C:\Users\Damien\Documents\Personal\Coffee\Workcards[UGAG003.xlsx]"
Reference the "Checklist" as the data source, and load the data from it to where you desire, e.g. a new sheet in the 1st workbook.
Then simply use lookup, or directly reference the "loaded" data columns. Data as from 2nd closed workbook
Each time you need to refresh, just hit refresh on the query.

Use cell value for file name in excel formula

5 Answers5