I'm trying to find the equivalent of the INDIRECT function, but instead of a cell location, I'd like to "build" a Range. As an example, I'd like to convert the following text, "CONCATENATE("Sheet","'!A1:B4") to a range that Excel can understand. If this were simply a cell reference, then the text string could be the argument for the INDIRECT function. Is there a way to do this? Thanks in advance
Not sure what you're looking for but it'll work as you describe. D1 = text string: Sheet1!A1:B4 =SUM(INDIRECT(D1)) D1 = a formulated reference: ="Sheet1!A"&A1&":B"&B1 Where A1 and B1 contain the row numbers =SUM(INDIRECT(D1)) -- Biff Microsoft Excel MVP "jmt" wrote in message news:E44C3C0D-4A3C-44AC-8774-3F0BF6E700F1@microsoft.com... > I'm trying to find the equivalent of the INDIRECT function, but instead of > a > cell location, I'd like to "build" a Range. As an example, I'd like to > convert the following text, "CONCATENATE("Sheet","'!A1:B4") to a range > that > Excel can understand. If this were simply a cell reference, then the text > string could be the argument for the INDIRECT function. Is there a way to > do > this? Thanks in advance
Thank you. My error was omiting an apostrophe (') before th sheet name. "T. Valko" wrote: > Not sure what you're looking for but it'll work as you describe. > > D1 = text string: Sheet1!A1:B4 > > =SUM(INDIRECT(D1)) > > D1 = a formulated reference: ="Sheet1!A"&A1&":B"&B1 > > Where A1 and B1 contain the row numbers > > =SUM(INDIRECT(D1)) > > -- > Biff > Microsoft Excel MVP > > > "jmt" wrote in message > news:E44C3C0D-4A3C-44AC-8774-3F0BF6E700F1@microsoft.com... > > I'm trying to find the equivalent of the INDIRECT function, but instead of > > a > > cell location, I'd like to "build" a Range. As an example, I'd like to > > convert the following text, "CONCATENATE("Sheet","'!A1:B4") to a range > > that > > Excel can understand. If this were simply a cell reference, then the text > > string could be the argument for the INDIRECT function. Is there a way to > > do > > this? Thanks in advance > > >
Thanks for feeding back! -- Biff Microsoft Excel MVP "jmt" wrote in message news:3176CBCD-8F8F-4DB4-8638-8A140B30F4FE@microsoft.com... > Thank you. My error was omiting an apostrophe (') before th sheet name. > > "T. Valko" wrote: > >> Not sure what you're looking for but it'll work as you describe. >> >> D1 = text string: Sheet1!A1:B4 >> >> =SUM(INDIRECT(D1)) >> >> D1 = a formulated reference: ="Sheet1!A"&A1&":B"&B1 >> >> Where A1 and B1 contain the row numbers >> >> =SUM(INDIRECT(D1)) >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "jmt" wrote in message >> news:E44C3C0D-4A3C-44AC-8774-3F0BF6E700F1@microsoft.com... >> > I'm trying to find the equivalent of the INDIRECT function, but instead >> > of >> > a >> > cell location, I'd like to "build" a Range. As an example, I'd like to >> > convert the following text, "CONCATENATE("Sheet","'!A1:B4") to a range >> > that >> > Excel can understand. If this were simply a cell reference, then the >> > text >> > string could be the argument for the INDIRECT function. Is there a way >> > to >> > do >> > this? Thanks in advance >> >> >>
