• 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

GraH - Guido

Well-Known Member
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

bosco_yip

Excel Ninja
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
 
Top