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,
=COUNTIF('F2'!L6:L93,2)
=COUNTIF('F2'!L7:L94,3)
=COUNTIF('F2'!L8:L95,4)
I need this,
=COUNTIF('F2'!L5:L92,2)
=COUNTIF('F2'!L5:L92,3)
=COUNTIF('F2'!L5:L92,4)
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:
=COUNTIF(DATA,ROW()-ROW($B$4)+2),
where you replace $B$4 by one that is appropriate in your situation.
Aladin
