Referring to a Name Range in Excel by Name

Ever need to make a formula where you have the ability to change the name of the range it is referring to on-the-fly via the value of another cell?

The formula INDIRECT() helps us achieve this.
For example I have the following formula:

VLOOKUP($A8, March, 3, false)

Now what if you didnt want to have to change the months manually every few weeks to generate a report? Yes you could do a find/replace. However a more elegant solution would be to simply write April on a cell and have the formula call and process the array with that name.

Below is what it would look like if I made it so that I entered the name of the month onto cell B4.

VLOOKUP($A8, INDIRECT($B$4), 3, false)

Great, now you dont have to seek out all the formulas where you wrote March and replace the name range in each formula. This formula will save you time if planned right.

How To Sum Cells Across Multiple Spreadsheets

  1. Create a tab called named ‘Start’. Place it at the far left of all the tabs you would like to add.
  2. Create a tab called named ‘End’. Place it at the far right of all the tabs you would like to add.
  3. On the tab where your formulas are use a formula like this to add all the cells, rows, or columns together on your data tabs.

=SUM(Start:End!A1)

Removing Letters From Strings

This is a quick and dirty way I came up with to remove letters from a particular Excel cell via Macros. Just paste this into a module and call it like any other Excel formula by using RemoveText(cell).

I am a little rusty but I know it probably would have been more efficient to user Chars instead of a string array. : )

Public Function RemoveText(ByVal strInput As String) As String

    'Holds the alphabet for it to know what we don't want
    Dim arrAlphabet(26) As String

    'Temporary holding location while we scan through the input string
    Dim strTemp As String

    'Place to aggregate our output
    Dim strOutput As String

    arrAlphabet(0) = "A"
    arrAlphabet(1) = "B"
    arrAlphabet(2) = "C"
    arrAlphabet(3) = "D"
    arrAlphabet(4) = "E"
    arrAlphabet(5) = "F"
    arrAlphabet(6) = "G"
    arrAlphabet(7) = "H"
    arrAlphabet(8) = "I"
    arrAlphabet(9) = "J"
    arrAlphabet(10) = "K"
    arrAlphabet(11) = "L"
    arrAlphabet(12) = "M"
    arrAlphabet(13) = "N"
    arrAlphabet(14) = "O"
    arrAlphabet(15) = "P"
    arrAlphabet(16) = "Q"
    arrAlphabet(17) = "R"
    arrAlphabet(18) = "S"
    arrAlphabet(19) = "T"
    arrAlphabet(20) = "U"
    arrAlphabet(21) = "V"
    arrAlphabet(22) = "W"
    arrAlphabet(23) = "X"
    arrAlphabet(24) = "Y"
    arrAlphabet(25) = "Z"

    'Loop through each letter in the input string
    For x = 1 To Len(strInput)

        'Check that letter against the alphabet
        strTemp = Right(Left(strInput, x), 1)

        For y = 0 To 25
            'If it's a letter...
            If (strTemp = arrAlphabet(y) Or strTemp = LCase(arrAlphabet(y))) Then
             'Clear out our temporary hold variable
                strTemp = ""
            End If
        Next y

        'If it's not a number then concantonate it for the end result
        strOutput = strOutput & strTemp
    Next x

    RemoveText = strOutput

End Function

How the SumProduct Formula Works

The sum product formula is officially used for summing the product of
two or more columns in an array. Here is a simple example.

sumprod1

Our formula:

=sumproduct(A1:A2, B1:B2)

Really translates to:

=(A1+B1) * (A2+B2)

*notice row 1 is adding to row 1, and row 2 to 2

Because:

(5*6)+(10*3) = 60

It is pretty straight forward.

Mutiple Conditional

You can also use the sumproduct formula as a super useful multi-conditional countif or sumif formula. Check out the table and how the formula is used. Please note that I am using named arrays in order to make this as simple as possible.

sumprod2

Super Countif

=sumproduct(--(name_array= John),--(location_array= CA),--(has_a_dog_array="yes"))

The above use of the sumproduct formula will return the count of all the down owners in California that are named John. Cool isnt it? Very useful.

But what if you want to know the sum of the incomes of all the guys named John that live in California? Easy, add the range that has the income to the end of this. Like so.

Super Sumif

=sumproduct(--(name_array= John),--(location_array= CA),--(has_a_dog_array="yes"), --(income_array))

In this case the formula will match all the Johns that live in California and have a dog and tally up their incomes. So our result will be 123,000. You can also use conditionals if working with numbers.

Now I know the formula looks funny with all those negative signs but they are required as the formula will not work right if you omit them. In a couple of tests I have conducted I noticed that if the double negatives are removed the formula tries to multiply boolean (true/false) types instead of matching up what you are looking for. So though my observation of this is probably not ideal, remember the keep the double negatives in there for it to work right.

A very important thing to note is that when working with sumproduct formulas you must make sure your arrays are the same length. You cant have the name array be 100 rows and the location array be 95 rows high because it will cause an error.