| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect a range that spans multiple sheets
I'm having a problem with indirect.
I'd like to get =SUM('Section A:Section B'!A1) As I step through my formula I get to here, and then get #Ref! =SUM(INDIRECT("'Section A:Section B'!A1")) Is there any way to do this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect a range that spans multiple sheets
Why are you trying to use INDIRECT?
-- Biff Microsoft Excel MVP "jhgravelle" wrote in message ... I'm having a problem with indirect. I'd like to get =SUM('Section A:Section B'!A1) As I step through my formula I get to here, and then get #Ref! =SUM(INDIRECT("'Section A:Section B'!A1")) Is there any way to do this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect a range that spans multiple sheets
because the full formula will use concatenate or &'s no construct 'Section
A:Section B'!A1. I know that part is working, either buy using the formula auditor, or editing and selecting portions of the formula and pressing F9. what's not working is the inderect portion. "T. Valko" wrote: Why are you trying to use INDIRECT? -- Biff Microsoft Excel MVP "jhgravelle" wrote in message ... I'm having a problem with indirect. I'd like to get =SUM('Section A:Section B'!A1) As I step through my formula I get to here, and then get #Ref! =SUM(INDIRECT("'Section A:Section B'!A1")) Is there any way to do this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect a range that spans multiple sheets
Well, if you are building up a string of "'Section A:Section B'!A1",
you may as well go the whole hog and make it: "SUM('Section A:Section B'!A1)" Let's say that this is in D5. Then you can make use of this user- defined function: Function Eval(Rng As Range) As Variant Application.Volatile True Eval = Evaluate(Rng.Text) End Function by means of this formula: =Eval(D5) in cell E5 (say). Hope this helps. Pete On Nov 12, 11:51 pm, jhgravelle wrote: because the full formula will use concatenate or &'s no construct 'Section A:Section B'!A1. I know that part is working, either buy using the formula auditor, or editing and selecting portions of the formula and pressing F9. what's not working is the inderect portion. "T. Valko" wrote: Why are you trying to use INDIRECT? -- Biff Microsoft Excel MVP "jhgravelle" wrote in message ... I'm having a problem with indirect. I'd like to get =SUM('Section A:Section B'!A1) As I step through my formula I get to here, and then get #Ref! =SUM(INDIRECT("'Section A:Section B'!A1")) Is there any way to do this?- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect a range that spans multiple sheets
Thanks for the other ways to do it. I wouldn't have come up with such short
vba code, but i could do it through various ways in VBA. I was looking for using simple excel formula like one would think they could be used. But it appears that INDIRECT cant always convert a string to a reference. "Pete_UK" wrote: Well, if you are building up a string of "'Section A:Section B'!A1", you may as well go the whole hog and make it: "SUM('Section A:Section B'!A1)" Let's say that this is in D5. Then you can make use of this user- defined function: Function Eval(Rng As Range) As Variant Application.Volatile True Eval = Evaluate(Rng.Text) End Function by means of this formula: =Eval(D5) in cell E5 (say). Hope this helps. Pete On Nov 12, 11:51 pm, jhgravelle wrote: because the full formula will use concatenate or &'s no construct 'Section A:Section B'!A1. I know that part is working, either buy using the formula auditor, or editing and selecting portions of the formula and pressing F9. what's not working is the inderect portion. "T. Valko" wrote: Why are you trying to use INDIRECT? -- Biff Microsoft Excel MVP "jhgravelle" wrote in message ... I'm having a problem with indirect. I'd like to get =SUM('Section A:Section B'!A1) As I step through my formula I get to here, and then get #Ref! =SUM(INDIRECT("'Section A:Section B'!A1")) Is there any way to do this?- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect a range that spans multiple sheets
I don't think INDIRECT works very well with 3-d references. The Eval
UDF is very useful for converting strings into formulae - certainly worth adding to your library of routines. Pete On Nov 13, 2:36 pm, jhgravelle wrote: Thanks for the other ways to do it. I wouldn't have come up with such short vba code, but i could do it through various ways in VBA. I was looking for using simple excel formula like one would think they could be used. But it appears that INDIRECT cant always convert a string to a reference. "Pete_UK" wrote: Well, if you are building up a string of "'Section A:Section B'!A1", you may as well go the whole hog and make it: "SUM('Section A:Section B'!A1)" Let's say that this is in D5. Then you can make use of this user- defined function: Function Eval(Rng As Range) As Variant Application.Volatile True Eval = Evaluate(Rng.Text) End Function by means of this formula: =Eval(D5) in cell E5 (say). Hope this helps. Pete On Nov 12, 11:51 pm, jhgravelle wrote: because the full formula will use concatenate or &'s no construct 'Section A:Section B'!A1. I know that part is working, either buy using the formula auditor, or editing and selecting portions of the formula and pressing F9. what's not working is the inderect portion. "T. Valko" wrote: Why are you trying to use INDIRECT? -- Biff Microsoft Excel MVP "jhgravelle" wrote in message ... I'm having a problem with indirect. I'd like to get =SUM('Section A:Section B'!A1) As I step through my formula I get to here, and then get #Ref! =SUM(INDIRECT("'Section A:Section B'!A1")) Is there any way to do this?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect a range that spans multiple sheets
INDIRECT is limited with regards of converting a text string spanning more
than one sheet, it needs all the sheet names not just the first and last, one possible way would be to use =SUM(N(INDIRECT("'Section "&{"A";"B"}&"'!A1"))) and if the Section is part of all sheet names you can put the other part of the names within those curly brackets -- Regards, Peo Sjoblom "jhgravelle" wrote in message ... Thanks for the other ways to do it. I wouldn't have come up with such short vba code, but i could do it through various ways in VBA. I was looking for using simple excel formula like one would think they could be used. But it appears that INDIRECT cant always convert a string to a reference. "Pete_UK" wrote: Well, if you are building up a string of "'Section A:Section B'!A1", you may as well go the whole hog and make it: "SUM('Section A:Section B'!A1)" Let's say that this is in D5. Then you can make use of this user- defined function: Function Eval(Rng As Range) As Variant Application.Volatile True Eval = Evaluate(Rng.Text) End Function by means of this formula: =Eval(D5) in cell E5 (say). Hope this helps. Pete On Nov 12, 11:51 pm, jhgravelle wrote: because the full formula will use concatenate or &'s no construct 'Section A:Section B'!A1. I know that part is working, either buy using the formula auditor, or editing and selecting portions of the formula and pressing F9. what's not working is the inderect portion. "T. Valko" wrote: Why are you trying to use INDIRECT? -- Biff Microsoft Excel MVP "jhgravelle" wrote in message ... I'm having a problem with indirect. I'd like to get =SUM('Section A:Section B'!A1) As I step through my formula I get to here, and then get #Ref! =SUM(INDIRECT("'Section A:Section B'!A1")) Is there any way to do this?- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Indirect - Multiple Sheets/Cells | Excel Worksheet Functions | |||
| Individual record spans multiple rows | Excel Worksheet Functions | |||
| INDIRECT for range of sheets | Excel Worksheet Functions | |||
| INDIRECT and multiple sheets | Excel Worksheet Functions | |||
| Sum Indirect function through multiple sheets | Excel Discussion (Misc queries) | |||
Linear Mode
