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

Demosthenes&Locke

Board Regular
Joined
May 11, 2010
Messages
93
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Demosthenes&Locke

Board Regular
Joined
May 11, 2010
Messages
93
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,384
Messages
5,595,862
Members
414,027
Latest member
zippyfrog

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top