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.
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...
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.
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
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.
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:
Then create another named formula called SevenToThirteen, referring to the formula:
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.