| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect and Dynamic Range
I have a named dynamic range Class_6 which holds the dynamic range
=OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Hi!
Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message ... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Just use =Class_6 in the DVD
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Graham Haughs" wrote in message ... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Just to clarify.......
As Bob noted, you can just use =Class_6 as the source if that's the only range name you have. But, I have a feeling that you do have more (and didn't tell us!) which is why I suggested the Choose formula. If you do have more names than the Choose formula can be expanded to handle the other names (up to 29). Biff "Biff" wrote in message ... Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message ... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Thanks for feedback but I really need the indirect function. I know I
can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message ... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Sorry I have confused it enough without complicating it even more. In
the last post i made an error as I meant that B11 was changing with multiple lists , not E11 as I typed. The data validation list was pointing to B11, ie =Indirect(B11) and a large number of named ranges will be created and will appear as options in cell B11. Graham Graham Haughs wrote: Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message ... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Exactly how many names do you have? Choose will work with up to 29 names.
I really need the indirect function Forget about Indirect. It won't work! Indirect needs a TEXT representation of a reference. When you use =INDIRECT(B11) the name in B11 evaluates to the FORMULA: =OFFSET(.....................). This is not a TEXT representation of a reference! Try this: List the names in a range of cells, say, AA1:AAn AA1 = Class_1 AA2 = Class_2 AA3 = Class_3 etc As the source for the drop down: =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_2,Class _3) Biff "Graham Haughs" wrote in message ... Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message ... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Thanks for your patience Biff, the problem is that I need 120 named
ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. Graham Biff wrote: Exactly how many names do you have? Choose will work with up to 29 names. I really need the indirect function Forget about Indirect. It won't work! Indirect needs a TEXT representation of a reference. When you use =INDIRECT(B11) the name in B11 evaluates to the FORMULA: =OFFSET(.....................). This is not a TEXT representation of a reference! Try this: List the names in a range of cells, say, AA1:AAn AA1 = Class_1 AA2 = Class_2 AA3 = Class_3 etc As the source for the drop down: =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_2,Class _3) Biff "Graham Haughs" wrote in message ... Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message ... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
I need 120 named ranges
I've never had to deal with that many in this context! Let me see if I can come up with something. No guarantees! Biff "Graham Haughs" wrote in message ... Thanks for your patience Biff, the problem is that I need 120 named ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. Graham Biff wrote: Exactly how many names do you have? Choose will work with up to 29 names. I really need the indirect function Forget about Indirect. It won't work! Indirect needs a TEXT representation of a reference. When you use =INDIRECT(B11) the name in B11 evaluates to the FORMULA: =OFFSET(.....................). This is not a TEXT representation of a reference! Try this: List the names in a range of cells, say, AA1:AAn AA1 = Class_1 AA2 = Class_2 AA3 = Class_3 etc As the source for the drop down: =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_2,Class _3) Biff "Graham Haughs" wrote in message ... Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message .. . I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Can you provide more details?
Where EXACTLY are these named ranges located? Are they in a contiguous 120 column block? Are the names Class_1 to Class_120? Biff "Biff" wrote in message ... I need 120 named ranges I've never had to deal with that many in this context! Let me see if I can come up with something. No guarantees! Biff "Graham Haughs" wrote in message ... Thanks for your patience Biff, the problem is that I need 120 named ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. Graham Biff wrote: Exactly how many names do you have? Choose will work with up to 29 names. I really need the indirect function Forget about Indirect. It won't work! Indirect needs a TEXT representation of a reference. When you use =INDIRECT(B11) the name in B11 evaluates to the FORMULA: =OFFSET(.....................). This is not a TEXT representation of a reference! Try this: List the names in a range of cells, say, AA1:AAn AA1 = Class_1 AA2 = Class_2 AA3 = Class_3 etc As the source for the drop down: =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_2,Class _3) Biff "Graham Haughs" wrote in message ... Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message . .. I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1 ) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
The ranges are in one worksheet in the main workbook. They are in a
contiguous 120 column block but the number of rows with entries is variable although there are no blank entries, ie one column may have 4 entries starting at the second row, another may have 15 but there are no blanks in these entries, and all entries are numerical, non decimal. The names are Class_1 to Class_120, the top row of each column carrying this name. Thanks for the help Biff but don't spend too much time on it. Patience is not a virtue, it is a minor state of despair! Graham Graham Biff wrote: Can you provide more details? Where EXACTLY are these named ranges located? Are they in a contiguous 120 column block? Are the names Class_1 to Class_120? Biff "Biff" wrote in message ... I need 120 named ranges I've never had to deal with that many in this context! Let me see if I can come up with something. No guarantees! Biff "Graham Haughs" wrote in message ... Thanks for your patience Biff, the problem is that I need 120 named ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. Graham Biff wrote: Exactly how many names do you have? Choose will work with up to 29 names. I really need the indirect function Forget about Indirect. It won't work! Indirect needs a TEXT representation of a reference. When you use =INDIRECT(B11) the name in B11 evaluates to the FORMULA: =OFFSET(.....................). This is not a TEXT representation of a reference! Try this: List the names in a range of cells, say, AA1:AAn AA1 = Class_1 AA2 = Class_2 AA3 = Class_3 etc As the source for the drop down: =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_2,C lass_3) Biff "Graham Haughs" wrote in message .. . Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message .. . I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F), 1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Graham Haughs wrote...
Thanks for your patience Biff, the problem is that I need 120 named ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. .... "Graham Haughs" wrote in message I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. So Class_6 holds the result of an OFFSET call, and it happens to be based on column F in the Data worksheet. Column F is the 6th column of that worksheet. Is there a correspondence between columns in the worksheet and the number in the Class_# defined names? If so, you could make the validation range =INDEX(Data!$2:$2,B11):INDEX(Data!$A:$DP,COUNTA(IN DEX(Data!$A:$DP,0,B11)),B11) Otherwise, you'd need a table of these defined names and their current addresses, use the B11 value as a lookup value into that table to pull the range address, then use that as the argument to INDIRECT. In that case, your data validation list reference would be something like =INDIRECT(VLOOKUP(B11,RefTbl,2,0)) The first column of RefTbl would be your Class_# defined names, and the second column the corresponding addresses, which could be produced by a multistep process like 1. Enter text formula in topmost cell of 2nd column of RefTbl, ="=SUBSTITUTE(CELL(""Address"",(Data!A1,"&X101&")) ,""$A$1,"","""",1)" where X101 would be the topmost cell in the 1st column of RefTbl. Fill down so there's one of these formulas for each defined name. 2. Copy the column of formulas and paste special as values on top of itself. 3. With the range of former formulas still selected, run Edit Replace and replace = with =. That might seem to be no change, but it has the effect of taking text constants that look like formulas and entering them as formulas. The result should be the range addresses of the defined names. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
don't spend too much time on it.
Eh, most of this stuff is fairly easy! This took me about 10 mins to figure out AND test. I enjoy problem solving! Ok........ Assume the 120 ranges are on Sheet2, columns A to DT. Row 1 is the column headers. Sheet1 B11 is where you enter the desired range name. Create this named formula: Name: GetRange Refers to: =OFFSET(Sheet2!$A$1,1,MATCH(Sheet1!$B$11,Sheet2!$A $1:$DT$1,0)-1,COUNTA(INDEX(Sheet2!$A:$DT,,MATCH(Sheet1!$B$11,S heet2!$A$1:$DT$1,0)))-1) As the source for the drop down: =GetRange Biff "Graham Haughs" wrote in message ... The ranges are in one worksheet in the main workbook. They are in a contiguous 120 column block but the number of rows with entries is variable although there are no blank entries, ie one column may have 4 entries starting at the second row, another may have 15 but there are no blanks in these entries, and all entries are numerical, non decimal. The names are Class_1 to Class_120, the top row of each column carrying this name. Thanks for the help Biff but don't spend too much time on it. Patience is not a virtue, it is a minor state of despair! Graham Graham Biff wrote: Can you provide more details? Where EXACTLY are these named ranges located? Are they in a contiguous 120 column block? Are the names Class_1 to Class_120? Biff "Biff" wrote in message ... I need 120 named ranges I've never had to deal with that many in this context! Let me see if I can come up with something. No guarantees! Biff "Graham Haughs" wrote in message ... Thanks for your patience Biff, the problem is that I need 120 named ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. Graham Biff wrote: Exactly how many names do you have? Choose will work with up to 29 names. I really need the indirect function Forget about Indirect. It won't work! Indirect needs a TEXT representation of a reference. When you use =INDIRECT(B11) the name in B11 evaluates to the FORMULA: =OFFSET(.....................). This is not a TEXT representation of a reference! Try this: List the names in a range of cells, say, AA1:AAn AA1 = Class_1 AA2 = Class_2 AA3 = Class_3 etc As the source for the drop down: =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_2, Class_3) Biff "Graham Haughs" wrote in message . .. Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message . .. I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1 ) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Ooops!
The formula references a range of 124 columns (column DT), not 120 (column DP)! Here is the formula with the correct column range: =OFFSET(Sheet2!$A$1,1,MATCH(Sheet1!$B$11,Sheet2!$A $1:$DP$1,0)-1,COUNTA(INDEX(Sheet2!$A:$DP,,MATCH(Sheet1!$B$11,S heet2!$A$1:$DP$1,0)))-1) Biff "Biff" wrote in message ... don't spend too much time on it. Eh, most of this stuff is fairly easy! This took me about 10 mins to figure out AND test. I enjoy problem solving! Ok........ Assume the 120 ranges are on Sheet2, columns A to DT. Row 1 is the column headers. Sheet1 B11 is where you enter the desired range name. Create this named formula: Name: GetRange Refers to: =OFFSET(Sheet2!$A$1,1,MATCH(Sheet1!$B$11,Sheet2!$A $1:$DT$1,0)-1,COUNTA(INDEX(Sheet2!$A:$DT,,MATCH(Sheet1!$B$11,S heet2!$A$1:$DT$1,0)))-1) As the source for the drop down: =GetRange Biff "Graham Haughs" wrote in message ... The ranges are in one worksheet in the main workbook. They are in a contiguous 120 column block but the number of rows with entries is variable although there are no blank entries, ie one column may have 4 entries starting at the second row, another may have 15 but there are no blanks in these entries, and all entries are numerical, non decimal. The names are Class_1 to Class_120, the top row of each column carrying this name. Thanks for the help Biff but don't spend too much time on it. Patience is not a virtue, it is a minor state of despair! Graham Graham Biff wrote: Can you provide more details? Where EXACTLY are these named ranges located? Are they in a contiguous 120 column block? Are the names Class_1 to Class_120? Biff "Biff" wrote in message ... I need 120 named ranges I've never had to deal with that many in this context! Let me see if I can come up with something. No guarantees! Biff "Graham Haughs" wrote in message .. . Thanks for your patience Biff, the problem is that I need 120 named ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. Graham Biff wrote: Exactly how many names do you have? Choose will work with up to 29 names. I really need the indirect function Forget about Indirect. It won't work! Indirect needs a TEXT representation of a reference. When you use =INDIRECT(B11) the name in B11 evaluates to the FORMULA: =OFFSET(.....................). This is not a TEXT representation of a reference! Try this: List the names in a range of cells, say, AA1:AAn AA1 = Class_1 AA2 = Class_2 AA3 = Class_3 etc As the source for the drop down: =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_2 ,Class_3) Biff "Graham Haughs" wrote in message ... Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message ... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F), 1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Sorry for delay in reply but was around midnight last time I posted.
This works and works well. I really appreciate the time and effort you put into this. It may be easy stuff to you but there is no way I would ever have arrived at what you produced which makes the whole thing a lot simpler and slicker. Many thanks. Graham Biff wrote: Ooops! The formula references a range of 124 columns (column DT), not 120 (column DP)! Here is the formula with the correct column range: =OFFSET(Sheet2!$A$1,1,MATCH(Sheet1!$B$11,Sheet2!$A $1:$DP$1,0)-1,COUNTA(INDEX(Sheet2!$A:$DP,,MATCH(Sheet1!$B$11,S heet2!$A$1:$DP$1,0)))-1) Biff "Biff" wrote in message ... don't spend too much time on it. Eh, most of this stuff is fairly easy! This took me about 10 mins to figure out AND test. I enjoy problem solving! Ok........ Assume the 120 ranges are on Sheet2, columns A to DT. Row 1 is the column headers. Sheet1 B11 is where you enter the desired range name. Create this named formula: Name: GetRange Refers to: =OFFSET(Sheet2!$A$1,1,MATCH(Sheet1!$B$11,Sheet2! $A$1:$DT$1,0)-1,COUNTA(INDEX(Sheet2!$A:$DT,,MATCH(Sheet1!$B$11,S heet2!$A$1:$DT$1,0)))-1) As the source for the drop down: =GetRange Biff "Graham Haughs" wrote in message .. . The ranges are in one worksheet in the main workbook. They are in a contiguous 120 column block but the number of rows with entries is variable although there are no blank entries, ie one column may have 4 entries starting at the second row, another may have 15 but there are no blanks in these entries, and all entries are numerical, non decimal. The names are Class_1 to Class_120, the top row of each column carrying this name. Thanks for the help Biff but don't spend too much time on it. Patience is not a virtue, it is a minor state of despair! Graham Graham Biff wrote: Can you provide more details? Where EXACTLY are these named ranges located? Are they in a contiguous 120 column block? Are the names Class_1 to Class_120? Biff "Biff" wrote in message .. . I need 120 named ranges I've never had to deal with that many in this context! Let me see if I can come up with something. No guarantees! Biff "Graham Haughs" wrote in message . .. Thanks for your patience Biff, the problem is that I need 120 named ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. Graham Biff wrote: Exactly how many names do you have? Choose will work with up to 29 names. I really need the indirect function Forget about Indirect. It won't work! Indirect needs a TEXT representation of a reference. When you use =INDIRECT(B11) the name in B11 evaluates to the FORMULA: =OFFSET(.....................). This is not a TEXT representation of a reference! Try this: List the names in a range of cells, say, AA1:AAn AA1 = Class_1 AA2 = Class_2 AA3 = Class_3 etc As the source for the drop down: =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_ 2,Class_3) Biff "Graham Haughs" wrote in message l... Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message l... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F) ,1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
Many thanks for this Harlan. I am just starting to get my head round it
but it is only 6 o'clock in the morning so it is a slow process! I am grateful for all your efforts. Graham Harlan Grove wrote: Graham Haughs wrote... Thanks for your patience Biff, the problem is that I need 120 named ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. ... "Graham Haughs" wrote in message I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1 ) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. So Class_6 holds the result of an OFFSET call, and it happens to be based on column F in the Data worksheet. Column F is the 6th column of that worksheet. Is there a correspondence between columns in the worksheet and the number in the Class_# defined names? If so, you could make the validation range =INDEX(Data!$2:$2,B11):INDEX(Data!$A:$DP,COUNTA(IN DEX(Data!$A:$DP,0,B11)),B11) Otherwise, you'd need a table of these defined names and their current addresses, use the B11 value as a lookup value into that table to pull the range address, then use that as the argument to INDIRECT. In that case, your data validation list reference would be something like =INDIRECT(VLOOKUP(B11,RefTbl,2,0)) The first column of RefTbl would be your Class_# defined names, and the second column the corresponding addresses, which could be produced by a multistep process like 1. Enter text formula in topmost cell of 2nd column of RefTbl, ="=SUBSTITUTE(CELL(""Address"",(Data!A1,"&X101&")) ,""$A$1,"","""",1)" where X101 would be the topmost cell in the 1st column of RefTbl. Fill down so there's one of these formulas for each defined name. 2. Copy the column of formulas and paste special as values on top of itself. 3. With the range of former formulas still selected, run Edit Replace and replace = with =. That might seem to be no change, but it has the effect of taking text constants that look like formulas and entering them as formulas. The result should be the range addresses of the defined names. |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Indirect and Dynamic Range
I was thinking that you might have 120 ranges scattered all over the place
which would have been pretty complicated to figure out. But, since it's a contiguous range it's fairly straightfoward. The formula can be simplified further if you were to just use numbers to indicate which column you needed. Doing this would eliminate the MATCH functions. For example, entering in B11, just 6 instead of Class_6. Then you don't have to match the column header and the formula would be: =OFFSET(Sheet2!$A$2,,Sheet1!$B$11-1,COUNTA(INDEX(Sheet2!$A:$DP,,Sheet1!$B$11))-1) Quite a bit shorter. Biff "Graham Haughs" wrote in message ... Sorry for delay in reply but was around midnight last time I posted. This works and works well. I really appreciate the time and effort you put into this. It may be easy stuff to you but there is no way I would ever have arrived at what you produced which makes the whole thing a lot simpler and slicker. Many thanks. Graham Biff wrote: Ooops! The formula references a range of 124 columns (column DT), not 120 (column DP)! Here is the formula with the correct column range: =OFFSET(Sheet2!$A$1,1,MATCH(Sheet1!$B$11,Sheet2!$A $1:$DP$1,0)-1,COUNTA(INDEX(Sheet2!$A:$DP,,MATCH(Sheet1!$B$11,S heet2!$A$1:$DP$1,0)))-1) Biff "Biff" wrote in message ... don't spend too much time on it. Eh, most of this stuff is fairly easy! This took me about 10 mins to figure out AND test. I enjoy problem solving! Ok........ Assume the 120 ranges are on Sheet2, columns A to DT. Row 1 is the column headers. Sheet1 B11 is where you enter the desired range name. Create this named formula: Name: GetRange Refers to: =OFFSET(Sheet2!$A$1,1,MATCH(Sheet1!$B$11,Sheet2 !$A$1:$DT$1,0)-1,COUNTA(INDEX(Sheet2!$A:$DT,,MATCH(Sheet1!$B$11,S heet2!$A$1:$DT$1,0)))-1) As the source for the drop down: =GetRange Biff "Graham Haughs" wrote in message . .. The ranges are in one worksheet in the main workbook. They are in a contiguous 120 column block but the number of rows with entries is variable although there are no blank entries, ie one column may have 4 entries starting at the second row, another may have 15 but there are no blanks in these entries, and all entries are numerical, non decimal. The names are Class_1 to Class_120, the top row of each column carrying this name. Thanks for the help Biff but don't spend too much time on it. Patience is not a virtue, it is a minor state of despair! Graham Graham Biff wrote: Can you provide more details? Where EXACTLY are these named ranges located? Are they in a contiguous 120 column block? Are the names Class_1 to Class_120? Biff "Biff" wrote in message . .. I need 120 named ranges I've never had to deal with that many in this context! Let me see if I can come up with something. No guarantees! Biff "Graham Haughs" wrote in message .. . Thanks for your patience Biff, the problem is that I need 120 named ranges so I will have to re-think strategy as you have confirmed that indirect is no use in ths situation. Graham Biff wrote: Exactly how many names do you have? Choose will work with up to 29 names. I really need the indirect function Forget about Indirect. It won't work! Indirect needs a TEXT representation of a reference. When you use =INDIRECT(B11) the name in B11 evaluates to the FORMULA: =OFFSET(.....................). This is not a TEXT representation of a reference! Try this: List the names in a range of cells, say, AA1:AAn AA1 = Class_1 AA2 = Class_2 AA3 = Class_3 etc As the source for the drop down: =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class _2,Class_3) Biff "Graham Haughs" wrote in message bl... Thanks for feedback but I really need the indirect function. I know I can use =Class_6 etc but E11 will be changing, as it is a drop down list. It may be Class_5, Class_6, Class_12 etc so the data validation has to pick up the named range which this indicates. If it was not a dynamic range it works fine, I just do =E11 and whatever value appears in cell E11 the appropriate named range will appear as the drop down list, but NOT when it is a dynamic range. Sorry to be a pain but I really need it to do this. Graham Biff wrote: Hi! Do you enter more than just "Class_6" into B11? Try this as the source for the drop down: =CHOOSE(1,Class_6) Biff "Graham Haughs" wrote in message bl... I have a named dynamic range Class_6 which holds the dynamic range =OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1) I create a data validation list and put the location as =INDIRECT(B11) In cell B11 is the value Class_6 When I try to enter =indirect(B11) in the data validation I get the message "The source currently evaluates to an error" I can get the indirect to work with a named list but not with a dynamic range list. Am I doing something wrong or will it not do this. I value any help. Kind regards, Graham Haughs Turriff Scotland |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic range name - odd results | Excel Worksheet Functions | |||
| Dynamic Range | Setting up and Configuration of Excel | |||
| Dynamic range in chart | Charts and Charting in Excel | |||
| Dynamic Ranges using INDIRECT | Excel Worksheet Functions | |||
| Indirect and dynamic ranges | Excel Worksheet Functions | |||
Linear Mode
