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

#### Attachments

• 10.9 KB Views: 4

#### 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

• 11.2 KB Views: 2

#### 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