excel - Trying to create a range from a set of values -

-

so have range of cells contains list of cell addresses.

enter image description here

column b & c show block of information starts , ends. column d states whether start of combination of tables, end of combination, or whether it's single table.

so having difficulty combining answers b & c form combined range. in picture, column e shows start a170 , end a596. (i don't think necessary tbh) need make range containing a170:a543, a548:a554, etc. , needs dynamic. these should create based on values in column d. i'm looking in vba, if it's easier in excel formulas, that's okay too.

can give me hints how accomplish this? brain mush.

you can create small user defined function (aka udf) stitch non-contiguous cell ranges textual representations of respective addresses. can return range native worksheet function uses cell range sum function or counta function (to use 2 simple examples).

function makenoncontiguousrange(startrngs range)     dim rng range, runion range      each rng in startrngs         if runion nothing             set runion = range(rng.value2, rng.offset(0, 1).value2)         else             set runion = union(runion, range(rng.value2, rng.offset(0, 1).value2))         end if     next rng      'debug.print runion.address     set makenoncontiguousrange = runion end function

the function used on worksheet like,

=sum(makenoncontiguousrange(b2:b4))

in data sample writing,

=sum($a$170:$a$543,$a$548:$a$554,$a$558:$a$566)

note passing in start of range in column b , gaining end range .offset. if need expand functionality pass in end range need check if both start , end ranges same size.


Comments

Post a Comment

Popular posts from this blog

qt - Using float or double for own QML classes -

-
i've created components , helper methods using c++ , qt used in qml gui. of methods calculate gui elements (size, transformations, scene graph items creation). target arm 32 bit using opengl / eglfs render output. opengl using float , qml using real (defined double ). what should use in c++ do not lose precision? if use qreal ( double ) shouldn't lose precision. if git grep float in qtbase.git , can find out bit more: dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0-* important behavior changes * dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0- dist/changes-5.2.0- - qt compiled qreal typedef'ed double on dist/changes-5.2.0: platforms. qreal float on arm chipsets before. guarantees more dist/changes-5.2.0- consistent behavior between platforms qt supports, binary dist/chan...
Read more

Create Outlook appointment via C# .Net -

-
i need create outlook appointment using microsoft.office.interop.outlook , while can work locally on own workstation , works if run web application via server's browser, not work when connect server externally. hence, think might permission issue. i changed application pool identity " localsystem " don't access denied error. unfortunately, doesn't work. the app behaves if appointment created, appointment doesn't appear in outlook. here include file on top of aspx.cs page using outlook = microsoft.office.interop.outlook; here code using pop appointment. outlook.application apptapp = new outlook.application(); outlook.appointmentitem appt = apptapp.createitem(outlook.olitemtype.olappointmentitem) outlook.appointmentitem; appt.subject = txtfirstname.text + " " + txtlastname.text; appt.body = txtcomment.text; appt.alldayevent = false; appt.start = datetime.parse(txtreminderdate.text + " 8:00 am"); appt.end = datetime.p...
Read more

ios - Swift Array Resetting Itself -

-
i have 2 arrays: pointhistory , datehistory . have nstimer , , when timer runs out, action called. in action appending int pointhistory , , current date string datehistory . here timer action: func timeraction() { let steppervalue = int(stepper.value) employee.numberofpoints = steppervalue networking.saveemployee(employee, completion: { (error) -> void in if error != nil { let alert = uialertcontroller(title: "error", message: "there error updating \(self.employee.name)'s points", preferredstyle: .alert) let alertaction = uialertaction(title: "ok", style: .cancel, handler: nil) alert.addaction(alertaction) self.presentviewcontroller(alert, animated: true, completion: nil) } }) var dateformatter = nsdateformatter() dateformatter.dateformat = "mm-dd-yyyy" dateformatter.timezone = nstimezone.localtimezone() var date = nsdate() var ...
Read more