Search results

Thanks Narayan! The worksheet_selectionchange approach works. It's weird this can't be handled from the sheet.protect command. Thanks again

Hi everyone! I'm facing quite a challenge on this one. I have a table on a worksheet that needs to stay locked (users can't modifiy the cells) but still users should be able to filter and sort that table. I have tried everything and saw forum solutions but none work. Here's my best shot so...

Ha! That's what's so wonderful about this forum. Thanks again for sharing your wisdom, they both work and I understand now :)

Hi everyone, I'm trying to enter a long array formula through VBA, by using the method posted here I'm struggling though, can't find a way to make it work. If there's another way around it, I'm completely open. Code is: Sub Array_Formula() Dim FormulaPart1 As String Dim FormulaPart2 As...

Thanks Debraj and Somendra! And i'll look into DCOUNTA, I've never used database functions. Thanks ThrottleWorks, you are right but I need a formula approach in this case.

Sure! Please check now

Hi everyone! Question: I need to count with several criteria and ranges, only that one of those ranges can include multiple criteria. For example, in column A:A, the criteria "Jan", "Feb" and "March", and then continue with the rest of ranges and criterias (for example, B:B, "Car models", C:C...

That's actually very clever!! I knew I wasn't thinking it right. Thank you Narayank once again!

Hi forum Can anyone think of a non-array approach to this formula, considering there are a lot of criteria and the ranges are thousands of rows long. Thank you!

That's great Haseeb let me try that. It shouldn't be slower in calculating than countifs right? Because there's over 20K rows of data. Thanks

Yes, that works, but in my actual file there will be like 10, 12 conditional criteria sets, and taking this approach would end up in a huge formula. I was just wondering if there's a simple approach, which I've found it to always be the case in Excel ;)

Thanks Hui and Narayank, actually I think it's something different. I'm uploading a sample file. You'll see there's a column for Car model, one for the State and a third one for the Income. And on cell E3 there's a Yes/No field that I'd want it to condition wether the COUNTIFS will consider...

Hi everyone! This is probably an easy one but I just can't figure it out. In a countifs formula, I want it to skip a certain set of range and criteria- let's say the 2nd and 3rd- if a certain condition is met. For example, something like...

I did it works perfectly, I'm going over the code since I'm unfamiliar with the dictionary approach. Thanks Hui for that reference as well. Thank you!

I remember I had seen a similar approach that took care of all the "unnecessary" phrases (such as those ending in "the", "of", "I", "in", etc). Applying a procedure that'd take care of that seems like the logical step, I'll get to that.

It's really cool! I love the multi-word phrases, I'll definitely include this in my analysis. Sorry for the late response Marc!

OK I think I got it! Wasn't thinking it through (happening more and more often these days). It's: (IF(criteria2="All",1,(range2=criteria2))) -- so that it avoids the whole range directly. Next goal: making it run faster!

Thanks Narayan, not sure I got it though. What could equal to "All" is the criteria "red", therefore in that scenario the formula should omit that criteria from the range. It's what in a COUNTIFS I would do like this: =COUNTIFS(range1,if(criteria1="All","*?",criteria1) Does it make sense?

Thanks Hui!! I'll test it and let you know.

Hi again! Went back to using this formula and I'm struggling to apply a "if cell is equal to "All" avoid that column" condition. Array Formula from Sajan is: =SUM((MMULT(N(range1="car"), TRANSPOSE(COLUMN(range1)^0))>=1)*(range2="red")) So I want to add a logical test in the "red" criteria...

Hi everyone, I've been dealing with this one for a while now, hoping a more experienced vba user found a solution/workaround of some sort. When using activex listbox (inserted on sheet, not userform), it happens that when another user opens the file the listbox starts behaving funny. It...

@Narayank: you're right the formula you pointed works perfectly! Now I just need to understand why. Thanks!

I'll try this approach and comment soon, thank you!!

I'll check Marc, didn't notice that one I'll go over it!

Hi all! Can anyone point me in the right direction to writing a UDF for a MATCH capable of matching cells with over 256 characters? Never written a udf before. Thank you!