I wrote a simple sumif function in a column and copied down the range,

=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!A2,'Unit-E data'!G:G)


That was fine, but when I tried to sort the range I noticed that criteria
(cell reference) changed from A2 to something totally different for the row 2.

Is there any way to get round this problem?

Once you have copied the formula down, highlight all the cells with
the formula in and CTRL-H (Find & Replace):

Find what: ation'!A
Replace with: ation'!A$
Click Replace All

Hope this helps.

Pete

On Dec 5, 8:48*am, nc wrote:
I wrote a simple sumif function in a column and copied down the range,

=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!A2,'Unit-E data'!G:G)

That was fine, but when I tried to sort the range I noticed that criteria
(cell reference) changed from A2 to something totally different for the row 2.

Is there any way to get round this problem?

Thanks.

Did you mean

Replace with: ation'!$A


I replaced with the above and it solved the problem.


"Pete_UK" wrote:

Once you have copied the formula down, highlight all the cells with
the formula in and CTRL-H (Find & Replace):

Find what: ation'!A
Replace with: ation'!A$
Click Replace All

Hope this helps.

Pete

On Dec 5, 8:48 am, nc wrote:
I wrote a simple sumif function in a column and copied down the range,

=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!A2,'Unit-E data'!G:G)

That was fine, but when I tried to sort the range I noticed that criteria
(cell reference) changed from A2 to something totally different for the row 2.

Is there any way to get round this problem?

Hi Pete

I just realised that when I used

Replace with: ation'!$A

After sorting I get

=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A2,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A5,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A6,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A7,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A11,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A12,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A13,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A14,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A15,'Unit-E data'!G:G)

for rows 1-10, for criteria was expecting ation'!$A2, ation'!$A3, ation'!$A4
etc.

When I change the sheet reference to just the cell reference A2, A3, A4
etc., the criteria is fine after sorting.



"Pete_UK" wrote:

Once you have copied the formula down, highlight all the cells with
the formula in and CTRL-H (Find & Replace):

Find what: ation'!A
Replace with: ation'!A$
Click Replace All

Hope this helps.

Pete

On Dec 5, 8:48 am, nc wrote:
I wrote a simple sumif function in a column and copied down the range,

=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!A2,'Unit-E data'!G:G)

That was fine, but when I tried to sort the range I noticed that criteria
(cell reference) changed from A2 to something totally different for the row 2.

Is there any way to get round this problem?