Excel 2007 : use a variable in formula for a workbookname

**Generon** · 01-11-2012, 08:28 AM

Hi,

I have different workbooks with data.
eg workbook "data_red". workbook "data_blue" and workbook "data_green". The sheetnames are the same in the different workbooks.

Now I have a workbook called "Overview". In this workbook I have a formula in cells A10..A110 that is linked to data in workbook "data_blue" (=[Data_blue.xlsx]Sheet1!$A$1 and so on.

If I want to use the data from the other workbook, eg "data_green.xlsx", I have to change al the formulas in the cells of workbook " Overview".

What I like to do is to put a variable in the formula =[Data_blue.xlsx]Sheet1!$A$1 on the position of the workbook [Data_blue.xlsx]. So that I have a cell B1 in the Overview workbook where I put the name of one of the other workbooks, eg workbook "data_blue". And that the formula now is changed to get the data from the other workbook.

Hopefully do you understand what I mean. And do you have a solution for it

**NBVC** · 01-11-2012, 08:52 AM

The INDIRECT function allows for that, but unfortunately, it only works with open workbooks.

e.g.

=INDIRECT('"["&$B$1&".xlsx]Sheet1'!$A$1")

If you can download and install a free addin called Morefunc

then you can use the INDIRECT.EXT to work with closed workbooks

=INDIRECT.EXT('"["&$B$1&".xlsx]Sheet1'!$A$1")

Excel 2007 : use a variable in formula for a workbookname

LinkBack

Thread Tools

Rate This Thread

Display

use a variable in formula for a workbookname

Re: use a variable in formula for a workbookname

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions