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?
INDIRECT
andCONCATENATE
. 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.C5
, and you want to use cellC5
to access a range starting at cellF5
. How are you specifying the end of the range?