Posts by DC Mega

  • Sum Area Based on Compound Row and Column Criteria

    Quote from FlameRetired

    Sorry, I have been gone a while, and had lost sight of this thread. My bad. :(
    It sounds like the condition you speak of is IFERROR(long formula,""). That would trigger errors. Try IFERROR(long formula,[SIZE=14px]0[/SIZE])


    Flame, better late than never. I appreciate the follow-up. I did eventually figure out that I either needed to use zeros instead of spaces or use a double unary to ensure I was returning a numeric value and not "True/False" in some of the nested function calls to other formulas. It is actually opportune that you made your presence known, granted I saw this a few months later.


    I have been successfully using your code for months, tailoring it as required to meet the needs of various situation. But yesterday and today I have been beating my head against a wall trying to get it to work. I keep dropping it and coming back to it, and maybe it's the 28 hours with no sleep, but I just can't seem to get it right. Today of all days I could really use some expertise.


    I have some columns labeled as periods (YYYYMM) but no matter what I do, I cannot get this methodology to register a result and return the desired values. It just returns nothing. I have tried formatting it as Text, Numeric, and Custom Date formats; I've tried regular formulas and array formulas, but nothing seems to work. See Summary worksheet starting at row 15 and the Data worksheet starting at column CJ. Any suggestions?

  • Sum Area Based on Compound Row and Column Criteria


    It required some reverse engineering but I have adapted your code to work! Thank you so much.


    The only dilemma is trying to conditionally exclude variables. I tried using an IFERROR(IF((blah blah)) around the constituent parts of the SUMPRODUCT but it does not work. I suspect this may because some of my data resolves to blanks or spaces, which seem to resolve to #VALUE errors and trigger the IFERROR encapsulating the entire formula.


    Any thoughts about how to work around this? I'm trying to just clean the source data but it in turn is entirely formulas referencing other sheets and is proving to be quite an undertaking.

  • Sum Area Based on Compound Row and Column Criteria

    Quote from NBVC

    Hmm. I see an Edit on your first post... but that could be because I have Moderator priviliges...


    Just reply to this post and add an attachment (paper w/clip icon at top right of reply box)


    That I can do. So I made a few updates in the sample workbook. I want to emphasize that all the formulas I have created and need to create will be in the SUMMARY tab. Here's what my original post would look like had I been able to edit it:



    My question is this: Is it possible to add the values in a large dataset based on multiple row and multiple column criteria? Does anyone dare face the maw of madness and explain how to have 3 or 4 criteria (2 rows and 2 columns) in a single formula? Here's a brief overview of the situation. All my formulas are on the "summary" worksheet.


    Variables: [Core.xlsm]Parameters!$A$1:$O$30 <----(This sheet was cleaned for the sample and is not used)
    Dataset: [Core.xlsm]Data!$A$1:$BD$100 <----(Source data my formulas reference, this is a minuscule sample population, I don't expect all criteria to produce matches)
    Output: [Reports.xlsx]Summary!$A$1:$BD$13 <----(Active worksheet for this request, all variables are pulled from the column and row headings)


    Criteria 1: [Reports.xlsx]Summary!C$2 <----(Based on the column header value)
    Criteria 2: =RIGHT(C$1,LEN(C$1)-FIND(" ",C$1)) <----(This extracts the number from the title "Phase #")
    Criteria 3: [Reports.xlsx]Summary!$A6 <----(based on the primary row header value "Project Name")
    Criteria 4: [Reports.xlsx]Summary!$B6 <----(based on the secondary row header value "Region")
    Criteria 5: [Reports.xlsx]Summary!$B1 <----(based on the "Key" value applied by the user, if they wish to provide one)



    Result:


    I can use the SUMIFS function to return matches on any multiples row criteria OR multiple column criteria I wish to include scuh as this formula in Summary!C6:


    Code
    =SUMIFS(Data!$R$2:$R$100,Data!$BD$2:$BD$100,C$2,Data!$AT$2:$AT$100,RIGHT(C$1,LEN(C$1)-FIND(" ",C$1)))


    I can process any combination of one row and one column criteria to return a conditional sum on a single column of values using this formula in Summary!C3:

    Code
    {=SUMPRODUCT((Data!$BD$2:$BD$100=C$2)*(Data!$J$1:$AR$1=$A3),(Data!$J$2:$AR$100))}


    But this breaks down and returns an #N/A error when I try to add in a third criteria, let alone a fourth or fifth (prospective formula for C4 if it worked):

    Code
    {=SUMPRODUCT((Data!$BD$2:$BD$100=B$4)*(Data!$A$1:$AR$1=A$4)*(Data!$O$2:$O$100=RIGHT(C1,LEN(C1)-FIND(" ",C1))),(Data!$J$2:$AR$100))}

    I have attached a sanitized sample workbook that is 100 records (from a source set of over 5000). All candidate formulas should appear in Summary!C3:C13 so they can be copied into the columns to the right.

  • Sum Area Based on Compound Row and Column Criteria

    Quote from NBVC

    It would help us tremendously if your sample formulas would match the actual data you posted. Your ranges are of different sizes and don't seem to reference the same data that is your workbook.


    My apologies. I assure you I updated them inside the SUMMARY sheet in the sample workbook, but I forgot to update them in the code blocks in my post. I'm trying to figure out how to edit my original post to fix them, but so far no luck.

  • Sum Area Based on Compound Row and Column Criteria

    My question is this: Is it possible to add the values in a large dataset based on multiple row and multiple column criteria? Does anyone dare face the maw of madness and explain how to have 3 or 4 criteria (1-2 rows and 2 columns) in a single formula? I am confident it can be done, but I have been at it for hours and I hit a wall. Any working sample would be welcome. I've not had any luck finding something quite like this online. Here's a brief overview of the situation. All my formulas are on the "output" workbook listed below. [INDENT]
    Variables: [Core.xlsm]Parameters!$A$1:$Z$100
    Dataset: [Core.xlsm]Data!$A$16:$AR$5320
    Output: [Reports.xlsx]Summary!$A$20:$L$50


    Row Criteria 1: [Reports.xlsx]Summary!B$22
    Row Criteria 2: =RIGHT(B$21,LEN(B21)-FIND(" ",B21))


    Column Criteria 1: [Reports.xlsx]Summary!$A23
    Column Criteria 2: <User Selected Reference or Value>[/INDENT]

    Result:


    I can use the SUMIFS function to return matches on any multiples row criteria OR multiple column criteria I wish to include:


    Code
    =SUMIFS([Core.xlsm]Data!$J$16:$K$5320,[Core.xlsm]Data!$BD$16:$BD$5320,D$22,[Core.xlsm]Data!$O$16:$O$5320,RIGHT(B$21,LEN(B21)-FIND(" ",B21)))


    I can process any combination of one row and one column criteria to return a conditional sum on a single column of values using this formula:


    Code
    {=SUMPRODUCT(([Core.xlsm]Data!$BD$16:$BD$5320=B$22)*([Core.xlsm]Data!$J$15:$AR$15=$A23),([Core.xlsm]Data!$J$16:$AR$5320))}


    But this breaks down and returns an #N/A error when I try to add in a third criteria, let alone a fourth:


    Code
    {=SUMPRODUCT(([Core.xlsm]Data!$BD$16:$BD$5320=B$22)*([Core.xlsm]Data!$J$15:$AR$15=$A23)*([Core.xlsm]Data!$O$15:$O$5320=RIGHT(B21,LEN(B21)-FIND(" ",B21))),([Core.xlsm]Data!$J$16:$AR$5320))}


    My dataset has unique column names but the data itself is mostly calculations based on Indirect and Index Match references to the detail worksheets where the values of each line item in the dataset sheet are calculated based on their unique values combined with the parameters from the variables sheet. There are thousands of sheets, one for each row in the dataset. Yes the workbook is huge. Yes I'd rather use SQL, but it is not an option for this project.


    I have attached a sample workbook but in the process of whittling it down to a small enough dataset, I realize that the formulas I listed here may not match exactly anymore. Thanks for perusing my dilemma.

  • Expand & Collapse Images Via Single Click

    Re: Expand &amp; Collapse Images Via Single Click


    That's the tragedy of suddenly losing a job after working there for a decade. I took my personal code libraries in when I started, and expanded them for almost a decade, but the whole shutdown occurred in a few hours and I wasn't able to take my improved VBA or SQL code libraries with me. I feel like a huge piece of my skill set and development life died that day.


    All I can remember, is that I triggered an event when the image was clicked, where the clicked image became the selection, and then I used the selection method check the ScaleHeight of the selection, and toggle it between 0.2 and 1.0 based on the ScaleHeight.Value (which isn't a valid property, but it was something like that). Recording a macro is the closest I can come to recreating it, but it only works one way.

    Code
    Selection.ShapeRange.ScaleHeight 0.2, msoFalse, msoScaleFromTopLeft


    Once I had the design down, I ever after referenced it as ImageZoom() and pretty much forgot exactly how the code was laid out.

  • Expand & Collapse Images Via Single Click

    Re: Expand &amp; Collapse Images Via Single Click


    Quote from royUK;705547

    Are the images on sheet1?


    Try ActiveSheet instead.


    Thanks Roy, that did the trick and will work for now. I appreciate the code and your guidance.


    At some point I hope to rediscover how to set it up so I don't have to click all the images first. I will share the code on this thread if I get it figured out.

  • Expand & Collapse Images Via Single Click

    Re: Expand &amp; Collapse Images Via Single Click


    Quote

    I have assigned the macro to 4 images and the code works fine.


    Roy, I get the following error message when I try to run this code.


    Runtime Error '9'. Subscript out of range.

    [ATTACH=CONFIG]59397[/ATTACH]


    It hits the brakes exactly where I anticipated, though I'm not sure why, if it isn't the fact I mentioned in my previous post that I am often working with atypical sheet names. In the workbook I tested this in, the VBA property (Name) for these sheets are all changed to what the sheet is called on the tab in the workbook. In another book I tested it on, I had the same problem, all the sheets (Name) property is simply a numerical designation. It isn't practical to go rename the sheets manually, as some of the workbooks have hundreds of sheets, each containing a image or two.

  • Expand & Collapse Images Via Single Click

    Re: Expand &amp; Collapse Images Via Single Click


    Quote from royUK;705457

    This works. I can't think of a simpler way


    Thanks Roy,


    I do know that I will have to find a workaround for the specific sheet reference, since my workbook sheet names are not standard, and some workbooks have multiple sheets. I'm also thinking a toggle between 20% and 100% image zoom would remove the need to identify each image first, since the onclick event could just check for the current state prior to triggering.


    I will give it a try with your code and report back.

  • Expand & Collapse Images Via Single Click

    Re: Expand &amp; Collapse Images Via Single Click


    Quote from iwrk4dedpr;705388

    Where's the recorded code? What version of excel are you using now? How were the pics imported? Copy/paste, import ??


    Hello iwrk4dedpr, Thank you for responding. The Macro in question produced a single line of code. This works fine for a one-way transition by itself, but when I try to use it in a CASE or IF statement, as in my original post, it fails.


    Code
    Sub Picture4_Click()


Selection.ShapeRange.ScaleHeight 1, msoFalse, msoScaleFromTopLeft


End Sub


    The pictures were a mix of copy/paste, import, and pre-loaded when the spreadsheets arrived. Does that effect the solution, because, as I said, I do not want a macro that has anything to do with importing pictures. I just want to manipulate ones that are already in the spreadsheets.

  • Expand & Collapse Images Via Single Click

    I simply want to create some sort of onclick event that changes the size of the clicked-on image based on it's current state. If the image is at less than 100% I want to store its current size, and then expand it out to 100%. If it is clicked on a second time, I want it to reduce back to the original state. Although if saving the original size is complicated, I would settle for toggling back down to 20%.

    I need to be able to apply this to any image I have in the spreadsheet. In Excel 2003 I was able to figure out how to make this happen. The users liked how they could watch the images grow and shrink when they clicked on them. But now I can't get it to work at all in Excel 2010. So I attempted to start over by recording a macro and then fleshing out the logic based on that, however the method I am attempting to use does not work.


    Code
    Sub ZoomImage()
' Zooms an image to 100% if it's not already at that size, or reduces it to 20% if it is at 100%
If Selection.Shapes.msoPicture.ScaleHeight = 1 Then    
Selection.ShapeRange.ScaleHeight 0.2, msoFalse, msoScaleFromTopLeft
Else    
Selection.ShapeRange.ScaleHeight 1, msoFalse, msoScaleFromTopLeft
End If
End Sub


    The only two references to ScaleHeight I can find in the reference library are for the Shape and ShapeRange classes. Because so many of the search results returned questions about user-added images, I want to clarify that no new images will be added by the user in this workbook. Thank you.