Return range name from concatenated strings

Thanks JB...

But have tried that already (see below).

The only thing that has worked for me so far is to hard-code the account number into the formula:

=INDIRECT(dPath&dFn1&dAc5000)

I have also tried the following without getting the desired result:

=INDIRECT(dPath&dFn1&B1)
=INDIRECT(dPath&dFn1&"dAc"&A1))
=INDIRECT(dPath&dFn1&INDIRECT("dAc"&A1))

where A1:=5000 (original account number) and B1:="dAc"&A1 (concatenated string)

In each case, B1 returns dAc5000 as a string only (as expected). Like you suggested, I had thought that perhaps a nested INDIRECT formula might then interpret the string as a defined name but it still returns a #REF! error.

It's got me stumped...

Thanks RS.

Originally Posted by romperstomper

INDIRECT won't work unless it refers to an actual range, which it does not do here.

That seems to be the essence of this problem. I works when I use a combination of predefined names, but not when I want to use 2 strings combined to represent one of those names.

Is there a way to have Excel recognize a concatenated string as a (predefined) name?

Originally Posted by romperstomper

You could use a table on a sheet with the references entered into cells and the names pointing at those cells.

Assuming I have, say, a couple of hundred accounts of data in the external worksheet, I already have a couple of hundred corresponding named ranges pointing to a different column range for each account. Multiply this by a couple of hundred companies, and you can see that the naming exercise can quickly get out of control.

So I read about and used the INDIRECT function, and arrived at a solution of defining a name for the data file path (in case the path changes), separate names for the file names/worksheets of different companies (so the user can input a code for each company of interest and data is retrieved based on the user input) and separate names for each account column range.

I am now just trying to find the final piece of the puzzle - a way to retrieve account data based on the number of an account inputted into a column on the same row where the data is displayed.

Would your idea of creating additional references to a separate table involving creating an additional name for each account? Is that what you had in mind? Can you elaborate? Would this solve the original problem of having to hardcode account numbers in the formulae on the main worksheet where the data is displayed?

Originally Posted by romperstomper

I imagine performance will be pretty slow with all those INDIRECT formulas in your workbook though.

As mentioned, I already have one workable way of accessing the data (only by hardcoding the account numbers), with a worksheet with hundreds of formulae but very little noticeable performance decline.

So I just expect this concept to provide some added flexibility without performance issues.

If I had a workable solution without hardcoding the account numbers, I could try it out and see what performance issues, if any, it does bring up. A slight performance decline with added flexibility might be a better problem to have.

Thanks again, RS.

Yes it does make sense, on both points.

Originally Posted by romperstomper

Yes, but only if that name actually points to a range. Here, that is not the case - it's a string.

The solution I thought may be possible/available would be use (or create) a function that can convert the string in such a way that it is then recognized as the named reference and returns the actual reference of the name (in this case, the range).

This could then be passed to the INDIRECT function as a range, and not a string.

At least that was the theory...

Originally Posted by romperstomper

If you create a table of addresses with the company name in one column and the range address in the next, you can probably do away with most of your names and use a lookup formula to return the correct range address to concatenate into your INDIRECT string.

I will try setting up the table of addresses. It may even end up being a little easier to maintain if the external data ranges change, rather than having to redefine names.

I will post any feedback once I've had a chance to get it done.

Thanks again for your time and useful feedback.

Originally Posted by romperstomper

All you would need to do is alter your dAc5000 name to point to a cell containing the text $B$1:$B$1000 for example. Then you could use INDIRECT("dAc"&A1) where A1 contains 5000.

Brilliant! Your suggestion works if I do the following:

Workable solution:

Use INDIRECT("dAc"&$C:$C)
where $C:$C is a column of account numbers (eg 5000)
Define dAc5000 as =accts!$N$627
where $N$627 contains =$V$111:$V$142 (a range in an external data file corresponding to account 5000)

Although I must admit I'm still unclear as to why the following solutions don't work, when the name dAc5000, as defined, returns the same range (eg $V111:$V$142) in each case:

Non-Working Solution 1:

Use INDIRECT("dAc"&$C:$C) (as above)
Define dAc5000 as ="$V$111:$V$142"

Non-Working Solution 2:

Use INDIRECT("dAc"&$C:$C) (as above)
Define dAc5000 as =INDEX(dAcctRange,MATCH(5000,dAcctId))
where
dAcctRange is =accts!$N:$N, column containing data file ranges (eg $V$111:$V$142)
dAcctId is =accts!$A:$A, column containing account numbers (eg 5000)

In each case, the name dAc5000 returns the same range, $V$111:$V$142, but only the first solution works. Even a seemingly more direct method (Non-Working Solution 1 - name defined as range) does not work while the less direct method does (Workable Solution - name defined as cell that contains range).

Is this just a quirk of the INDIRECT function or is there something a little more obvious that I'm just not understanding about the this function?

Thanks for the explanation and all your help with this issue. I've learned a lot. It's much appreciated.