MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to update multi cells

Posted by Squirrel on April 15, 2001 1:58 AM

Hey Gang,
Rookie Question, I need to insert a "countif" formula that refrences same cells but diffrent no#, excell changes the cells when you drag it like this,
I need this,
I do not mind changing the ref# ie: 2,3,4

Posted by Aladin Akyurek on April 15, 2001 2:13 AM

Activate Insert|Name|Define, enter a meaningful name (e.g., DATA) for Names in workbook, and enter =F2!$L$5:$L$92 as source. You can now simply rewrite your COUNTIF formula as:

=COUNTIF(DATA,n), where n is one of {2,3,4}

You can even 'variablize' n if you so desire. Assuming that your first COUNTIF is in B4, then you can change the above formula to:


where you replace $B$4 by one that is appropriate in your situation.