Excel_User
New Member
=IF(ROW(A1)>ABS(SumOfNamedRanges),-OFFSET(A1,SumOfNamedRanges,0),IF(ISBLANK(NamedRangeA),INDEX(NamedRangeB,OFFSET(A1,NamedRangeAOffset,0),0),INDEX(NamedRangeA,0,0)))
So I have a problem and that problem is that I have this working on one excel sheet perfectly but not another. Step by step the formula does the following:
1. Check to see if all named values has been exhausted by taking all count values [ count(NamedRangeA) + count(NamedRangeB) ] and comparing them to the rows. Upon that condition being met it begins a reversal of those values for transfer between accounts.
2. Check to see if NamedRangeA is exhausted, if it is then it begins to index NamedRangeB by offsetting the count [ count(NamedRangeA) ] shifting the cell reference to the top of NamedRangeB.
3. If none of the checks pass it simply indexes NamedRangeA until it exhausts.
Now the reason I am using this particular format is because the values shift from report to report so the indexing of values for X thousand rows simply updates these values instantly by deleting and replacing the report in the designated tab. I therefore cannot use static ranges.
I've an overwhelming feeling that the problem lies with the "offset" function and I need an alternative to be able to grab values swapping from range to range but starting at the beginning of each range instead of directly indexing the incorrect row. I have tried using the "Row" function but have not found a way to make it return to the top and carry all the way through the entire spreadsheet which is a few thousand lines long.
In essence because I do not ever know when Report A or Report B will begin or end I must have the formula always be able to properly offset to the top. Thanks for your consideration.
So I have a problem and that problem is that I have this working on one excel sheet perfectly but not another. Step by step the formula does the following:
1. Check to see if all named values has been exhausted by taking all count values [ count(NamedRangeA) + count(NamedRangeB) ] and comparing them to the rows. Upon that condition being met it begins a reversal of those values for transfer between accounts.
2. Check to see if NamedRangeA is exhausted, if it is then it begins to index NamedRangeB by offsetting the count [ count(NamedRangeA) ] shifting the cell reference to the top of NamedRangeB.
3. If none of the checks pass it simply indexes NamedRangeA until it exhausts.
Now the reason I am using this particular format is because the values shift from report to report so the indexing of values for X thousand rows simply updates these values instantly by deleting and replacing the report in the designated tab. I therefore cannot use static ranges.
I've an overwhelming feeling that the problem lies with the "offset" function and I need an alternative to be able to grab values swapping from range to range but starting at the beginning of each range instead of directly indexing the incorrect row. I have tried using the "Row" function but have not found a way to make it return to the top and carry all the way through the entire spreadsheet which is a few thousand lines long.
In essence because I do not ever know when Report A or Report B will begin or end I must have the formula always be able to properly offset to the top. Thanks for your consideration.