Shortening external references

[mjsiemer]

Hello-

I have some very lengthy formulas in a reporting spreadsheet, and I want to point them towards data in another spreadsheet. The problem is that the formula is so long I get an error from Excel saying "the text string you entered is too long".

Part of the problem is that the file name for the other spreadsheet is long, ie "C:\Datasheets\Expenses\Project 41235\41235data.xls"

I've been trying to figure out some way to store this filename either as a variable or in another cell in the reporting spreadsheet, so I can just use the variable/cell in the formula. Obviously that would shorten the text in the formula considerably.

Any idea how I can do this? Because the way I've done it so far, Excel doesn't like. If I put the above path in cell A1 of the reporting spreadsheet, and write a formula like this for B2:

=A1sheet2!C25

Excel pops up the Open Window window and says it can't find a file named A2sheet2.

thanks for your help,
Matt

 

[Dave Patton]

With both files open, move to the cell where you want the reference,
enter = and then move to the cell in the other sheet and press Enter

Close the other sheet and see how Excel shows the reference.

"Part of the problem is that the file name for the other spreadsheet is long, ie "C:\Datasheets\Expenses\Project 41235\41235data.xls"


Does your folder named "Project 41235" have a space in it?
Is this a valid name?

I referenced a cell on my system and it showed

='E:\Data03\Tech03\[Dates2.xls]Calendar'!$A$3


You might want to check HELP on Indirect (both files must be OPEN)

and/or Indirect.ext which is part of Morefunc Add-in. Can access information from a closed workbook. See Add-in information at top of list of messages.

 

[Barry Katcher]

Your formula is incorrect. As Dave says, with both worksheets open, put an equal sign in the cell you want the formula in, click on the source workbook, click on the cell you want to be the source, and then press Enter. Your formula will look like one of the two below:

With source workbook open:
='[New Book.xls]Sheet4'!$A$1

With source workbook closed:
='C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART\[New Book.xls]Sheet4'!$A$1