• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Indexing Multiple Named Ranges, some difficulty in consistency

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.
 
Hi.

Would you be able to post a workbook with a small example? It doesn't have to be your real data: a mocked-up version with dummy data will suffice, providing it illustrates the same issue.

Regards
 
Thanks. But I can't seem to see any indication of which results in here are not what you wished for, nor what the results should be.

Regards
 
Oh, I apologize, I gave you the working one which iterates the two named ranges perfectly. Here is the broken one.

I need range M from the Revenue tab and range O from the Cost tab to smoothly run through and it's not working at all.
 

Attachments

  • Broken Applicable File.xlsx
    25 KB · Views: 1
Forgive me, but I have no idea what "smoothly run through" means in this context. Have you included your actual desired results in this file?

Regards
 
No. Here is an updated file with the desired result based on the values present. It's in a new tab.

I need all of the values from both named ranges to appear in one column and then reverse out. I just cannot get the second named range to start and show correctly here.
 

Attachments

  • Broken Applicable File.xlsx
    29.8 KB · Views: 9
Thanks a lot. Can you clarify what "Reversal of values" means? They do not appear to be in a "reverse" order to the original list.

Regards
 
I personally wouldn't have defined Revenue and Current as referring to entire columns, since this means that the header values ("CURRENT_AMORTIZED" and "Total Amortized") are included in those definitions, which complicates things a touch.

However, in A2:

=IF(ROWS($1:1)>2*COUNT(Revenue,Current),"",-1^(ROWS($1:1)>COUNT(Revenue,Current))*IF(1+MOD(ROWS($1:1)-1,COUNT(Revenue,Current))>COUNT(Revenue),INDEX(Current,2+MOD(ROWS($1:1)-1,COUNT(Revenue,Current))-COUNT(Revenue)),INDEX(Revenue,2+MOD(ROWS($1:1)-1,COUNT(Revenue,Current)))))

Copy down as required.

I disagree with your expected result for row 99.

Regards
 
Wonderful, so is it always blue? Also, what exactly did you do so I can understand it because this current formula works but if there are any shifts or changes I may have some issues if I don't know what this formula is doing.

More specifically I don't understand the carat (^) and what you are taking the power of.
 
Sure.

The first part:

IF(ROWS($1:1)>2*COUNT(Revenue,Current),""

is obvious enough, meaning that, in rows beyond twice the total count from the two ranges, a blank will be returned.

The part:

1+MOD(ROWS($1:1)-1,COUNT(Revenue,Current))

which is equal to 1, and becomes when copied down, successively:

1+MOD(ROWS($1:2)-1,COUNT(Revenue,Current))
1+MOD(ROWS($1:3)-1,COUNT(Revenue,Current))
1+MOD(ROWS($1:4)-1,COUNT(Revenue,Current))

...

etc., which are equal to:

2
3
4
...


etc., but also, importantly, when we arrive at a relative row number which is greater than the total count from the two ranges, we effectively begin this series again. For example, in row 97, where the reversal is to begin, we have:

1+MOD(ROWS($1:96)-1,COUNT(Revenue,Current))

which evaluates to:

1+MOD(95,95)

i.e. 1

Likewise, in row 98 onwards we have:

1+MOD(ROWS($1:97)-1,COUNT(Revenue,Current))
1+MOD(ROWS($1:98)-1,COUNT(Revenue,Current))
1+MOD(ROWS($1:99)-1,COUNT(Revenue,Current))
...


etc., i.e.:

2
3
4
...


etc.

Note that, in general, for any positive value x less than 95:

MOD(x,95)

is just x, and so this part does not affect the values generated in formulas prior to that in row 97.

Hence the part:

IF(1+MOD(ROWS($1:1)-1,COUNT(Revenue,Current))>COUNT(Revenue),INDEX(Current,2+MOD(ROWS($1:1)-1,COUNT(Revenue,Current))-COUNT(Revenue)),INDEX(Revenue,2+MOD(ROWS($1:1)-1,COUNT(Revenue,Current))))

indicates that, if the result of the aforementioned portion is greater than the number of (numerical) entries in Revenue, we INDEX Current. Conversely, if the initial IF statement is FALSE we instead INDEX Revenue.

I hope you can work out the logic for yourself - similar to that I outlined above - behind the constructions generating the row_num parameter being passed to INDEX in each case.

Finally, the the above is multiplied by the result of the portion:

-1^(ROWS($1:1)>COUNT(Revenue,Current))

which will only ever result in either -1 (when the relative row number of the formula is greater than the total count from the two ranges) or 1 (otherwise).

For example, in the first instance of the formula (in row 2) this part resolves to:

-1^(1>95)

which is:

-1^FALSE

i.e.:

-1^0

i.e. 1

And in, for example, row 97, this part is:

-1^(ROWS($1:96)>COUNT(Revenue,Current))

which is:

-1^(96>95)

i.e.:

-1^TRUE

i.e.:

-1^1

i.e. -1, as desired.

Hope that helps. Yes, it's always blue. :)

Regards
 
Back
Top