Using ISNA, IF, AND, OR statements

**henrock24** · 07-23-2015, 11:03 AM

Hello

I've made a formula to see if an account was added, stayed the same or has been deleted. In this case I started with a tab of only October 2014 Account, made another one for just September 2014 and another one for November 2014. Currently, I am only able to make a formula to tell me "Which accounts in this October 2015 tabs were from September and which were added since September". I want to incorporate in the below formula, which accounts from October 2015 are not in November 2014 as "Accounts Deleted" (I've already made a formula for the October accounts that are not in Nov). COuld someone please help me!?

=IF(ISNA(VLOOKUP(G2,SSBSEP2005ALL_NO_ERROR!$G$2:$G$132648,1,FALSE)),"Account Added Since Sept","Sept Accounts")

Thanks,

Hendrix

**TMS** · 07-23-2015, 11:08 AM

It would probably help to see a sample workbook. But, from what you have described, you are making a rod for your own back by having separate worksheets for each month. I would recommend that you put all your data in one worksheet and add a column for the join date.

Otherwise it will be extremely difficult to analyse your data.

Regards, TMS

**henrock24** · 07-23-2015, 11:27 AM

I've added a file for your view. In the October 2015 tab columns "AO" mentions the "Account Added Since Sept" or "Sept Accounts". I wanted to incorporate Accounts in November not in October as "Deleted Accounts". The accounts info is on Column "G" in the November 2005 tab.

Thanks,

Hendrix P

**henrock24** · 07-23-2015, 12:31 PM

Someone please help

**TMS** · 07-23-2015, 01:41 PM

Working on it ... give me time, I have other distractions

**henrock24** · 07-23-2015, 01:46 PM

Thank you!!!!!

**TMS** · 07-23-2015, 02:22 PM

See the attached updated workbook. It uses COUNTIF rather than VLOOKUP as it simplifies the formula because you don't need to return anything. That is modified to use INDIRECT and picks up the relevant sheet from the column header.

Note that the September tab has been changed from Sept 2005 to Sep 2005 for consistency. Ideally, the tab would also include the year, which would make the formula a little shorter and maybe cater for the end of year scenario.

The middle sheet has more columns and different text in the formula so it's not just a case of copying the formula across ... although it IS for September and November.

Anyway, take a look and see if it helps.

Regards, TMS

**ChemistB** · 07-23-2015, 03:11 PM

Originally Posted by TMS

Making a rod for your own back

Never heard that one, must be British. Is that like
"Between a rock and a hard place" or more like
"Painting yourself into a corner"

**TMS** · 07-23-2015, 03:21 PM

Not quite: more like causing yourself unnecessary pain.

http://dictionary.cambridge.org/dict...-your-own-back

If you make a rod for your own back, you act in a way that creates more problems for yourself in the future:
By giving in to the terrorists' demands, the government will simply be making a rod for its own back.

Possibly linked to phrases like: 'spare the rod and spoil the child' and 'rule with a rod of iron'

Regards, TMS

**henrock24** · 07-23-2015, 03:53 PM

TMS thanks so much for your help. However I really do not understand that formula and how to construct it to my task. Basically, what Im trying to do is make a final pivot table that can break down the data like this:

# of October Accounts:
# of October Accounts Deleted (Not in Nov):
# of October Accounts Added (from September):

Your formula looks good, but it is not helping with this task. I need a column to specify either 3 of the above categories. Please let me know if you can help. I've attached another sheet for your view.

Million Thanks!

**henrock24** · 07-23-2015, 04:03 PM

Thanks ChemistB, would you try to help since you are online?

I've posted something just recently..

**newdoverman** · 07-23-2015, 04:21 PM

Maybe this will work for you.
I consolidated a listing of all the accounts with their months on Sheet1 then created a Pivot Table out of the results. The Pivot Table lists the accounts in order and indicates the month(s) that they belong to. There is also a slicer that allows the selection of a month or months and all accounts for the month or months selected will be produced.

**ChemistB** · 07-23-2015, 04:41 PM

Here's my attempt at a pivot table. I used the pivot table wizard to pull data from all three sheets, just choosing Account and Lst_Name. In the table, you can see how many times the account occurs (1,2, or 3) but no easy way to determine where it occurs or doesn't occur, not without doing some lookups on each sheet.

**newdoverman** · 07-23-2015, 08:39 PM

For what it's worth, here is your workbook with each worksheet indicating if the account numbers appear on the other two worksheets. Named ranges are used for the accounts on each worksheet to make the calculation easier to write and understand.

Example formula:

Formula:

Please Login or Register  to view this content.

Using ISNA, IF, AND, OR statements

LinkBack

Thread Tools

Rate This Thread

Display

Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Re: Using ISNA, IF, AND, OR statements

Thread Information

Users Browsing this Thread

Similar Threads

IF Statement Comparing 2 ISNA Statements

Nested IF(ISNA) Statements - Multiple Sheets

Adding ISNA to long nested IF statements with vlookups

using AND with IF(ISNA...

Excel 2007 : Isna

Nested IF statements or CHOOSE statements needed for multiple conditions formula

[SOLVED] ISNA

ISNA

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions