Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a variable spreadsheet reference in a formula
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..... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a variable spreadsheet reference in a formula
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..... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a variable spreadsheet reference in a formula
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. -- Regards, OssieMac "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..... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a variable spreadsheet reference in a formula
Stupid me. Of course the single quotes are required as JLatham's post.
-- Regards, OssieMac "OssieMac" wrote: 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. -- Regards, OssieMac "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..... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a variable spreadsheet reference in a formula
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. "OssieMac" wrote: Stupid me. Of course the single quotes are required as JLatham's post. -- Regards, OssieMac "OssieMac" wrote: 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. -- Regards, OssieMac "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..... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a variable spreadsheet reference in a formula
Works GREAT!!!!! Thank you both........
"JLatham" wrote: 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. "OssieMac" wrote: Stupid me. Of course the single quotes are required as JLatham's post. -- Regards, OssieMac "OssieMac" wrote: 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. -- Regards, OssieMac "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..... |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a variable spreadsheet reference in a formula
Thanks for the feedback. Happy that we could help you out.
"3Nails" wrote: Works GREAT!!!!! Thank you both........ "JLatham" wrote: 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. "OssieMac" wrote: Stupid me. Of course the single quotes are required as JLatham's post. -- Regards, OssieMac "OssieMac" wrote: 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. -- Regards, OssieMac "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..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet reference in formula? | Excel Worksheet Functions | |||
Variable column reference in formula | Excel Discussion (Misc queries) | |||
XL how to change a cell reference in a formula to variable value? | Excel Worksheet Functions | |||
How to use variable in reference | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |