EXCEL SUMIF with OR condition

a simple workaround for using SUMIF with an OR condition:

- SUMIF returns a single Sum value from the Sum Range which matches the criteria set under Criteria. a work around is to use OR conditions in flower brackets separated by comma. 
- in this case the formula returns the value for the first condition mentioned in the flower bracket. it is not an error. the formula returns other values too but in a array form. to capture the complete value result of the formula in a single cell you would have precede it with a SUM formula. so the final output would look like this: 

 SUM(SUMIF(Criteria Range,{Criteria1, Criteria2, .. CriteriaN}, Sum Range))


this functionality can be extended for SUMIFS formula as well!

Excel VBA Automation Error: The object invoked has disconnected from its clients

Error:
Excel VBA Automation Error: The object invoked has disconnected from its clients

Reason:
If you have come across above error, it would mainly because of 
        Selection.Insert Shift:=xlDown
statement in your vba code. 

i.e : you are trying to copy and insert the previous row which contains formulae. I know it would have been working flawlessly all these years but suddenly Excel felt like it needs have some fun playing with you. No problem, as we all know MS Excel has stretched beyond its capabilities let it have some fun. Now how to tame it..

Solution:
There are many workarounds suggested on many forums. But for me for the specific task of inserting the previous formulae filled rows down, I had to replace the Copy and xlDown code with a code to first insert as many blank rows that are required and then copy the formulae and paste over the new blank rows.

Now out of the blue why did Excel behave like this!!? Only possible reasoning could be corruption in excel sheet. Usually this error occurs in an old/used [or even abused] Excel Workbooks/Worksheets.

Hope this helps!
Have a good one! :)

Excel Context Menu Key Board Shortcut : Shift key + F10 key

most of us are used to keyboard shortcuts especially if working on MS Excel Application. most of the laptops / keyboard's have a direct shortcut key for mouse context menu. but some of the models [especially Dell Laptops] skip on this.

<Shift> + <F10> key combo activates the context menu. though not single key solution but still a respite especially for those used to keyboard.


cheers,
Som

Dependent Drop Down in Excel : Data Validation

most of us have faced the need for dependent drop downs in out Excel models/Interfaces.

there are many ways to achieve this. i am describing first the simplest of them : using Data Validation, Named Ranges and INDIRECT formula.

scenario: 
categories of vehicles - Hatchback, Sedan and SUVs.




required output:
the list of vehicles that belong to these categories must appear against the category under the drop down.





method:
to achieve this,

-> we would have to first define the Named Ranges for these vehicles[Defined on Sheet2 of the workbook]. Remember: you would have to keep the name of these Named Ranges as they are referred to in the cells.



named ranges..





-> on Sheet1, select the cell next to the heading and update the Data Validation criteria. here the formula =INDIRECT does all the magic for you





voila you are done!!!

now this is happening because you are referring to the named range which is exactly same as that of the value in the Cell "A3". now if you change the value in the cell to Sedan to Hatchback , your list will reflect only hatchback cars.

further you can define a drop down even for your categories. i.e: Sedan, Hatchback and SUV can be selected via drop down on column A.

also you can use vlookup formula in  the DataValidation to look up the required Named Range so that appropriate drop down is always available the moment you select the value on the left. coz all that is required for DataValidation is a named range to display the list.. so how you get the name of that Named Range there is limited by your imagination and of course by excel formula ;)

cheers,
Som

Latest Vertica Driver Version 6.1 Caveat : LCOPY issue

newer versions of device drivers bring along some performance enhancements giving users better experience. seldom they would require any change in database queries / code that is already there.

but in this case 'LCOPY' command has been replaced with 'COPY LOCAL'. i can't understand the reason behind it, but if you are gonna upgrade your Vertica drivers to Version 6.1 please keep this in mind.

Now for the good part:
-improved performance of UNION and UNION ALL queries
-supports SQL WITH clause in a primary SELECT statement

etc.

MS Access Crash - shared database with multiple users

i am sure most of us Access users are familiar with Access crashing/going corrupt. especially it is the UI form or the VBA code that goes bad. though we can't entirely do away with this issue we can certainly minimize it to some extent.

-one solution that is suggested and also as i have experienced is to 'Split' the database. Splitting simply refers to maintain a separate database with all the tables required and in turn linking these tables [back-end] with other database having the required UI Forms and the VBA code [front-end].

-we can further have multiple copies of front-end database for each user too.

this helps in minimizing crashes as it avoids loading of every form and code in to the memory from same location for each user.

ODBC connection string for 64 bit Win OS [OS vs App and 64 Bit Vs 32 Bit]

with Windows 7 becoming standard across, naturally there are issues cropping up everywhere after migration. people are clueless as to what would be the effect of the new system update on their processes and systems- atleast i was.

we faced issues with ODBC drivers for the database connections [for MySql and Vertica databases]. i really had to struggly to 'find' out which was the right ODBC right ODBC connection string to use. this issue was compounded because the Office 2010 does not support Jet Database Engine .

to start with, there are couple of scenarios to consider:
1. a 64 bit Windows OS with 64 bit Application [MS Office]
2.a 64 bit Windows OS with 32 bit Application [MS Office]


right now we are using the scenario 2. after much of trial and error , and searching around online [inlcuding MS Knowledg Base] i could finally get the below combination of connection string working:

	Old	New
Vertica	Vertica ODBC Driver 4.1	Vertica ODBC Driver 4.0
MySql	MySQL ODBC 5.1 Driver	MySQL ODBC 5.2w Driver or MySQL ODBC 5.2a Driver [supposed to be 'newer']

 most of this confusion can be avoided, if the system admin makes efforts to inform all the stakeholders of the correct system updates and most importantly if Microsoft can udpate its Knowledge Base with the correct and un-ambigous information about the dropping support to old technologies and alternatives offered.