Excel 2007 : 'Countif' with 'indirect' formulas needed

Hi there,

I am working on a spreadsheet in excel 2007 involving a number of copy and paste macros. They move data from one sheet onto another sheet by shifting all existing data (on the sheet the data is moving to) down one row, and then pasting the new values in. This is working well but I want to put in a simple countif formula, like so:

Please Login or Register  to view this content.

However, when the new data shifts in, the B2 becomes B3 and the B367 becomes B368 and then this carries on so it is only ever counting the first piece of data that was entered.

How can I rectify this, I have read about indirect formulas, however I cannot get these to work, and using $B$2 doesn't work either, although you're probably just thinking why would i even bother trying that anyway!!

I have a very similar problem with cumulative addition formulas in cells (the answer may be very similar). I wish to add 2 cells, (not next to each other) the values of which will change since the data is shifting down one space each time with a copy and paste macro? could anyone supply a formula for that too please.

Many thanks,
Matt

Depending on how your macro works, you could change your formula to:

Formula:

Please Login or Register  to view this content.

Then when a row is added above row 2, the array in your formula automatically expands.

Any reason not to use:

=COUNTIF(B:B,">0")

There was before, but after you posted that, i've changed some coding bits around so stuff is grabbed from different places and now I'll use b:b, thanks

Worked all the other stuff out too, thanks for your help guys