| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Build a reference to another worksheet from cell values in currentsheet
I have a summary sheet and separate week number sheets. In the summary
sheet I have a link to the week number sheets returning data from A1 see below. A B 1 Week Data (cell A1 from Week ??.xls) 2 01 ='[Week 01.xls]Sheet1'!$A$1 3 02 ='[Week 02.xls]Sheet1'!$A$1 4 03 ='[Week 03.xls]Sheet1'!$A$1 Is it possible to build the formula which includes a cell value held in column A for example rather than specify ='[Week 01.xls]Sheet1'!$A $1 in cell B2, I'd like to build the formula something like this... ='["Week" & A2 & ".xls"]Sheet1'!$A$1. Hope this makes sense? Is this possible? Many thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Build a reference to another worksheet from cell values in currentsheet
Lucas Reece wrote:
I have a summary sheet and separate week number sheets. In the summary sheet I have a link to the week number sheets returning data from A1 see below. A B 1 Week Data (cell A1 from Week ??.xls) 2 01 ='[Week 01.xls]Sheet1'!$A$1 3 02 ='[Week 02.xls]Sheet1'!$A$1 4 03 ='[Week 03.xls]Sheet1'!$A$1 Is it possible to build the formula which includes a cell value held in column A for example rather than specify ='[Week 01.xls]Sheet1'!$A $1 in cell B2, I'd like to build the formula something like this... ='["Week" & A2 & ".xls"]Sheet1'!$A$1. Hope this makes sense? Is this possible? Many thanks. Look at the INDIRECT() function. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Build a reference to another worksheet from cell values in current
Try
=INDIRECT("'[week " & A2 &".xls]Sheet1'!$A$1") Mike "Lucas Reece" wrote: I have a summary sheet and separate week number sheets. In the summary sheet I have a link to the week number sheets returning data from A1 see below. A B 1 Week Data (cell A1 from Week ??.xls) 2 01 ='[Week 01.xls]Sheet1'!$A$1 3 02 ='[Week 02.xls]Sheet1'!$A$1 4 03 ='[Week 03.xls]Sheet1'!$A$1 Is it possible to build the formula which includes a cell value held in column A for example rather than specify ='[Week 01.xls]Sheet1'!$A $1 in cell B2, I'd like to build the formula something like this... ='["Week" & A2 & ".xls"]Sheet1'!$A$1. Hope this makes sense? Is this possible? Many thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Build a reference to another worksheet from cell values incurrent
On 17 Feb, 21:11, Mike H wrote:
Try =INDIRECT("'[week " & A2 &".xls]Sheet1'!$A$1") Mike "Lucas Reece" wrote: I have a summary sheet and separate week number sheets. In the summary sheet I have a link to the week number sheets returning data from A1 see below. * * * A * * * * * *B 1 * *Week * * *Data (cell A1 from Week ??.xls) 2 * *01 * * * * * ='[Week 01.xls]Sheet1'!$A$1 3 * *02 * * * * * ='[Week 02.xls]Sheet1'!$A$1 4 * *03 * * * * * ='[Week 03.xls]Sheet1'!$A$1 Is it possible to build the formula which includes a cell value held in column A for example rather than specify ='[Week 01.xls]Sheet1'!$A $1 in cell B2, I'd like to build the formula something like this... ='["Week" & A2 & ".xls"]Sheet1'!$A$1. Hope this makes sense? Is this possible? Many thanks. Thanks guys. This works a treat! However, when the week number sheet is closed the summary sheet value returns a #REF!. Any way around this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Build a reference to another worksheet from cell values in cur
No, and yes.
No as in: Not with the out-of-the-box indirect function. Yes as in, you can change how the data is stored (consolidating it into one workbook for example) and using a non-volatile function OR Have a look at: http://xcell05.free.fr/morefunc/english/ which includes (among other things) a function called INDIRECT.EXE that works more or less like indirect, but on workbooks that seem closed. "Lucas Reece" wrote: On 17 Feb, 21:11, Mike H wrote: Try =INDIRECT("'[week " & A2 &".xls]Sheet1'!$A$1") Mike "Lucas Reece" wrote: I have a summary sheet and separate week number sheets. In the summary sheet I have a link to the week number sheets returning data from A1 see below. A B 1 Week Data (cell A1 from Week ??.xls) 2 01 ='[Week 01.xls]Sheet1'!$A$1 3 02 ='[Week 02.xls]Sheet1'!$A$1 4 03 ='[Week 03.xls]Sheet1'!$A$1 Is it possible to build the formula which includes a cell value held in column A for example rather than specify ='[Week 01.xls]Sheet1'!$A $1 in cell B2, I'd like to build the formula something like this... ='["Week" & A2 & ".xls"]Sheet1'!$A$1. Hope this makes sense? Is this possible? Many thanks. Thanks guys. This works a treat! However, when the week number sheet is closed the summary sheet value returns a #REF!. Any way around this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Build a reference to another worksheet from cell values in cur
On 17 Feb, 22:16, ~L wrote:
No, and yes. No as in: *Not with the out-of-the-box indirect function. Yes as in, you can change how the data is stored (consolidating it into one workbook for example) and using a non-volatile function OR Have a look at:http://xcell05.free.fr/morefunc/english/ which includes (among other things) a function called INDIRECT.EXE that works more or less like indirect, but on workbooks that seem closed. "Lucas Reece" wrote: On 17 Feb, 21:11, Mike H wrote: Try =INDIRECT("'[week " & A2 &".xls]Sheet1'!$A$1") Mike "Lucas Reece" wrote: I have a summary sheet and separate week number sheets. In the summary sheet I have a link to the week number sheets returning data from A1 see below. * * * A * * * * * *B 1 * *Week * * *Data (cell A1 from Week ??.xls) 2 * *01 * * * * * ='[Week 01.xls]Sheet1'!$A$1 3 * *02 * * * * * ='[Week 02.xls]Sheet1'!$A$1 4 * *03 * * * * * ='[Week 03.xls]Sheet1'!$A$1 Is it possible to build the formula which includes a cell value held in column A for example rather than specify ='[Week 01.xls]Sheet1'!$A $1 in cell B2, I'd like to build the formula something like this... ='["Week" & A2 & ".xls"]Sheet1'!$A$1. Hope this makes sense? Is this possible? Many thanks. Thanks guys. This works a treat! However, when the week number sheet is closed *the summary sheet value returns a #REF!. Any way around this? Excellent thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to build reference to external, web-based sheets | Excel Worksheet Functions | |||
| INDIRECT function to reference values in another worksheet | Excel Worksheet Functions | |||
| Using cell value to build external reference | Excel Discussion (Misc queries) | |||
| How do I build a reference to an external worksheet? | Excel Worksheet Functions | |||
| How do I conditionally build worksheet from existing worksheet? | Excel Discussion (Misc queries) | |||
Linear Mode
