| Home |
| Search |
| Today's Posts |
|
#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..... |
| 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 | |||
Threaded Mode
