Search results

My conditional formatting (CF) is used on a dataset that's fluid - rows being added/deleted. I've created a named range using OFFSET to define the dataset dynamically, but after I use it in the "applies to" area of the CF rule, when I come back in it's been resolved to a static range (which...

Haseeb, thank you for your varied approaches to the challenge. I particularly like the SUMIFS solution. I'm a big proponent of simplifying wherever possible and this is elegant. I'm pondering here, but it would interesting to understand why the behavior of the SUMIF, SUMIFS, COUNTIFS, etc...

Faseeh, nice solution. I'm hoping I can do it without processing as an array. Your solution does arrive at the correct value. Haseeb, I love where you're going but I need to sum the values that are numeric and qualify. Your formula denotes which value(s) qualify.

My range has mostly numeric values, but also includes some imputed errors such as #N/A and #DIV/0!. I'd like to be able to use SUMPRODUCT to total up only those non-error cells in the range that qualify vs. a numeric condition. For example: Range A1:A6 equals: 4 5 #N/A 7 10 1 Range B1:B6...

Narayan, I appreciate your patience. I'm not giving you enough information, so here it is in the attached file. The range A1:A4 contains "Values", 1, 2, 3 respectively. The rand B1: B4 contains "Sum", =Sum(Test_Hard), =Sum(Test_Offset_Hard), and =Sum(Test_Offset_Dynamic) respectively. I have...

Narayan and SirJB7, thank you both. I'm very interested in WHY the INDIRECT fails when referring to a dynamic named ranges. Perhaps it has something to with the fact that multiple volatile functions are in play. The macro from SirJB7 fixed my issue.

Besides the chance that I'm doing something wrong, is there a structural reason why INDIRECT doesn't work when the Named Range is defined using Offset? Named Range CHANDOO = offset('Sheet1'!A1,0,0,3,1) Cells A1 though A3 contain 1, 2, 3 respectively. =SUM(Chandoo) = 6...

Narayan, once again you're dead on perfect! Thank you.

I have two arrays and I'm subtracting one from the other, then testing each difference vs. a constant - =SUMPRODUCT(--(((B5:J5)-(B4:J4)))=0). When I step through the formula it's evaluating to the appropriate number of Trues/Falses, but it's not yielding the sum of the Trues, it's coming back 0...

Debraj, thank you. That's a perfect solution.

Is there a way when you double click the fill handle to have it NOT increment date values?

Thanks to both of you for responding. My question may not have been clear, so I'll rephrase: Aside from whether or not INDIRECT is necessary in the OFFSET example I gave, why does Excel this named ranged when it's the underlying element of a Chart Series? Remove the INDIRECT with a direct cell...

I'm attempting to use WorkbookName.xlsx!NamedRangeName to define a Series for a Dynamic chart and it's failing. Is it because of the INDIRECT statement embedded within the OFFSET statement? It works fine if I remove the INDIRECT and point directly to a cell reference. I'm thinking I'm making...

SirJB7, he said he wanted only those ending in "?", correct? The ~? captures the ?a value as well. Cheers!

*~? - finds all 6 rows ENDING in ?

Sorry about that. Here you go.

I'm trying to do something I've done many, many times before - use dynamic named ranges to "automate" a chart. Both the two series, New and Total, as well as the X-Axis labels shift depending on two user inputs - a date value and a row position derived from a MATCH using the Date Value. This...

Thanks for the reply, SirJB7. This solution gets me what I needed. SOLVED Function CellAddress() As String CellAddress = Application.Caller.Address(0,0) End Function

Note: I had posted this incorrectly to the general Excel forum initially. Hopefully, this is an easy one. I have a Function that needs to know the Row & Column in which the Function is being executed. If I use the Function many times on the same worksheet, it's returning the same value in every...

Hopefully, this is an easy one. I have a Function that needs to know the Row & Column in which the Function is being executed. If I use the Function many times on the same worksheet, it's returning the same value in every cell since I'm, incorrectly, pointing to Active.Row and Active.Column...

Thank you

I carried this over from the "old" site: Original Post: I want to test multiple cells vs. a constant. Rather than doing =OR(A1<>99,f1<>99,g1<>99) is there a context for testing something like =99<>or(a1,f1,g1) (this obviously doesn't work)? Thanks in advance NARAYANK991 answered: Try...

Colin, that works perfectly. Thank you.

Debraj, I'm very familiar with the technique, but hadn't thought of using it for this exercise. Once I take the string from Word, however, even though I've set the entire worksheet to TEXT, Excel puts on its "smarty pants" hat and converts the text numbers to numbers hence losing the leading...

Thanks to both of you. My dilemma is that rather than one delimiter (|), I have 100 and to step through 100 column text options is somewhat laborious - if that's the only solution, so be it. I'm hoping there's a general override. Example...