Excel, using cell text references as input for formula

Question

First of all thanks for your help. I have a problem regarding the use of text into formulas. I am working on a huge dataset of stocks of several years. The stock are not correctly grouped. I used an index match formula to correctly group the stocks, with date and returns. However, this took ages for my file because for every cell it looks through the the whole row and colums.

I am trying to reduce the calculation time by creating a smaller range by locating the first appearance of the stock in my sample using the adress match formula. This gives me a cell location (let's say it is F5) and it gives me this location in cell C5. So now we have cell C5 wich has as a string F5 in it. I know the ending of the range because the cell location of the next stock is at the same time the end of the range of the previous stock (alphabetically ordend)

Here is the problem. I want to use the string in C5 (which is F5) as start of my range in the formula and the next cell, say D5 has the ending of the range (which is e.g. F10). However, whatever I try the string is not incorporated in my formula. See =IFERROR(INDEX('WRDS Input'!$O$2:$O$1048576;MATCH(Sheet1!$A11&Sheet1!C$4;'WRDS Input'!$B$2:$B$1048576&'WRDS Input'!$F$2:$F$1048576;0));""). I want to change the ranges to the strings in the cells and when i copy it to the right it uses the string in cell D5

I hope this is clearer

Can somebody help me? Or dan anyone know a better way?

Solution

You need to use indirect formula

=indirect(C5 & ":" & D5)

would give you the range F5:F10

=sum(indirect(C5 & ":" & D5))

would sum F5:F10. Assuming that cell C5 has the value F5 and cell D5 has the value F10. This can be used inside any formula.

Comments

[Iszi]

Short, incomplete answer: I think you need to familiarize yourself with the Excel naming conventions for references that cross between sheets and/or workbooks. Then, fix your formula with INDIRECT and CONCATENATE. However, I'm a little unsure that I properly understand your problem. Can you re-phrase/re-format your post to be less wall-of-texty, and perhaps include some sample data and expected outputs? Also include what formula(s) you're currently using which apply to this problem.

[Melvin]

Thanks for the swift answer. The problem is that I have a cell (e.g. C5) which has as text in it F5 (reference to another cell) I want to make a formula in which I use that data included in C5 (which is F5) as for instance beginning of a range

[Scott - Слава Україні]

(1) I don’t understand your question either. Please edit the question to clean it up, add clarifying detail, add example data, etc. Even though we’re asking you questions in comments, you shouldn’t use comments to provide information that belongs in the main question. (2) You seem to be talking about having the string value “F5” in cell C5, and you want to use cell C5 to access a range starting at cell F5. How are you specifying the end of the range?

[Melvin]

I have edited my question and hope it is clearer now. And I want to accomplish exactly what you are stating.

[Melvin]

Thanks for the answer but the problem is in that case it does not give me F5 but the value which is in cell F5.

[gtwebb]

indirect is often used inside other functions. I've added an example

[Melvin]

Thanks again, but for one reason or the other it still doesn't work. This is the formula: =IFERROR(INDEX(SUM(INDIRECT(C6&":"&C9));MATCH(Sheet1!$A11&Sh‌​eet1!C$4;SUM(INDIREC‌​T(C7&":"&C10))&SUM(I‌​NDIRECT(C5&":"&C8));‌​0));"") This links to the several ranges (e.g. in C6= 'WRDS Input'!$F$23). But when it use this formula it still doesn't work. However, when I fill in the hardcoded start of the range it does work. Thank you in advance

[Melvin]

And I am quite sure that indirect in this case will give the value that is in 'WRDS Input'!$F$23 instead of the value 'WRDS Input'!$F$23 as start of a range

[gtwebb]

I don't think index(sum(... does what you think it does. Sum was just an example of how you can use indirent inside a function. If your first input to an index function is a sum then it returns a single value which you wouldn't use an index for. Secondly I'm pretty sure you can't use match(a2,b2:b5 & c2:c5) as concatenation functions don't work that way inside a function. If you are quite sure you know what it is doing I will leave you to it, have fun.

[fixer1234]

This appears to be a totally different problem. Giving a long Excel formula for a different problem isn't really helpful or an answer to this problem.

[Neville]

What I understood from the OP was that he had problems regarding the references and use of the INDIRECT, similar to problems I experienced prior to my solution. I pasted my solution as reference and merely tried to explain a bit about it in an effort for clarity on it's use.