Formula too long

[racer25]

Hi Guys,

I have the formula

=OFFSET('G:\Budgets & Estimates\Budget 2008\S08 Forms Consulting and Retail\Retail\[S08_form_LM.xls]Operating RevenueYTD'!$D9,0,Start!$B$55)

Which only works if the linked file is open.

The formula when the file is open is represeneted as.

=OFFSET('[S08_form_ML.xls]Operating RevenueYTD'!$D9,0,Start!$B$55)

I as using 2007 and my inkling is the length of the formula as it comes up as a compatabilty error message when I try and save it as a 2003 file for my co-workers.

Is there anyway of truncating the formula without renaming folders or files. Maybe some sort of concatenate formula that joined say the file folder and file together.

Would appreciate anything that anyone can suggest.

Cheers,

R

[broro183]

hi,

I'm using Excel 2003 & I have just looked up the Help files for Offset & although it doesn't mention this, I'm sure I've read (somewhere?) that Offset is only designed to update if the linked file is open.

Try selecting "save external links" under [alt + t + o] - Calculation. Does this help?

Your string is only 140 ish characters long & I'm sure I've used longer ones myself, do the others have the same drive mapping (eg "G:")?


To concatenate a file name string with in a formula use the Indirect function (I think this also needs the source file open for updating?).


To quickly open a linked file press [alt + e + k], select the link, press Open Source. Or you could even try saving all the files as a "workspace"...
To find out more about of each of these suggestions read up the Excel Help Files ([F1] can be a knowledgeable friend!)

hth
Rob

[racer25]

It did help thanks,

The INDEX function was the right answer it does not require the file to be open.

[broro183]

Pleased I could help - thanks for the feedback :-)

Rob