inserting columns withOUT changing formula 1

[MrDougJ]

Hi, If anyone can help my problem, I'd appreciate:
I have a spreadsheet where I insert a new row at the top of the spreadsheet every day. I then have another sheet which anaylses the data from the 1st sheet, counting how many of the data entries fit certain criteria.
i.e. =Sumproduct(('sheet 1'!$B$3:$B$10000=A3)*('sheet 1'!$C$3:$C$10000="Large"),('sheet 1'!$T$3:$T$10000))

The trouble is, if I insert a new row, the formula changes to
=Sumproduct(('sheet 1'!$B$4:$B$10001=A3)*('sheet 1'!$C$4:$C$10001="Large"),('sheet 1'!$T$4:$T$10001))

I know this is how excel usually behaves so that changing cell locations will automatically change anything it affects, but is there a way that I can stop this for this page alone?

Thanks

 

[AnotherHiggins]

Best way:

Don't insert a row. Add a at the bottom then sort to get things in the proper order.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

The only other way is to use INDIRECT to refer to all your ranges:

=Sumproduct((INDIRECT("'sheet 1'!$B$3:$B$10000")=A3)*(INDIRECT("'sheet 1'!$C$3:$C$10000")="Large"),(INDIRECT("'sheet 1'!$T$3:$T$10000")))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

....but I should add that this will add a calc overhead to your formulae and if you're using a lot of sumproducts, this may not be the best idea!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

<fortune cookie>

Excel is a river. It is far easier to move with the river rather than against it.

</fortune cookie>


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

like it John!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Option 1:
Change your formula to include anchor cells that do not change when you insert a row eg !$B$[red]2[/red]:$B$10000

If row 2 contains blanks or column headings then most formulae will cope fine.

Option 2:
If the sheet that refers to your data is in another workbook then close that workbook as you add the rows.


Gavin