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

Witchcraft in Excel / Same OFFSET formula works only on certain cells within spreadsheet

Cocox2

New Member
Hi everyone, so I am witnessing some strange behaviour when trying to make a spreadsheet for work.

If you take a look at the file, you will see that on cell N4 I sum up the content of cells A4 to L4 until it adds up to less than the target value (M4). Moreover, on O4 I count the amount of cells that were added in the aforementioned operation.

The problem that I am encountering is that if I copy the whole table (range A2:O4) to another location wihin the spreadsheet, the two colored cells with the formulas that I mentioned earlier will only work sometimes.

For instance, I think that pasting the table on any row, but starting from column G onwards, the formulas stop working. Whatever is happening is way out of my league.

This will be a problem for me, as the spreadsheet that I am trying to create will require this formula to be used multiple times on many cells.

Please help o_O
 

Attachments

  • Troublemaker Formula.xlsx
    10.9 KB · Views: 4
Hi Cocox2,

Strange nobody answered before. Your formula gets wrong because of the use of column as last argument in the Offset function. It works "accidentally" in the tables starting at column A since they generate a number from 1 to 12, which is what's needed.
So it can get tweak using that part as array variable.
=LOOKUP(M4,SUBTOTAL(9,OFFSET(A4,,,,{1,2,3,4,5,6,7,8,9,10,11,12})))
 

Attachments

  • No more Troublemaker Formula.xlsx
    11.2 KB · Views: 2
Try...........

1] Change your N4 formula :

From this >>

=LOOKUP(M4,SUBTOTAL(9,OFFSET(A4,,,,COLUMN(A4:L4))))

Into this >>

=LOOKUP(M4,SUBTOTAL(9,OFFSET(A4,,,,COLUMN(A4:L4)-COLUMN(A4)+1)))

And,

2] Change your O4 array formula (Ctrl+Shift+Enter) :

From this >>

=MATCH(M4,SUBTOTAL(9,OFFSET(A4,,,,COLUMN(A4:L4))))

Into this >>

=MATCH(M4,SUBTOTAL(9,OFFSET(A4,,,,COLUMN(A4:L4)-COLUMN(A4)+1)))

Then,

You could copy & paste range A2:O4 to another location within the spreadsheet area.

Regards
Bosco
 
Back
Top