Use the INDIRECT() worksheet function in your formulas. If all you want them
to change is the sheet name, then try this as your formula:
=K52 + INDIRECT("'" & A1 & "'!K53")

I know that may be a little difficult to read, so I'll try to make the use
of double and single quote marks a little clearer: the ("'" is actually
left parenthesis, double-quote, single quote, double-quote mark. The "'!K53"
starts off with double-quote followed by single quote in front of the ! mark.

By putting the single quotes around the sheet name, if you ever use a sheet
name that has a split word name, like 10 24 08 instead of "10-24-08", it will
still work properly.

One thing, always make sure your sheet names don't have leading or trailing
spaces in them; "10-24-08 " is not the same as "10-24-08" and if that
situation arises, you'll get a #REF error in your formulas.



"3Nails" wrote:

I would like a user have the ability to modify a formula with a reference to
an open spreadsheet. I am working on today's spreadsheat named 10-24-08. It
uses the formula: =K52+'10-23-08'!K53 referencing the prior days spreadsheet
10-23-08. I would like the user to input into cell A1 a spreadsheet name (ie.
10-22-08 or whatever spreadsheet they wanted to use) and then use the
variable input into cell A1 as the reference in the formula:
=K52+cellA1variable!K53. The spreadsheet is protected. I tried a
macro....unprotect, find/replace, protect....which works great but doesn't
allow the user to change the find/replace values. Is there a way to do what I
want....Thanks.....