Edit Formula to link to different excel files

[cdotyii]

Excel Forum Solution.xlsx

Attached is a base file

What I am looking for is a way to change the formulas in section F12:G29 to reference the file in cells F37 & G37 respectively. I know I could build the whole thing out of CONCATENATEs then copy/paste special values and add a "+" in front to make it a formula again, but there has to be a better solution.

Will need to do this multiple times a week linking to other spreadsheets - The formulas will stay the same linking to the same spot in other sheets.

[FDibbins]

I will take a look at this for you, but I am curious about 1 thing....

why would you set the fonts to something like 28, then zoom out to 40%? I set font to 12 and zoom to 100% and theuy both look the same (after fiddling about with row heights and column widths)

At 40% zoom, the column letters abnd rown are barely readable

[cdotyii]

LOL, I did not build the original Spreadsheet! I asked the same ? May have something to do with the picture of the locations that are embedded in row 5 that I removed.

[FDibbins]

OK, looking at your file again...
1. F12:G12 is actually a text entry, not a formula.
2. To use a text entry (like F37) to reference another sheet or workbook, you would use INDIRECT(). However this only works on open workbooks, so both the 5 year and 10 year files would need to be open
A work-around to this would be to install the More-func addin, whick allows INDIRECT() to access closed files.

Have you considered combining both 5 and 10 inso 1 file, possibly on 1 sheet, even? That way, you dont need to reference different files and sheets

[FDibbins]

LOL, I did not build the original Spreadsheet! I asked the same ? May have something to do with the picture of the locations that are embedded in row 5 that I removed.

Those are still there, and they could be re-sized too, if needed

[cdotyii]

Do you not see the

=CONCATENATE(" Floors ",'Mt Prospect 5 year.xlsx'!AddressSuite," "," Total Leased Space = ",TEXT('Mt Prospect 5 year.xlsx'!RSF,"##,##")," RSF")

Formula in F7?

Would Be

=CONCATENATE(" Floors ",'XX New File.xlsx XX'!AddressSuite," "," Total Leased Space = ",TEXT('XX New File.xlsx XX'!RSF,"##,##")," RSF")

For new columns

[cdotyii]

What I am trying to do is build an analysis snapshot for several considered deals. So, for each overall deal, there would be several proposals. Each proposal is in its own spreadsheet. I would create the proposal analysis and keep them in a Deal file. Then my boss or client could open the snapshot work sheet and link to the different proposals for entire deal comparison. Trying to set up a single snapshot file the can be used over multiple deals.

So the "Mt Prospect" Deal will eventually get done, but then a deal needs analyzed in "Tampa" Etc. etc.

Fro example this deal has not only the 5 & 10 year Mt Prospect, but several other iterations of other surrounding buildings, I thing 7 in total - that I would need to link the 5 other proposal files into the spreadsheet

[FDibbins]

Do you not see the

=CONCATENATE(" Floors ",'Mt Prospect 5 year.xlsx'!AddressSuite," "," Total Leased Space = ",TEXT('Mt Prospect 5 year.xlsx'!RSF,"##,##")," RSF")

Formula in F7?

No I didnt, because you did not refer to that in your question.

Did you real 2. in my #4?

2. To use a text entry (like F37) to reference another sheet or workbook, you would use INDIRECT(). However this only works on open workbooks, so both the 5 year and 10 year files would need to be open

Fro example this deal has not only the 5 & 10 year Mt Prospect, but several other iterations of other surrounding buildings, I thing 7 in total

You miss my point. They can ALL be in the same workbook (file), and even on the same sheet (tab) - and I dont mean the same file as your Snapshot File. This way, you would link to 1 file and 1 sheet, and avoid any messy references and links.

As I said, as soon as you need to use a cell reference to complete a file name or sheet name, you would either need to use INDIRECT() and have the other file/s open, or use the More-func addin. With only 1 (other) file being referenced, you can make a hard-coded reference

[cdotyii]

I hear what your saying, but I cannot simply add all the spreadsheets into one to make it simple to do the overall deal analysis. Each individual proposal is on a workbook with several sheets and very complicated formulas that I did not create. And these spread sheet are passed around to several people within the client & my team before they are ready to be analyzed as part of the overall deal. It is a standard analysis tool that has been used for yrs by the client for every deal around the country to approve the cost of a deal once the proposal is selected, then it goes to the client financial management.

So for this entire deal I have an analysis workbook 5 of these:

Mt Prospect 1 year
Mt Prospect 5 year
Mt Prospect 10 year
Relo example 10 year
Schaumburg 1 year

Formulas:

F7 - CONCATENATE(" Floors ",C:\Client File\Mt Prospect 5 year.xlsx'!AddressSuite," "," Total Leased Space = ",TEXT(C:\Client File\Mt Prospect 5 year.xlsx'!RSF,"##,##")," RSF")
F8 - Manual Entry
F9 - C:\Client file\Mt Prospect 5 year.xlsx'!LeaseCommence
F10 - CONCATENATE(C:\Client File\Mt Prospect 5 year.xlsx'!LeaseTerm," ","Months")
F11 - Manual
F12 - Manual
F13 - C:\Client file\[Mt Prospect 5 year.xlsx]Rent Schedule'!$J$17
F14 - Manual
F15 - Manual
F16 - CONCATENATE(TEXT(C:\Client File\[Mt Prospect 5 year.xlsx]Rent Schedule'!$I$34,"$#,##")," / ",TEXT(C:\Client File\[Mt Prospect 5 year.xlsx]Rent Schedule'!$J$34,"$#,##0.00"))
F17 - CONCATENATE(TEXT(-C:\Client File\Mt Prospect 5 year.xlsx'!TIAllowance,"$#,##")," / ",TEXT(C:\Client File\[Mt Prospect 5 year.xlsx]Budget Tracking'!$F$17,"$#,##0.00"))
F18 - CONCATENATE(TEXT(C:\Client File\Mt Prospect 5 year.xlsx'!BudgetTotal,"$#,##")," / ",TEXT(C:\Client File\[Mt Prospect 5 year.xlsx]Budget Tracking'!$F$49,"$#,##0.00"))
F26 - (C:\Client File\[Mt Prospect 5 year.xlsx]Executive Summary'!$E$63/C:\Client File\Mt Prospect 5 year.xlsx'!LeaseTerm)*12
F28 - F26/C:\Client File\Mt Prospect 5 year.xlsx'!RSF
F29 - F26/F8

Those are the formulas in the SNAPshot file that I need to create to analyze the deals in each of the 5 proposals.

So I would create the snapshot with 5 columns and use those formulas in each column and link them to each individual proposal spreadsheet from client (Changing F37 the "Mt Prospect 5 year" to REF the other 4 spreadsheets).

How would I set this up using INDIRECT() or other process?

In a perfect world, it would work on both my PC & my bosses PC (with same DIR setup - C:\Client File\Deal Name (ie. Chicago)\Mt Prospect 5 year)
OR
Could we put all the proposal files in a Company directory that we both have access to and link the snapshot spreadsheet to there? Where the snapshot file for each deal would be in a serve directory that we both have (\\M:\Client File\Deal File\Proposal Spreadsheet)

I could send you via email the actual spreadsheets to help with this Linking Issue and you could see exactly what I am talking about, if that would help. But, how would I securely send them? Don't want to post my email here

[cdotyii]

Sent you a PM

[FDibbins]

Will you always have all those files open? If not, then i really dont want to waste your time, INDIRECT() only works on open workbooks, not closed.

The other options would be to use VBA or the More-func addin, which I dont have

[cdotyii]

I downloaded the More-Func addon

But, that would only work on My PC. To make it work I would make sure that all the analysis files are open & that all the files are open when my boss tries to use it as well

Just need a way to make it work

[FDibbins]

That is correct - it works at the machine level, so your boss would need it as well