Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
Hi,
I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
This worked for me using
=INDEX(INDIRECT(C1&C2),1,1) -- __________________________________ HTH Bob "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
INDIRECT requires a text representation of a *valid reference*. The OFFSET
formula does not meet this requirement. When you call SGDCalendar you're actually calling the named OFFSET *formula* which is not a text representation of a valid reference. About the only way I know of to get around this invovles using the CHOOSE function. However, since you're concatentating strings to build the named range this adds complexity to the mix. I love complexity! <g There's nothing "special" about your named range, it's just a static range. Why don't you just use: SGDCalendar =Sheetname!$A$1:$A$4000 Replace "Sheetname" with the actual sheet name. -- Biff Microsoft Excel MVP "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
TryThis refers to =OFFSET(Sheet1!$A$1,0,0,10,1)
C1 = Try C2 = This =INDEX(INDIRECT(C1&C2),1,1) I get #REF! -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... This worked for me using =INDEX(INDIRECT(C1&C2),1,1) -- __________________________________ HTH Bob "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
Unless he made a typo in his example, there is something special, and
there's nothing "static" about his formula. His *relative* column reference in the Offset formula makes the calculating range location relative to the location of any formula using the named range - dependant on which column was in focus during the creation of the named range. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... INDIRECT requires a text representation of a *valid reference*. The OFFSET formula does not meet this requirement. When you call SGDCalendar you're actually calling the named OFFSET *formula* which is not a text representation of a valid reference. About the only way I know of to get around this invovles using the CHOOSE function. However, since you're concatentating strings to build the named range this adds complexity to the mix. I love complexity! <g There's nothing "special" about your named range, it's just a static range. Why don't you just use: SGDCalendar =Sheetname!$A$1:$A$4000 Replace "Sheetname" with the actual sheet name. -- Biff Microsoft Excel MVP "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
there's nothing "static" about his formula.
Nothing? Assuming you're correct about the relative column, the height and width is still static! -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... Unless he made a typo in his example, there is something special, and there's nothing "static" about his formula. His *relative* column reference in the Offset formula makes the calculating range location relative to the location of any formula using the named range - dependant on which column was in focus during the creation of the named range. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... INDIRECT requires a text representation of a *valid reference*. The OFFSET formula does not meet this requirement. When you call SGDCalendar you're actually calling the named OFFSET *formula* which is not a text representation of a valid reference. About the only way I know of to get around this invovles using the CHOOSE function. However, since you're concatentating strings to build the named range this adds complexity to the mix. I love complexity! <g There's nothing "special" about your named range, it's just a static range. Why don't you just use: SGDCalendar =Sheetname!$A$1:$A$4000 Replace "Sheetname" with the actual sheet name. -- Biff Microsoft Excel MVP "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
C'mon Biff, we're talking about location (address) here.
You are getting a bit like Harlan now!<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... there's nothing "static" about his formula. Nothing? Assuming you're correct about the relative column, the height and width is still static! -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... Unless he made a typo in his example, there is something special, and there's nothing "static" about his formula. His *relative* column reference in the Offset formula makes the calculating range location relative to the location of any formula using the named range - dependant on which column was in focus during the creation of the named range. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... INDIRECT requires a text representation of a *valid reference*. The OFFSET formula does not meet this requirement. When you call SGDCalendar you're actually calling the named OFFSET *formula* which is not a text representation of a valid reference. About the only way I know of to get around this invovles using the CHOOSE function. However, since you're concatentating strings to build the named range this adds complexity to the mix. I love complexity! <g There's nothing "special" about your named range, it's just a static range. Why don't you just use: SGDCalendar =Sheetname!$A$1:$A$4000 Replace "Sheetname" with the actual sheet name. -- Biff Microsoft Excel MVP "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
You are getting a bit like Harlan now!<bg
That's a tremendous compliment to me but I'm sure Harlan would balk! Really, I'm not worthy. <g -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... C'mon Biff, we're talking about location (address) here. You are getting a bit like Harlan now!<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... there's nothing "static" about his formula. Nothing? Assuming you're correct about the relative column, the height and width is still static! -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... Unless he made a typo in his example, there is something special, and there's nothing "static" about his formula. His *relative* column reference in the Offset formula makes the calculating range location relative to the location of any formula using the named range - dependant on which column was in focus during the creation of the named range. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... INDIRECT requires a text representation of a *valid reference*. The OFFSET formula does not meet this requirement. When you call SGDCalendar you're actually calling the named OFFSET *formula* which is not a text representation of a valid reference. About the only way I know of to get around this invovles using the CHOOSE function. However, since you're concatentating strings to build the named range this adds complexity to the mix. I love complexity! <g There's nothing "special" about your named range, it's just a static range. Why don't you just use: SGDCalendar =Sheetname!$A$1:$A$4000 Replace "Sheetname" with the actual sheet name. -- Biff Microsoft Excel MVP "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
Here's how it can be done using CHOOSE.
Assume you have named ranges defined using OFFSET: RngOne RngTwo A1 = Rng A2 = Two =INDEX(CHOOSE(MATCH(A1&A2,{"RngOne","RngTwo"},0),R ngOne,RngTwo),1) Note that you're limited to the number of ranges that CHOOSE can handle depending on which version of Excel you're using. In Excel 2007 you can have up to 254 ranges. In other versions it's limited to 29. If you have more than a few ranges you can create a table and MATCH the name in the table rather than hardcode the names as I've done in the above formula. J1 = RngOne J2 = RngTwo =INDEX(CHOOSE(MATCH(A1&A2,J1:J2,0),RngOne,RngTwo), 1) Although I've never had to do it, I imagine there should be some way to make this work if you have more ranges than CHOOSE can handle. Probably a nested CHOOSE CHOOSE combination would work. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... INDIRECT requires a text representation of a *valid reference*. The OFFSET formula does not meet this requirement. When you call SGDCalendar you're actually calling the named OFFSET *formula* which is not a text representation of a valid reference. About the only way I know of to get around this invovles using the CHOOSE function. However, since you're concatentating strings to build the named range this adds complexity to the mix. I love complexity! <g There's nothing "special" about your named range, it's just a static range. Why don't you just use: SGDCalendar =Sheetname!$A$1:$A$4000 Replace "Sheetname" with the actual sheet name. -- Biff Microsoft Excel MVP "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
I did say a *bit*.
Harlan has his bad bits and his good bits, though his good bits do far outnumber his bad bits.<vbg -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... You are getting a bit like Harlan now!<bg That's a tremendous compliment to me but I'm sure Harlan would balk! Really, I'm not worthy. <g -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... C'mon Biff, we're talking about location (address) here. You are getting a bit like Harlan now!<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... there's nothing "static" about his formula. Nothing? Assuming you're correct about the relative column, the height and width is still static! -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... Unless he made a typo in his example, there is something special, and there's nothing "static" about his formula. His *relative* column reference in the Offset formula makes the calculating range location relative to the location of any formula using the named range - dependant on which column was in focus during the creation of the named range. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... INDIRECT requires a text representation of a *valid reference*. The OFFSET formula does not meet this requirement. When you call SGDCalendar you're actually calling the named OFFSET *formula* which is not a text representation of a valid reference. About the only way I know of to get around this invovles using the CHOOSE function. However, since you're concatentating strings to build the named range this adds complexity to the mix. I love complexity! <g There's nothing "special" about your named range, it's just a static range. Why don't you just use: SGDCalendar =Sheetname!$A$1:$A$4000 Replace "Sheetname" with the actual sheet name. -- Biff Microsoft Excel MVP "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
Ok, my VBA is bad, very bad, but I have a 'partial' solution. Currently, I
have it set to a macro that I run, but because of my bad VBA, it still shows the blinking dashes around the 'handwritten formula' from the VBA copy. Building off the TryThis example, with Try in C1, This in C2, also row in E1, column in E2. In a 'dummy' cell, I have the following (my dummy cell is H8). ="="&LEFT($C$1,3)&LEFT($C$2,4) TryThis is defined as: =INDEX(OFFSET('Sheet1'!$A$1,0,0,10,1),'Sheet1'!$E$ 1,'Sheet1'!$E$2) And currently, my macro coding is as follows: Sub ConvertStringToFormula() Range("H8").Copy With ActiveCell .PasteSpecial ActiveCell.Formula = ActiveCell.Text Range("H9").Select End With End Sub As I said, it's crude, if you know more than me about VBA (which shouldn't be difficult), you can modify to suit your needs. -- John C "T. Valko" wrote: Here's how it can be done using CHOOSE. Assume you have named ranges defined using OFFSET: RngOne RngTwo A1 = Rng A2 = Two =INDEX(CHOOSE(MATCH(A1&A2,{"RngOne","RngTwo"},0),R ngOne,RngTwo),1) Note that you're limited to the number of ranges that CHOOSE can handle depending on which version of Excel you're using. In Excel 2007 you can have up to 254 ranges. In other versions it's limited to 29. If you have more than a few ranges you can create a table and MATCH the name in the table rather than hardcode the names as I've done in the above formula. J1 = RngOne J2 = RngTwo =INDEX(CHOOSE(MATCH(A1&A2,J1:J2,0),RngOne,RngTwo), 1) Although I've never had to do it, I imagine there should be some way to make this work if you have more ranges than CHOOSE can handle. Probably a nested CHOOSE CHOOSE combination would work. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... INDIRECT requires a text representation of a *valid reference*. The OFFSET formula does not meet this requirement. When you call SGDCalendar you're actually calling the named OFFSET *formula* which is not a text representation of a valid reference. About the only way I know of to get around this invovles using the CHOOSE function. However, since you're concatentating strings to build the named range this adds complexity to the mix. I love complexity! <g There's nothing "special" about your named range, it's just a static range. Why don't you just use: SGDCalendar =Sheetname!$A$1:$A$4000 Replace "Sheetname" with the actual sheet name. -- Biff Microsoft Excel MVP "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cant use indirect() and dynamic ranges together?
my VBA is bad, very bad
You should see mine!. -- Biff Microsoft Excel MVP "John C" <johnc@stateofdenial wrote in message ... Ok, my VBA is bad, very bad, but I have a 'partial' solution. Currently, I have it set to a macro that I run, but because of my bad VBA, it still shows the blinking dashes around the 'handwritten formula' from the VBA copy. Building off the TryThis example, with Try in C1, This in C2, also row in E1, column in E2. In a 'dummy' cell, I have the following (my dummy cell is H8). ="="&LEFT($C$1,3)&LEFT($C$2,4) TryThis is defined as: =INDEX(OFFSET('Sheet1'!$A$1,0,0,10,1),'Sheet1'!$E$ 1,'Sheet1'!$E$2) And currently, my macro coding is as follows: Sub ConvertStringToFormula() Range("H8").Copy With ActiveCell .PasteSpecial ActiveCell.Formula = ActiveCell.Text Range("H9").Select End With End Sub As I said, it's crude, if you know more than me about VBA (which shouldn't be difficult), you can modify to suit your needs. -- John C "T. Valko" wrote: Here's how it can be done using CHOOSE. Assume you have named ranges defined using OFFSET: RngOne RngTwo A1 = Rng A2 = Two =INDEX(CHOOSE(MATCH(A1&A2,{"RngOne","RngTwo"},0),R ngOne,RngTwo),1) Note that you're limited to the number of ranges that CHOOSE can handle depending on which version of Excel you're using. In Excel 2007 you can have up to 254 ranges. In other versions it's limited to 29. If you have more than a few ranges you can create a table and MATCH the name in the table rather than hardcode the names as I've done in the above formula. J1 = RngOne J2 = RngTwo =INDEX(CHOOSE(MATCH(A1&A2,J1:J2,0),RngOne,RngTwo), 1) Although I've never had to do it, I imagine there should be some way to make this work if you have more ranges than CHOOSE can handle. Probably a nested CHOOSE CHOOSE combination would work. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... INDIRECT requires a text representation of a *valid reference*. The OFFSET formula does not meet this requirement. When you call SGDCalendar you're actually calling the named OFFSET *formula* which is not a text representation of a valid reference. About the only way I know of to get around this invovles using the CHOOSE function. However, since you're concatentating strings to build the named range this adds complexity to the mix. I love complexity! <g There's nothing "special" about your named range, it's just a static range. Why don't you just use: SGDCalendar =Sheetname!$A$1:$A$4000 Replace "Sheetname" with the actual sheet name. -- Biff Microsoft Excel MVP "nashism" wrote in message ... Hi, I have defined a range using the following syntax: SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet I have similarly defined other names like MYRCalendar, IDRCalendar primarily because I have other data that is labeled starting SGD, MYR etc that I would like to combine this with. now when I try to get a value from the range as INDEX(SGDCalendar,1,1) it works correctly but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it gives me an #REF! error (note that "SGD" and "Calendar" will be strings that will come from elsewhere in my workbook) I have troubled many people around me to solve this but cant seem to get any further. Any help would be greatly appreciated! ciao Nash |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT with dynamic range | Excel Discussion (Misc queries) | |||
Indirect and Dynamic Range | Excel Worksheet Functions | |||
Dynamic Ranges using INDIRECT | Excel Worksheet Functions | |||
Indirect and dynamic ranges | Excel Worksheet Functions | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions |