excel - Dynamic Summing Range -


currently have medical spread-sheet list of clients have serviced. have 8 different clinical categories denoted different acronyms - hv,sv,cv,wv,cc,ov,ts , gs.

a client can receive multiple therapies i.e. hv,sv,cv - in background have counter mechanism increment each of these records 1.the formula used counter is:
=(len('parent sheet'!f25)-len(substitute('parent sheet'!f25,'parent sheet'!$p$4,"")))/len('parent sheet'!$p$4)

snapshot of spreadsheet

at bottom of sheet have sum ads treatments occurred week.
summed totals sample

now tricky part have year's worth of data in sheet summing formulas set as: sum(column 6: column 53) due need increase entries beyond limit, have adjust sum formula. have 300 sum formulas adding each of 8 criteria items , assigning them hv,sv,sc,wv etc. counters.

would have adjust manually 1 one or there easier way of doing this? snapshot of counterenter image description here

thank much!

to me, think should change sheet layout little, create user defined function (udf) , alter formulas in sum rows efficient row/column adding (to make use of excel's formula fill). issue need save macro-enabled file.

what need change in formulas utilize $ restrict changes in column , rows when formula fill takes place.

to illustrate in example, consider:
data
assuming first data starts @ row 6, , no more row 15 (you can use idea of data gap on top). alter sum row titles begin abbreviation create udf below:

option explicit  ' orngtype refers cell abbreviation stored ' orngcount refers cells abbreviation counted ' "hv" stored in $c16, , cells count hv d$6:d$15, ' sum of hv date (d16) calculated formula ' `=counttype($c16, d$6:d$15)`  function counttype(byref orngtype range, byref orngcount) long     dim orngval variant, oval variant, otmp variant, slookfor string, count long     slookfor = left(orngtype.value, 2)     orngval = orngcount.value ' load values onto memory     count = 0     each oval in orngval         if not isempty(oval)             each otmp in split(oval, ",")                 if instr(1, otmp, slookfor, vbtextcompare) > 0 count = count + 1             next         end if     next     counttype = count end function

formulas in sheet:
columns sum fixed rows 6 15 , type lookup fixed column c
d16 | =counttype($c16,d$6:d$15)
d17 | =counttype($c17,d$6:d$15)
...
e16 | =counttype($c16,e$6:e$15)
e17 | =counttype($c17,e$6:e$15)

the way created udf lookup , count appearances of cell value (first argument) within range of cells (second argument). can use count type of treatment big range of cells (column g).

now if add many columns after f, need use autofill , appropriate rows , columns there.
autofill

you can create vba sub add rows , columns , formulas you, that's different question.


Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more