# Closed workbook, external indirect limitation, variable workbook name linking, overcome limitation?

#### Demosthenes&Locke

##### Board Regular
I have a workbook that is named based on the entry in a cell when the person selects save as. These workbooks are always in the same place and will always be the same formatting of the date:
08-02-10-to-08-08-10.xls
08-09-10-to-08-15-10.xls
Now because the workbook is based on a variable set within itself, I can also calculate the previous workbook's name using that same variable.

I want to be able to link to my previous workbook without having to manually put in each link. I've tried using indirect also, and both ways an extra pair of " quote marks show up, and I presume that is what is screwing the proverbial pooch. Anyone know another way to calculate the name of a workbook and have it work?

I'm thinking of next trying to add in vb the calculation and the setting of a cell to the formula I need to pull up the closed workbook, I just wanted to be able to make it in a cell.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Demosthenes&Locke

##### Board Regular
This was the formula I was using, and during evaluation it puts an extra pair of quotes around the external name.
=A17+D17+(( "'" & LEFT(CELL("filename",A1),(FIND("[",CELL("filename",A1))-1)) & "[" & TEXT((C4-7),"mm-dd-yy") &" -to-" & TEXT((C4-1),"mm-dd-yy") & ".xls]Sheet1'!A28" ))
A17 and D17 were the two values from this current sheet, the linked cell was the same cell this formula was in on another sheet, to kind of keep a running tally as it were from each workbook to the next
When watching it evaluate, it evaluates the name correctly, and the values added together correctly, but when it came time to lookup the linked data, there was an extra " mark around the link. Anyone know any way to remove those extra "'s?

This is what I had to put in the sheet to do what I want it to do.
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C4"), Target) Is Nothing Then
test = "=A17+D17+'" & Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) & "[" & Format((Range("C4").Value - 7), "mm-dd-yy") & "-to-" & Format((Range("C4").Value - 1), "mm-dd-yy") & ".xls]Sheet1'!A28"
test2 = "=B17+'" & Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) & "[" & Format((Range("C4").Value - 7), "mm-dd-yy") & "-to-" & Format((Range("C4").Value - 1), "mm-dd-yy") & ".xls]Sheet1'!B28"
test3 = "=C17+'" & Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) & "[" & Format((Range("C4").Value - 7), "mm-dd-yy") & "-to-" & Format((Range("C4").Value - 1), "mm-dd-yy") & ".xls]Sheet1'!C28"
test4 = "=G19+'" & Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) & "[" & Format((Range("C4").Value - 7), "mm-dd-yy") & "-to-" & Format((Range("C4").Value - 1), "mm-dd-yy") & ".xls]Sheet1'!D28"
Range("A28").Value = test
Range("B28").Value = test2
Range("C28").Value = test3
Range("D28").Value = test4
End If
End Sub``````

C4 is my date, the cells A28-D28 contain the values I'm carrying a runniny tally of. The vb version of this works, but if someone can tell me how to remove the quotes from the cell's evaluation, I could do this with a formula.

Replies
1
Views
467
Replies
4
Views
169
Replies
2
Views
951
Replies
3
Views
603
Replies
3
Views
130

1,127,557
Messages
5,625,500
Members
416,113
Latest member
Zulwaqar88

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back