Using a variable spreadsheet reference in a formula

[3Nails]

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.....

 

[JLatham]

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.

 

[OssieMac]

Don't know whether this will help. Lookup INDIRECT function.

The following uses the indirect function and the sheet name is concatenated
with the cell reference.

=K52+INDIRECT(A1&"!K53")

However, I cannot make it work with a date as the sheet name. Even
formatting the cell as text so the date is entered as text or using the TEXT
function to convert it to text. It does not like the Hyphen or Spaces in the
date with the indirect function. The numbers are OK if entered as 102308 but
not
10-23-08 or 10 23 08

Also not able to use My Sheet with the space. MySheet works.

 

[OssieMac]

Stupid me. Of course the single quotes are required as JLatham's post.

 

[JLatham]

You know, actually I got kind of lucky recommending the single quote marks.
I didn't test with a sheet named 10-24-08, just with Sheet2, which works with
or without them. But the lack of single quotes is something I see so often
in posts using a sheet name and my knowing that the same thing is going to be
a problem if the sheet name gets changed to something with a space in it (or
in this case an unusual sheet name), that I try to remember to use or
recommend them.
'Sheet2'!K53 and Sheet2!K53 will always both work, while
'Sheet 2'!K53 will work and Sheet 2!K53 won't.

 

[3Nails]

Works GREAT!!!!! Thank you both........

You know, actually I got kind of lucky recommending the single quote marks.
I didn't test with a sheet named 10-24-08, just with Sheet2, which works with
or without them. But the lack of single quotes is something I see so often
in posts using a sheet name and my knowing that the same thing is going to be
a problem if the sheet name gets changed to something with a space in it (or
in this case an unusual sheet name), that I try to remember to use or
recommend them.
'Sheet2'!K53 and Sheet2!K53 will always both work, while
'Sheet 2'!K53 will work and Sheet 2!K53 won't.

 

[JLatham]

Thanks for the feedback. Happy that we could help you out.