excel - Using INDIRECT to sum cells in another spreadsheet -

-

in worksheet titled “data!” have following successful formula: 

=sum(indirect(char(64+match(e3,a3:n3,0))&match($b165,$a$1:$a$900,0)&":"&(char(64+match(e3,a3:n3,0))&match($b165,$a$1:$a$900,0)+$o$2-1)))

please don't bother details, problem related referencing spreadsheet, explained below. in sum, above formula works intended summing cells e124 e133. in cell e3 have desired column name. in b165 have desired year.

i need formula in different worksheet. so, in second worksheet i’ve written:

=sum(indirect("data!"&(char(64+match(data!e3,data!a3:n3,0))&match($o3,data!a1:a900,0)&":"&"data!"&(char(64+match(data!e3,data!a3:n3,0)&match($o$3,data!a1:a900,0)))))

however, brings me #value error.

when break formula 2 parts works.

that is, works: char(64+match(data!e3,data!a3:n3,0))&match($o3,data!a1:a900,0)

and works: (char(64+match(data!e3,data!a3:n3,0)&match($o$3,data!a1:a900,0)))

but when combine 2 breaks down. suppose error has parts bolded below reference initial worksheet.

=sum(indirect(**"data!"**&(char(64+match(data!e3,data!a3:n3,0))&match($o3,data!a1:a900,0)&":"**&"data!"**&(char(64+match(data!e3,data!a3:n3,0)&match($o$3,data!a1:a900,0))))))

any guidance appreciated!!

updated
updated formula, , works perfect now.
here corrected version of formula: 

=sum(indirect("data!"&(char(64+match(data!e3,data!a3:n3,0))&match($g3,data!a1:a900,0)&":"&char(64+match(data!e3,data!a3:n3,0))&(match($g$3,data!a1:a900,0)+data!$o$2-1))))

i think gonna work.


Comments

Post a Comment

Popular posts from this blog

javascript - How to synchronize the Three.js and HTML/SVG coordinate systems (especially w.r.t. the y-axis)? -

-
i'm combining 3d content three.js html , svg content. three.js cssloader pretty job synchronizing placement of html , svg content in 3d world. but svg/html coordinate systems 'left-handed', whereas three.js coordinate system 'right-handed'. means y-axes reversed. in svg/html, y / top goes go down screen, , three.js uses more standard mathematical convention of y going down go down screen. i have continually convert 1 other, pretty error prone. know not first run (for example, look here ). has come general solution? here's tried: do in object3d .scale.y = -1 . may suspect, turns out disaster. turns inside-out, , don't try put camera in there. do in object3d .rotate.x = math.pi . more promising, z axis no longer consistent html concept of z-index. still, i'm using now. in html, don't use top , use bottom . in svg , inside <g transform="scale(1, -1)"> inside <g transform="translate(0, imageheight)"> . h...
Read more

javascript - How do I find how many occurences are there of a highlighted string, and which occurence is it? -

-
as title says, have website text inside div, there way highlight part of , know occurence is? if text has 4 occurences of word "something", , highlight 3rd, how information using javascript/jquery? http://jsfiddle.net/g09g35xa/6/ here's method seems work. highlight string want , when click button alert index of highlighted word in div. markup: <div id="thediv"> <p>the quick brown quick fox jumps on lazy dog</p> </div> <br /> <input id="findindex" type="button" value="find index of highlighted string" /> and js: function getselectedhtml() { try { if (window.activexobject) { var c = document.selection.createrange(); return c.htmltext; } return getselection().getrangeat(0).tostring(); } catch (e) { if (window.activexobject) { return document.selection.createrange(); } else { return ...
Read more

java - Reading data from multiple zip files and combining them to one -

-
i want read data lets 4 zip files called zip1, zip2, zip3, zip4. of these zip files split 1 big zip file called "bigzip". want combine zip files 1 , compare bytes if 1 bigzip file matches size of bytes combined zip file of (zip1+zip2+zip3+zip4). getting small file size when combine size of 4 zip files. doing wrong? here code same: targetfilepath1, targetfilepath2, targetfilepath3, targetfilepath4 belongs path of 4 zip files. sourcefilepath path bigzip file class test { public static void main(string args[]) { zipoutputstream outstream = new zipoutputstream(new fileoutputstream(sourcebigzip)); readzip(sourcefilepath, targetfilepath1); readzip(sourcefilepath, targetfilepath2); readzip(sourcefilepath, targetfilepath3); readzip(sourcefilepath, targetfilepath4); outstream.close(); } static void readzip(string sourcebigzip, string targetfile) throws exception { zipinputstream instream = new zipinputstream(new file...
Read more