Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing sheet reference to cell reference
On one sheet I have the formula ='TeeJay'!$L$40 where TeeJay is the name of a
different sheet to the one I'm working on but in the same workbook. Is it possible to change the formula so that it refers to a cell on the current sheet that has a reference to the other sheet? If I had ='TeeJay'!A1 (where A1 has the name of the sheet) in cell K1 on the current sheet, would it be possible to get a formula that works =K1!$L$40 If I try this at the minute, I get a dialog box to update values. How should I be writing this formula? I'm using Excel 2000 and would prefer not to use VBA (some of the computers this will have to be viewed on have macros disabled). Many thanks for any help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing sheet reference to cell reference
Use the INDIRECT() function
-- Gary''s Student - gsnu200750 "TeeJay" wrote: On one sheet I have the formula ='TeeJay'!$L$40 where TeeJay is the name of a different sheet to the one I'm working on but in the same workbook. Is it possible to change the formula so that it refers to a cell on the current sheet that has a reference to the other sheet? If I had ='TeeJay'!A1 (where A1 has the name of the sheet) in cell K1 on the current sheet, would it be possible to get a formula that works =K1!$L$40 If I try this at the minute, I get a dialog box to update values. How should I be writing this formula? I'm using Excel 2000 and would prefer not to use VBA (some of the computers this will have to be viewed on have macros disabled). Many thanks for any help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing sheet reference to cell reference
check out the indirect() function in help.
If I am reading your input right =indirect(K1&"!$L$40") but put only TeeJay in K1 depending on your sheet names you may have to add the ' to the front and back on the sheet name "TeeJay" wrote: On one sheet I have the formula ='TeeJay'!$L$40 where TeeJay is the name of a different sheet to the one I'm working on but in the same workbook. Is it possible to change the formula so that it refers to a cell on the current sheet that has a reference to the other sheet? If I had ='TeeJay'!A1 (where A1 has the name of the sheet) in cell K1 on the current sheet, would it be possible to get a formula that works =K1!$L$40 If I try this at the minute, I get a dialog box to update values. How should I be writing this formula? I'm using Excel 2000 and would prefer not to use VBA (some of the computers this will have to be viewed on have macros disabled). Many thanks for any help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing sheet reference to cell reference
Hi bj,
thanks for the response. However, this keeps giving me a =REF! error when I enter it. Stepping through the function helps shows that the interim step right before the =REF! value has the equation =INDIRECT( "TeeJay!$L$40") (the italised writing is making it hard to determine if those are quotation marks or two ' marks together) The ' marks to reference the TeeJay sheet seem to disappear on the step before. I have looked at Excel help and to be honest I'm not finding it helpful for INDIRECT formulas. One thing I realise now I forgot to mention was that cell A1 on the TeeJay worksheet (the one that cell K1 on the current sheet is referencing) has this formula in: =MID(CELL("filename",A1),(FIND("]",CELL("filename",A1)))+1,50) I don't think this is affecting the INDIRECT formula but I haven't used the MID command before picking it up elsewhere in the discussion group I can tell that I'm not the only person to have had problems with INDIRECT formula but I haven't found yet a reply in these groups that seems to address this particular issue. Thanks in advance to any help "bj" wrote: check out the indirect() function in help. If I am reading your input right =indirect(K1&"!$L$40") but put only TeeJay in K1 depending on your sheet names you may have to add the ' to the front and back on the sheet name "TeeJay" wrote: On one sheet I have the formula ='TeeJay'!$L$40 where TeeJay is the name of a different sheet to the one I'm working on but in the same workbook. Is it possible to change the formula so that it refers to a cell on the current sheet that has a reference to the other sheet? If I had ='TeeJay'!A1 (where A1 has the name of the sheet) in cell K1 on the current sheet, would it be possible to get a formula that works =K1!$L$40 If I try this at the minute, I get a dialog box to update values. How should I be writing this formula? I'm using Excel 2000 and would prefer not to use VBA (some of the computers this will have to be viewed on have macros disabled). Many thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
stop insert from changing cell reference | Excel Worksheet Functions | |||
Problem with formulas changing cell reference | Excel Discussion (Misc queries) | |||
A cell reference in a formula changing | Excel Worksheet Functions | |||
HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE | Excel Discussion (Misc queries) |