Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring to cells on other sheets using formula
I am trying to automate the way I refer to cells on other worksheets, by
working out the name of the worksheet using a formula and then referring to this formula. For example: the sheet I want to refer has a name of the form: "Year x months ahead". Year, and x can vary in the worksheet doing the referring, so for example: in one sheet I need to refer to worksheet "2006 6 months ahead", and in that sheet "2006" and "6" are variables which I can use to get the sheet name to be referred to. I can set up the sheet name I require using the & function, but then excel doesn't like it I try and refer to the other sheet using this equation. I hope that makes sense - its quite difficult to explain my problem! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring to cells on other sheets using formula
With the below values in your active sheet
A1 = 2006 A2 = 6 the below formula will refer to sheet '2006 6 months ahead' cell A1 =INDIRECT("'" & A1& " " & A2 & " months ahead'!A1") If this post helps click Yes --------------- Jacob Skaria "ClaireS" wrote: I am trying to automate the way I refer to cells on other worksheets, by working out the name of the worksheet using a formula and then referring to this formula. For example: the sheet I want to refer has a name of the form: "Year x months ahead". Year, and x can vary in the worksheet doing the referring, so for example: in one sheet I need to refer to worksheet "2006 6 months ahead", and in that sheet "2006" and "6" are variables which I can use to get the sheet name to be referred to. I can set up the sheet name I require using the & function, but then excel doesn't like it I try and refer to the other sheet using this equation. I hope that makes sense - its quite difficult to explain my problem! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring to cells on other sheets using formula
Jacob
This partially works, but I now want to use this cell reference as the first argument in the OFFSET function. Excel doesn't like this - it picks this up as text rather than a cell ref in another sheet. Any ideas? "Jacob Skaria" wrote: With the below values in your active sheet A1 = 2006 A2 = 6 the below formula will refer to sheet '2006 6 months ahead' cell A1 =INDIRECT("'" & A1& " " & A2 & " months ahead'!A1") If this post helps click Yes --------------- Jacob Skaria "ClaireS" wrote: I am trying to automate the way I refer to cells on other worksheets, by working out the name of the worksheet using a formula and then referring to this formula. For example: the sheet I want to refer has a name of the form: "Year x months ahead". Year, and x can vary in the worksheet doing the referring, so for example: in one sheet I need to refer to worksheet "2006 6 months ahead", and in that sheet "2006" and "6" are variables which I can use to get the sheet name to be referred to. I can set up the sheet name I require using the & function, but then excel doesn't like it I try and refer to the other sheet using this equation. I hope that makes sense - its quite difficult to explain my problem! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring to cells on other sheets using formula
=OFFSET(INDIRECT("'" & A1& " " & A2 & " months ahead'!A1"),1,1)
refers to sheet '2006 6 months ahead' cell B2 If this post helps click Yes --------------- Jacob Skaria "ClaireS" wrote: Jacob This partially works, but I now want to use this cell reference as the first argument in the OFFSET function. Excel doesn't like this - it picks this up as text rather than a cell ref in another sheet. Any ideas? "Jacob Skaria" wrote: With the below values in your active sheet A1 = 2006 A2 = 6 the below formula will refer to sheet '2006 6 months ahead' cell A1 =INDIRECT("'" & A1& " " & A2 & " months ahead'!A1") If this post helps click Yes --------------- Jacob Skaria "ClaireS" wrote: I am trying to automate the way I refer to cells on other worksheets, by working out the name of the worksheet using a formula and then referring to this formula. For example: the sheet I want to refer has a name of the form: "Year x months ahead". Year, and x can vary in the worksheet doing the referring, so for example: in one sheet I need to refer to worksheet "2006 6 months ahead", and in that sheet "2006" and "6" are variables which I can use to get the sheet name to be referred to. I can set up the sheet name I require using the & function, but then excel doesn't like it I try and refer to the other sheet using this equation. I hope that makes sense - its quite difficult to explain my problem! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Formula referring to 2 cells | Excel Discussion (Misc queries) | |||
Referring to Cells | Excel Discussion (Misc queries) | |||
Referring to sheets in formulas | Excel Discussion (Misc queries) | |||
Sort a list with cells referring to others cells | Excel Discussion (Misc queries) | |||
Pivot shart referring to multiple sheets | Charts and Charting in Excel |