## Tuesday, September 11, 2012

### InfoPath Shrinking List Filter

To allow for “Shrinking” Values within an InfoPath Combo Box, use the following “Expression”.  The selected value will only be allowed to be used once in the repeating table.

not(. = xdXDocument:get-DOM()//my:FieldName)

## Friday, March 23, 2012

### Excel Cell Search & Calc

Thanks to my buddy Ron for this challenge...

The Excel Formula below is handy when you want to search for a specific value contained within an adjacent cell and then do a calculation of time that rounds up to the nearest quarter hour.  This formula includes error trapping if the searchable cell contains a null value.

### Excel "In Between"

This Excel example will show you how to determine if a cell value is "In Between" a pre-established range. Please see the details below for setup steps and a visual example...

Example Assumptions:

• The spreadsheet has been named: Range Example
• This cell value that you wish to calculate is: A8
• The 'in between' values to check against are placed in cells: B4:L4
Setup Steps:

1. Create your "in between" values somewhere on the spreadsheet (Cells B4:L4 are used in this example)
2. Create a "Named Range". In this example, the Named Range has been called: "SPREAD".
3. In the "Refers to:" field for "SPREAD", enter the following reference: ='Range Example'!\$B\$4:\$L\$4
4. Click 'OK' to save the Named Range.
5. Enter the formula below into a cell that's (preferably) adjacent to the cell that you wish to find the range for:

The formula above uses the "SPREAD" Named Range, which you defined earlier, to determine if the referenced cell is 'in between' the pre-established range.

For a visual representation of how to set this up, please see the screen shot below... ## Monday, March 12, 2012

### Dynamic Range Assignment

Dynamic Referencing for Excel Pivot Table(s) and / or Graph(s)

BC, this one's for you... :-)

Situation: You have an Excel solution that auto generates Graphs and / or Charts based on a specific set of data that changes over time. Instead of manually re-selecting a new Data Source for your Pivot Table(s) and / or Graph(s) each time your Raw Data changes (i.e. appending Q2 data to Q1, etc.), check out the information below for a dynamic solution...

• Excel Sheet Name = "DataTab"
• Column Headers Accross the top beginning with A1
• Subsequent Data lies beneath each Header
• Assuming your data is standardized and consistent over time
• Create a Named Range called "DataRange" and paste in the following formula...

=OFFSET('DataTab'!\$A\$1,0,0, COUNTA('DataTab'!\$A:\$A), COUNTA('DataTab'!\$1:\$1))

• Assign the above Named Range ("DataRange") to your Pivot Table and/or Graph as it's Data Source

• Refresh your Table(s) / Graph(s) - Can be done manually, via Table Settings, or with VBA

When you paste in future data that may be different from your original data, your Graph(s) and / or Chart(s) will now be based on a dynamically growing or shrinking dataset that matches your "evolved" Data Source.

## Friday, March 09, 2012

### Compare & Consolidate

Excel Value Comparison & Result Consolidation

If This ~ Or That ~ Then This ~ Else This
With raw data in cell(s) A2:xx, place the following formula in cell(s) B2:xx

=IF(OR(ISNUMBER(SEARCH("*Item1*",A2)),(ISNUMBER(SEARCH("*Item2*",A2)))),"Assigned Value",A2)
This formula will search the contents of cell A2 and if it finds anything relating to “Item1” OR “Item2”, it will place the word “Assigned Value” in B2. If it doesn’t find the referring value, it will simply place the existing contents of cell A2 in cell B2.

## Monday, February 13, 2012

### Excel Nested IF's

A well known limitation to Excel is that you cannot "nest" more than 7 functions. For example, the following formula will fail because the limit is exceeded.

=IF(Sheet1!\$A\$4=1,11,IF(Sheet1!\$A\$4=2,22,IF(Sheet1!\$A\$4=3,33,IF(Sheet1!\$A\$4=4,44,IF(Sheet1!\$A\$4=5,55,IF(Sheet1!\$A\$4=4,44,IF(Sheet1!\$A\$4=5,55,IF(Sheet1!\$A\$4=6,66,IF(\$A\$4=7,77,FALSE))))))))

As a general "rule of thumb," if you have a formula with more than 7 nested statements, you should consider using a VBA function instead. However, if you do not want to use VBA, you can get around this limitation by creating a defined name that refers to part of the formula. Since defined formulas are evaluated separately, you can have one or more defined formulas which refer to large formulas, and combine these into a "master" formula.

Suppose we wanted an nested IF formula to test: IF A4 = 1 Then 11 Else If A4 = 2 Then 22 Else If A4 = 3 Then 33 Else If A4 = 4 Then 44...Else If A4 = 55 Then 55 Else "Not Found" and so on.

Of course, as a practical matter, we'd be better off using a VLOOKUP to do this, but this will work for illustration. First, we'd create a named formula called OneToSix, referring to the formula:

=IF(Sheet1!\$A\$4=1,11,IF(Sheet1!\$A\$4=2,22,IF(Sheet1!\$A\$4=3,33,IF(Sheet1!\$A\$4=4,44,IF(Sheet1!\$A\$4=5,55,IF(Sheet1!\$A\$4=4,44,IF(Sheet1!\$A\$4=5,55,IF(Sheet1!\$A\$4=6,66,FALSE))))))))

Then create another named formula called SevenToThirteen, referring to the formula:
=IF(Sheet1!\$A\$4=7,77,IF(Sheet1!\$A\$4=8,88,IF(Sheet1!\$A\$4=9,99,IF(Sheet1!\$A\$4=10,100,IF(Sheet1!\$A\$4=11,110,IF(Sheet1!\$A\$4=12,120,IF(Sheet1!\$A\$4=13,130,"NotFound")))))))

Finally, enter the "master" formula in the worksheet cell: =IF(OneToSix,OneToSix,SevenToThirteen)

It's not pretty, but it can be a work around in a pinch...This "beats" the nested function limitation because no single part of the formula exceeds the limit, even though the "sum" of the components do. You can use this technique whenever you exceed the limit on nested functions.