PC Review


Reply
Thread Tools Rate Thread

Countif with indirect formulas needed

 
 
New Member
Join Date: Jul 2012
Posts: 1
 
      16th Jul 2012
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:

Code:
=COUNTIF(B2:B367,">0")
However, when the new data shits 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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4) Dave F Microsoft Excel Misc 3 20th Sep 2007 08:36 PM
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions =?Utf-8?B?TWlrZSBCYXJsb3c=?= Microsoft Excel Worksheet Functions 6 6th Jul 2005 03:04 AM
Wanted - help with generating INDIRECT("A1") from INDIRECT (A1) Dan E Microsoft Excel Discussion 2 6th Apr 2005 11:18 PM
Help using INDIRECT / INDIRECT.EXT with paths created by FUNCTIONS =?Utf-8?B?UGhpbGlw?= Microsoft Excel Worksheet Functions 4 18th Aug 2004 12:15 AM
indirect INDIRECT boris Microsoft Excel Misc 3 6th Aug 2003 09:20 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:18 PM.