Imagine you want to extract phone numbers from a text file or a worksheet, however the issue here is that there is also text combined with phone numbers and that makes it hard to filter out. Here a regular expression is coming to rescue.

Looking for 555-412395 in a cell range is easy in excel but looking for phone numbers with 3 digits then a hyphen and then 6 more digits is a lot harder. Each digit can be anything between 0 and 9.

You will find a guide later in this post to most of the characters you can use in regular expressions and how to solve the problem with phone numbers.

Nothing, you don't need to reference anything in VB Editor \ Tools for this to work. I will now demonstrate a simple UDF that counts matching strings in a cell range.

Function CountStringsCellRange(c As Range, pttrn As String) Rng = c.Value regexpattern = pttrn With CreateObject("vbscript.regexp") .Global = True .MultiLine = True .IgnoreCase = False .Pattern = regexpattern If c.Cells.CountLarge > 1 Then For rr = LBound(Rng, 1) To UBound(Rng, 1) For cc = LBound(Rng, 2) To UBound(Rng, 2) Set Results = .Execute(Rng(rr, cc)) i = i + Results.Count Next cc Next rr CountStringsCellRange = i Else Set Results = .Execute(Rng) CountStringsCellRange = Results.Count End If End With End Function

- Copy UDF above
- Start/Open Excel
- Go to VB Editor (Alt+F11)
- Click "Insert" on the menu at the top
- Click "Module"
- Paste code to code module

I will use the UDF above to show you how to work with patterns using specific characters. It is important to know which characters you can use and what they do.

**Search is case sensitive **

AbC - matches this exact string. A upper case letter A, a smaller case letter b and a upper case letter C

**A range of letters**

A hyphen allows you to search for a range of characters or numbers. [] brackets allow you to search for exactly one character.

[A-Z] finds a single upper case letter between A to Z.

[A-Z][a-z] matches a single uppercase letter between A to Z and then a lower case letter between a to z.

**Match at least one or more characters**

A+ matches a single A and multiple A's.

A+ matches A and AA and AAA but not B, three matches in total displayed in cell C9 in picture above.

**Match 0 or one character before it**

A? matches an empty string or one A.

**Match 0 or more characters**

A* matches an empty string or one or more A.

**Match any character except new line**

. matches any character

A.A matches AbA and ACA

**Multiple matches**

Curly brackets allows you to match multiple characters.

[A-Z]{4} is the same as [A-Z][A-Z][A-Z][A-Z] but easier to write. [A-Z]{4} matches 4 upper case letters between A-Z.

ABCD is the only match in string ABCD abcd aBcD to pattern [A-Z]{4}.

**NOT operator**

^ allows you to exclude certain characters

[^A] matches B, C and D because they are not equal to A.

**OR operator**

| is an OR operator.

A|C matches A and C in string ABCD.

**Escape characters**

\ (backslash) escapes characters.

If you are looking for question marks in a string the backslash allows you to escape certain characters that would otherwise have a different functionality.

**Match start and end of string**

^ matches the start of a string.

$ matches the end of a string.

^ABC matches the three first characters ABC DCE

DCE$ matches the three last characters in string ABC DCE

BC$ does not match ABC DCE because it is not at the end of the string

**Group patterns**

Parentheses allows you to group characters

W(AB){2} is the same as WABAB

In the beginning of this post I said that regular expressions are good for matching phone numbers.

The phone number I am looking for begins with three digits 0 to 9, the pattern becomes [0-9]{3}.

Next is a hyphen and then 6 more digits between 0 to 9, the pattern becomes -[0-9]{6} and combined [0-9]{3}-[0-9]{6}.

Count matching substrings using reg exp.xlsm

]]>

- Reverse two-way lookups in a cross reference table
- Reverse two-way lookup in a cross reference table

I have this table where there are two dependent variables.

table

Row 2 contains variable and Column B contains another variable

The combination of 32 with 51 gives me 0.9 or cell D4

I need to report which 2 and B combinations give me values between 0.4 and 0.5. In this case, it would be 31-53 and 32-50.

I could rearrange this to be in 3 columns with the results on the last column and use something like below (The equation does not represent the example I gave.)

IFERROR(INDEX(array, SMALL(IF((min=data), ROW($B$2:$B$10)-1), ROW(A3)), COLUMN(A3)), " ")

This works but my excel table would have too many rows and the initial data set comes the other way so it would take some time to convert it. The original matrix is more compact.

I am trying to convert this equation but I am having trouble matching it to an array instead to just one column.

table

Row 2 contains variable and Column B contains another variable

The combination of 32 with 51 gives me 0.9 or cell D4

I need to report which 2 and B combinations give me values between 0.4 and 0.5. In this case, it would be 31-53 and 32-50.

I could rearrange this to be in 3 columns with the results on the last column and use something like below (The equation does not represent the example I gave.)

IFERROR(INDEX(array, SMALL(IF((min=data), ROW($B$2:$B$10)-1), ROW(A3)), COLUMN(A3)), " ")

This works but my excel table would have too many rows and the initial data set comes the other way so it would take some time to convert it. The original matrix is more compact.

I am trying to convert this equation but I am having trouble matching it to an array instead to just one column.

The following array formula returns multiple values from a cross reference table if they meet specific critera.

**Array formula in cell H3:**

=TEXTJOIN(",", TRUE, IF(($C$3:$E$5>=$H$1)*($C$3:$E$5<=$H$2), $B$3:$B$5&"-"&$C$2:$E$2,""))

**How to build an array formula**

If you did above steps correctly excel automatically adds a beginning and ending curly bracket to the formula, like this:

Don't enter these characters yourself.

Note, TEXTJOIN function works only in Excel 2016. If you have an earlier version of excel, use the UDF in this post or read my comment.

*Step 1 - Check if values in cell range C3:E5 are larger than H1 and smaller than H2*

IF(($C$3:$E$5>=$H$1)*($C$3:$E$5<=$H$2), $B$3:$B$5&"-"&$C$2:$E$2,"")

becomes

IF({0,0,1;1,0,0;0,0,0}, $B$3:$B$5&"-"&$C$2:$E$2,"")

*Step 2 - If TRUE concatenate corresponding values in row 2 and column B*

IF({0,0,1;1,0,0;0,0,0}, $B$3:$B$5&"-"&$C$2:$E$2,"")

becomes

IF({0,0,1;1,0,0;0,0,0}, {"31-50","31-51","31-53";"32-50","32-51","32-53";"33-50","33-51","33-53"},"")

and returns

{"","","31-53";"32-50","","";"","",""}

*Step 3 - Concatenate values using TEXTJOIN function*

TEXTJOIN(",",TRUE,{"","","31-53";"32-50","","";"","",""})

returns

31-53. 32-50 in cell H3.

Link to two-way lookups in an index table:

Looking up data in a cross reference table

Reverse two-way lookup in a cross reference table.xlsx

The following array formulas returns a single value from a cross reference table.

**Array formula in cell E15:**

=INDEX($A$3:$A$12, MIN(IF(B3:K12=E14, MATCH(ROW(B3:K12), ROW(B3:K12)), "")))

**Array formula in cell E16:**

=INDEX($B$2:$K$2, MIN(IF(B3:K12=E14, MATCH(COLUMN(B3:K12), COLUMN(B3:K12)), "")))

**How to build an array formula**

- Select cell E15
- Type the formula above in formula bar

- Press and hold CTRL + SHIFT key simultaneously
- Press Enter

Your formula is now enclosed with curly brackets, like this: {=arrray_formula}

*Step 1 - Check what values in cell range equals value in E14 and return smallest row*

MIN(IF(B3:K12=E14, MATCH(ROW(B3:K12), ROW(B3:K12)), ""))

returns 5.

**Step 2 - Return corresponding header value**

=INDEX($A$3:$A$12, 5)

returns H.

Reverse two-way lookup in a cross reference tablev2.xlsx

**TEXTJOIN**(*delimiter*, *ignore_empty*, *text1*, ...)

Concatenates a list or range of text strings using a delimiter

**IF**(*logical_test*, [*value_if_true*], [*value_if_false*])

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**ROW(**reference**)**

Returns the rownumber of a reference

The map chart below shows US states by population and I will show how I made this.

I copied US population data from wikipedia and rounded values to millions with one decimal.

This world map shows up, US states are barely visible. This is not what we want.

Double click on the map with left mouse button to access chart formatting.

Here we have three options we can modify, map projection, map area and map labels.

Map projection

*Automatic -*Self explanatory*Mercator -*Cylindrical map projection*Miller -*Modified Mercator projection*Robinson -*Showing the globe as a flat image

Map area

- Automatic
- Only regions with data
- World

Select "Only regions with data" and the chart changes to this:

Still not good, US state Alaska is so big it is hard to see the other states.

However, this setting shows different options if we add a third column to our data. Column A has value US in all 52 cells, see picture below.

Now change the data source to include column A. Right click on chart and select "Select Data...". Change the cell reference to A1:C56

Click OK button. The map chart changes once again.

Lets look at the formatting options. There are now five different map areas and the "Automatic" setting shows the above map chart.

- Automatic
- Only regions with data
- Country/Region
- Multiple countries/regions
- World

Map labels setting lets you display state names: None, best fit only or show all. I have selected "Show all" and this is what the map chart looks like.

If you resize the chart, even more state names are visible. You can also add the population number, right click on map and click on "Add Data Labels", see chart below.

If you don't like the state colors you can change that, as well. Double click on the map and then click on a state, the format object task pane appears to the right hand side of the excel window, see picture below.

Click "Series Options" and then "Series Color" to see map chart color settings. Click the "Fill color" buttons and pick a lowest value color and a highest value color.

]]>

I have

A1(anil singh raj)

It can be anything Like

A1(singh raj anil)

I want return value in

B1 (10 30 20)

Or

B1(30 20 10)

Or

Lookup array is

D1(anil) E1(10)

D2(raj) E2(20)

D3(singh) E3(30)

A1(anil singh raj)

It can be anything Like

A1(singh raj anil)

I want return value in

B1 (10 30 20)

Or

B1(30 20 10)

Or

Lookup array is

D1(anil) E1(10)

D2(raj) E2(20)

D3(singh) E3(30)

I have made a small custom function to split the search string and get the values you are looking for.

**Formula in cell B1:**

=SearchValues(A1,$D$1:$D$3,$E$1:$E$3)

Function SearchValues(str As Range, search_col As Range, return_col As Range) Dim j As Long, i As Long arr = Split(str, " ") j = search_col.Rows.CountLarge For Each Vl In arr For i = 1 To j If search_col.Cells(i, 1) = Vl Then result = result & return_col.Cells(i, 1) & " " Next i Next Vl SearchValues = result End Function

You need to copy the code above and paste it to a code module, detailed instructions below.

- Open vb editor (shortcut keys: Alt+F11)
- Insert a new module
- Paste code to code module
- Exit vb Editor

Split search value using delimiter and search for each string.xlsm

]]>

*Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm)*

Today I want to demonstrate a user defined function that downloads data from ThingSpeak to Excel so you can easily analyze and chart the data yourself, in excel.

The way this works is that using a simple http address with your channel and field number and parameters, you can easily fetch values from your feed.

The http address returns xml data, something like this:

The following user defined function lets you specify a channel, field and some more parameters. The function then removes xml tags and returns data to worksheet.

Remember to enter this as an array formula, 3 columns wide.

Function ThingSpeak(ch As Long, fld As Integer, prm As String) 'Add question mark if missing If prm <> "" Then prm = "?" & prm 'Build url url = "https://api.thingspeak.com/channels/" & ch & "/fields/" & fld & ".xml" & prm On Error Resume Next 'Download data Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", url, False http.Send tmp = http.responseText 'Check for errors If Err <> 0 Then MsgBox "Error fetching values" On Error GoTo 0 Exit Function End If On Error GoTo 0 'Remove beginning xml data tmp = Right(tmp, Len(tmp) - InStr(tmp, "") + 1) 'Clear xml tags with RemoveFromStr udf and then return values to worksheet ThingSpeak = RemoveFromStr(tmp) End Function

Function RemoveFromStr(tmp As Variant) Dim tmp2 As Variant 'Clear xml tags using a regular expression regexpattern = "<.*?>" With CreateObject("vbscript.regexp") .Global = True .MultiLine = True .IgnoreCase = False .Pattern = regexpattern Set Results = .Execute(tmp) End With If Results.Count <> 0 Then With Results For d = 0 To .Count - 1 tmp = Replace(tmp, .Item(d), ",") Next End With End If 'Remove spaces tmp = Replace(tmp, " ", "") 'Remove newline tmp = Replace(tmp, vbLf, "") 'Replace T and Z with space character tmp = Replace(tmp, "T", " ") tmp = Replace(tmp, "Z", " ") 'Combine multiple commas For i = 1 To Len(tmp) If Mid(tmp, i, 1) = "," And Mid(tmp, i, 1) = Mid(tmp, i + 1, 1) Then Else tmp1 = tmp1 & Mid(tmp, i, 1) End If Next i 'Remove beginning and ending commas If Left(tmp1, 1) = "," Then tmp1 = Right(tmp1, Len(tmp1) - 1) If Right(tmp1, 1) = "," Then tmp1 = Left(tmp1, Len(tmp1) - 1) 'Split text into 1D array tmp1 = Split(tmp1, ",") 'Count array j = ((UBound(tmp1) - LBound(tmp1) + 1) / 3) - 1 'Build 2D array ReDim tmp2(0 To j, 0 To 2) ro = 0 co = 0 For i = LBound(tmp1) To UBound(tmp1) tmp2(ro, co) = tmp1(i) co = co + 1 If co = 3 Then co = 0 ro = ro + 1 End If Next i 'Return array RemoveFromStr = tmp2 End Function

The ColorIndex property has 56 different colors, shown below.

The color property holds up to 16 777 216 colors. I tried to color 16 columns with 1048576 rows each (16 * 1048576 = 16 777 216) using the color property but excel returned this error after 65277 cells.

The following macro lets you count background colors, however it won't count cells colored with conditional formatting.

Sub CountColors() 'This macro counts background colors in cell range 'http://www.get-digital-help.com/2017/03/30/count-colored-cells/ Dim IntColors() As Long, i As Integer Dim chk As Boolean Set rng = Application.InputBox("Select a cell range to count colors: ", , , , , , , 8) ReDim IntColors(0 To 2, 0) For Each cell In rng chk = False For c = LBound(IntColors, 2) To UBound(IntColors, 2) If cell.Interior.ColorIndex = IntColors(0, c) And cell.Interior.Color = IntColors(1, c) Then IntColors(2, c) = IntColors(2, c) + 1 chk = True Exit For End If Next c If chk = False Then IntColors(0, UBound(IntColors, 2)) = cell.Interior.ColorIndex IntColors(1, UBound(IntColors, 2)) = cell.Interior.Color ReDim Preserve IntColors(2, UBound(IntColors, 2) + 1) End If Next cell ReDim Preserve IntColors(2, UBound(IntColors, 2) - 1) Set WS = Sheets.Add WS.Range("A1") = "Color and count" WS.Range("B1") = "ColorIndex" WS.Range("C1") = "Color" j = 1 For i = LBound(IntColors, 2) To UBound(IntColors, 2) If IntColors(2, i) <> 0 Then WS.Range("A1").Offset(j).Interior.ColorIndex = IntColors(0, i) WS.Range("A1").Offset(j).Interior.Color = IntColors(1, i) WS.Range("A1").Offset(j) = IntColors(2, i) WS.Range("A1").Offset(j, 1) = IntColors(0, i) WS.Range("A1").Offset(j, 2) = IntColors(1, i) j = j + 1 End If Next i End Sub

- Select and copy code above (Ctrl+c)
- Open VB Editor (Alt+F11)
- Insert a new module to your workbook
- Paste code to code module

- Press Alt + F8 to see a list of macros
- Select CountColors
- Press "Run" button
- Select a cell range you want to count

The macro then creates a new sheet with cells in a column colored and their count, see picture below.

Value -4142 means No fill and -4105 is default color (white).

There is no way to quickly transfer cell formatting properties to an array so the macro is quite slow, it reads a cells property one by one. I don't recommend using this with larger cell ranges unless you are prepared to wait for a while.

]]>What can an excel defined table do for you? It will simplify your work with data sets, adding or removing data, filtering, sorting, readability using cell formatting, cell references, formulas and more. I will go through all this in greater detail, keep reading.

- Create table
- Name a table
- Edit data
- Sort data
- Filter data
- Structured references
- Formulas in a table
- Formatting
- Show totals
- Named ranges
- Charts
- Data Validation List
- Working with a filtered excel table
- Quickly navigate to a table

Follow these simple steps to convert a cell range to a table:

- Go to tab "Insert" on the ribbon
- Select your data set
- Click "Table" button on tab "Insert"
- Click "OK" button if your table has headers, if not deselect check box and click "OK". Excel will automatically create headers for you.
- You have built an excel table

Tip! Use short cut keys CTRL + T to quickly build a table.

I recommend you give the table and table headers descriptive names, for example it will be easier to identify cell references in formulas.

- Select a cell in your table
- Excel automatically navigates to tab "Design" on your ribbon
- Change table name
- Press Enter

Add data to a cell adjacent to the table and the table expands automatically.

Press tab on keyboard to move from one cell to another, if you press tab while having the most lower right cell in the table selected a new row is included. See picture below.

Right click on a cell in a table to open a menu, from there you can insert or delete rows and columns, also select columns and rows or all table data.

Sorting a table is easy, click on any black triangle located at each header, a menu appears allowing you to quickly sort data in a descending or ascending order.

An arrow next to the black triangle indicates sort order. Sort Z to A (descending) shows you an arrow pointing down.

You can also sort on multiple columns, follow these steps.

- Right click on a cell
- Click Sort and then click "Custom Sort..."

- Select column name to sort on and sort order then add more columns.

- Click OK button to apply sort settings to table

- Click on a black triangle next to any header
- Select values you want to filter
- Click OK button

See animated picture below.

Excel allows you to apply filters to multiple columns easily, repeat above steps with another column.

Use the search field to quickly find the value you want to filter, see picture below.

You are probably used to cell references like this one:

=SUM(E3:E6)

Creating a cell reference to a table column returns this instead, see picture below.

First the table name (Inventory) and then the column name enclosed with brackets [Price].

The amazing thing with structured references is that if you add or remove values to a table the structured reference stays the same, no need to update cell references. In other words, they are dynamic.

*Back to top*

The following example demonstrates what happens if I type a formula in an excel table. I want to multiply cell E3 with F3 in cell G3, see animated picture below.

Excel creates these structured cell references in cell G3 if I type = (equal sig) and then click on cell E3, type * (asterisk) and then click on cell F3:

=[@['#]]*[@Price]

@ (at) means cell value on same row as formula.

Excel also calculates the remaining cells in column G automatically, see animated picture above.

Creating a reference to the entire excel table and headers returns this: =Inventory[#All]

A reference to data in table looks like this: =Inventory

A reference to a table column returns: =Inventory[Warehouse]

A reference to a column header only looks like this: =Inventory[[#Headers],[Warehouse]]

*Back to top*

- Select a cell in excel table
- Go to tab "Design" on the ribbon
- Hover over a table style and see your table change

- If you like it click on it to select it
- Click the black triangle to se even more table styles

You can also build your own table style.

- Select a table cell
- Go to tab "Design"
- Click black triangle

- Click "New Table Style..."
- Enter a name for your table style
- Select a table element you want to change
- Click "Format" button
- Format as you like
- Click OK button twice

- Click on a cell in an excel table
- Go to tab "Design"

- Click "Total Row" check box

- Click cell G7 and then on black triangle

- You can change how value in cell G7 is calculated, the menu has these formulas: Average, Count, Count Numbers, Max, Min, Sum, StdDev, Var and More Functions.
- If you click "More Functions" a dialog box opens with formulas to choose from.

The Name Manager contains a list of all named ranges and excel tables.

- Click "Formula" tab on the ribbon
- Click "Name Manager"

There are only tables in this workbook so the dialog box shows table names, no named ranges.

Combine chart and table to make use of dynamic cell references while filtering data.

More details here: How to create a dynamic chart

*Back to top*

The following animation shows you a data validation list linked to a table.

Read this post if you are interested in the details:

How to use a table name in data validation lists and conditional formatting formulas

*Back to top*

If you try to use a filtered table as a data source in a formula you are in for trouble, see animated picture below.

As you can see above the SUM function sums all values in table regardless of filtered or not.

I have written a few articles about this:

- Highlight duplicates in a filtered excel defined table
- Count unique distinct values in a filtered table
- Highlight unique values in a filtered excel table
- Populate a list box with visible unique values from an excel table (vba)
- Highlight unique values in a filtered excel table
- Extract unique distinct values from a filtered table (udf and array formula)
- Vlookup visible data in a table and return multiple values

Excel lets you quickly focus on a table if you type the table name in the name box.

]]>Hi Oscar,Your formula works great, however, I was wondering if there is capability to add another countif criteria so that it produces a random unique number different from the one above AS WELL AS the one to the left.

I have 7 groups, 7 tables, and 7 rotations for which each group will move to a different table.

With your formula, I am able to successfully assign each group a random order of the 7 tables to rotate too with no repeats (however I am unable to guarantee that for each rotation only one group is at each table), OR I am able to produce a schedule that has max one group per table per rotation, but then I am not able to have each group have no repeats for the tables they are rotating too.

Is there a way to tweak the formula to satisfy both requirements where each group has a random 1-7 table rotation, AND for each rotation there is only one group per table? Thanks!! Appreciate the help.

**Answer**

This is not as easy as it may seem. I first tried using the same technique as in the post but the formula returned errors which is really not surprising if you think of it. I will explain why later in this post but first you need to know how the original formula works.

The original formula uses the COUNTIF function to take previous values vertically into account and return a new unique random number between 1 and 7. This works well, unique random numbers between 1 to 7 are produced in column B.

**Array formula in cell B5:**

=IF(ROW(3:3)<8, LARGE((COUNTIF($B$2:B4, ROW($1:$7))=0)*ROW($1:$7), RANDBETWEEN(1, SUM(--(COUNTIF($B$2:B4, ROW($1:$7))=0)))), "")

I tweaked the formula so it also considers previous values horizontally to make sure a table never has a duplicate group number, see picture below.

Here is the entire table, the formulas return almost always a #num error somewhere in the table. Press F9 to recalculate values if you have the attached workbook open.

So why is this happening? Look at cell D9, number 4 and 1 is to the left of cell D9. This means that only numbers 2,3,5,6 or 7 can be populated in cell D9 or there will be a duplicate on row 9.

In this case, the values above cell D9 are 7,4,5,3,6 and 2 so the only value left is 1 or there will be a duplicate in column D. Number 1 is not possible in cell D9 so the formula returns a #NUM error.

The formulas above cell D9 do not take this into consideration while producing a random value, they only look back at previous values. It is not possible for the formulas to look at cells below or to the right because excel calculates each cell in this order.

It begins with the upper most left cell containing a formula, in this case B2 and then continues with the next cell below and so on, until there are no more formulas in that column. Excel then continues with the first formula in the next column, in this case cell C2.

My second attempt to solve this problem is to move each value one cell for each rotation, the picture below shows how in the second rotation.

The problem with this approach is that only the first rotation has random values, the remaining rotations are not random. However, this technique makes it absolutely certain that a table has no repeats. Perhaps it is random enough?

**Formula in cell C3:**

=INDEX($B$3:$B$9, IF(MATCH(B3, $B$3:$B$9, 0)=7, 1, MATCH(B3, $B$3:$B$9, 0)+1))

To make it even more random I changed the formula so the first value in each column is random (row 3) but the sequence is the same as in rotation 1 (column B). As before, it takes previous values horizontally into account when calculating a new unique value.

**Array formula in cell B3:**

=IF(ROW(1:1)<8, LARGE((COUNTIF($B$2:B2, ROW($1:$7))=0)*ROW($1:$7), RANDBETWEEN(1, SUM(--(COUNTIF($B$2:B2, ROW($1:$7))=0)))), "")

Copy cell B3 and paste to cell range B4:B9.

**Array formula in cell C3:**

=IF(ROW(1:1)>1, INDEX($B$3:$B$9, IF(MATCH(C2, $B$3:$B$9, 0)=7, 1, MATCH(C2, $B$3:$B$9, 0)+1)), LARGE((COUNTIF($B3:B3, ROW($1:$7))=0)*ROW($1:$7), RANDBETWEEN(1, SUM(--((COUNTIF($B3:B3, ROW($1:$7)))=0)))))

Copy cell C3 and paste to cell range C3:H9.

The above picture shows you January 2017, simply enter the project name in column A and the hours in cell range B5:AF32. Row 4 contains dates for January, row 3 the weekdays and row 2 the week numbers. Saturday and Sundays are colored grey.

The Summary sheet, see picture below, contains all projects entered in all month sheets and a total, both for the month and project. A quite large array formula extracts all project names in column B, you don't need to do that manually.

A simple SUMIF function sums the values from each monthly sheet, excel takes care of that too. There is no vba in this workbook, you can find the download link below.

If you change the year on sheet "Summary" you will notice that the monthly sheets change accordingly, you don't need to change weekdays, week number or color weekends on each sheet, excel will do it for you.

Any suggestions for improvement?

The workbook has a summary sheet and sheets for each month in a year. The summary sheet allows you to enter the year in cell H1, I also changed the font size for that cell.

The first monthly sheet is January, this sheet will be the template for the remaining months. This means I will copy this sheet and change the date for each sheet.

Cell D1 contains this formula: =Summary!H1

This lets you change the year on the summary sheet and all other monthly sheets will be instantly updated.

Formula in cell I1: =DATE(D1,1,1)

Select cell I1 and press CTRL + 1 to open the formatting cells dialog box. Change formatting category to "Custom".

Change Type to MMMM;@

If this is not working you need to find out your regional settings in Windows and change MMMM accordingly. This web page from Microsoft explains how to use the formatting dialog box.

This is what cell I1 now looks like:

Time to add dates,type 1 to 31 in cell range B4:AH4. I also change the cell width to 21 pixels. Type "Project" in cell A4, cells below contains project names.

Add text "Total:" to cell AG4 and use this formula in cell AG5:=SUM(B5:AF5)

Copy cell AG5 and paste to cell range AG6:AG32. See picture below.

Add text "Total:" to cell A33 and use this formula in cell B33:=SUM(B5:B32)

Copy cell A33 and paste to cell range B33:AF33. See picture above.

To make this sheet easier to read I want the row above dates to contain the abbreviation of days of the week. I am using this formula in cell B3:

=IF(MONTH($B$4)=MONTH(B4), CHOOSE(WEEKDAY(B4,2),"M","T","W","T","F","S","S"),""). See picture below.

I also want the row above days of the week to contain the week number. Formula in cell B2:

=WEEKNUM(B4,1)

But I only want that number above a date when a new week starts.

Formula in cell C2:

=IF((WEEKNUM(C4,1)<>WEEKNUM(B4,1))*(MONTH($B$4)=MONTH(B4)), WEEKNUM(C4,1),"")

Copy cell C2 and paste to D2:AF2. See pic below.

The following conditional formatting formula highlights weekends gray:

(WEEKDAY(B$4,2)>5)*(MONTH(B$4)=MONTH($B$4))

- Select cell range B4:AF32
- Go to tab Home on the ribbon
- Click "Conditional Formatting" button and then click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Paste above formula to field "Format values where this formula is true:"

- Click "Format" button
- Go to tab "Fill"
- Pick a color

- Click OK button

Worksheet "January" now looks like this:

The conditional formatting changes depending what month and year it is, this is not something you have to manually do.

Next thing is the grid pattern, here is the conditional formatting formula:

=IF($AI$3="Off",,MONTH(B$4)=MONTH($B$4))

To build formatting formula, repeat above steps 1 to 8 except instead of picking a color, go to tab "Border" and click "Outline, then click OK button. See picture below.

Worksheet January is now ready, time to copy the worksheet and create worksheets for the remaining months.

- Right click on sheet "January"
- Click "Move or Copy..."
- Select "move to end" and "Create a copy"
- Click Ok button

Repeat above steps until you have a worksheet for each month in a year.

- Rename the sheet after January to February
- Change formula in cell I1 to =DATE(D1,2,1)

Note that February is the second month in a year and the second argument in the formula is then 2.

Repeat steps 1 and 2 above for all remaining worksheets.

]]>

A regular expression is a sequence of characters that define a search pattern, according to Wikipedia.

This is the custom function I am using to extract cell references from a formula.

Function ExtractCellRefs(c As Range) As String regexpattern = "" With CreateObject("vbscript.regexp") .Global = True .MultiLine = True .IgnoreCase = False .Pattern = regexpattern Set Results = .Execute(c.Formula) End With If Results.Count <> 0 Then With Results For d = 0 To .Count - 1 Rstr = Rstr & .Item(d) & "," Next End With ExtractCellRefs = Left(Rstr, Len(Rstr) - 1) Else ExtractCellRefs = "No Matches" End If End Function

The tricky part is the regular expression and I am a beginner at this, feel free to simplify my expression.

A cell reference can be anything from A1 to XFD1048576 so to match that the reg exp becomes:

[A-Z]{1,3}[0-9]{1,7}

[A-Z] matches any upper case letter from A to Z.

[A-Z]{1,3} matches 1 or up to 3 lower and upper case letters from A to Z. Example, XFD1048576 contains three letters.

[0-9]{1,7} matches 1 or more up to 7 digits from 0 to 9. Example, XFD1048576 contains 7 digits.

A cell reference can also be absolute or relative or both and the $ sign tells which it is.

\$?[A-Z]{1,3}\$?[0-9]{1,7}

\ (backslash) escapes the character that follows

\$ allows us to use the character $, if I had not used the \ (backslash) $ (dollar sign) had been taken for a match at end of string, I don't want that to happen.

\$? the question mark matches zero or one of the pattern defined before it, in this case $ (dollar sign)

The picture below shows the matches for above expression \$?[A-Z]{1,3}\$?[0-9]{1,7}

It also works for this simple formula: =SUM(XFD1048576, $A$1,A$1,$A1), it returns these cell references: XFD1048576,$A$1,A$1,$A1

**Cell reference to a cell range**

The above regular expression finds only cell refs to a single cell, how do we find a solution to that?

A cell ref to a single cell looks like this =A1, a cell ref to a cell range may look like this =A1:C3

\$?[A-Z]{1,3}\$?[1-9]{1,7}(:\$?[A-Z]{1,3}\$?[1-9]{1,7})?

The new part is

(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?

( (parentheses) groups an expression

: a cell ref to a cell range contains a colon :

\$?[A-Z]{1,3}\$?[0-9]{1,7} is the same as before, it matches letters and digits

(:\$?[A-Z]{1,3}\$?[0-9]{1,7}) the parentheses groups the expression

(:\$?[A-Z]{1,3}\$?[0-9]{1,7})? the question mark matches zero or one of the pattern defined before it, in this case the group

**Cell references to other sheets**

A cell reference to another sheet always ends with a ! (exclamation mark), the question mark matches zero or one of the pattern defined before it, in this case the ! (exclamation mark)

This is a new group so I am leaving out the previous expression for now, I will add it later. The regular expression is now !?

The sheet name may have lower and upper letters from A to Z and also numbers 0 to 9, the regular expression is [a-zA-Z0-9]{1,99}!?

If there is a blank space in the sheet name excel automatically surrounds the sheet name with two ' (apostrophe character), the expression becomes '?[a-zA-Z0-9]{1,99}'?!?

\s is any space character, this is what we have now '?[a-zA-Z0-9\s]{1,99}'?!?

'? the question mark matches zero or one of the pattern defined before it, in this case the ' (apostrophe character)

**Cell references to other workbooks**

There may also be cell references to other workbooks, it would be nice to find them as well.

A reference to a cell range in another workbook has the workbook name surrounded by these characters [].

('?[a-zA-Z0-9\s\[\]]{1,99})?'?!? , \ (backslash) escapes the character that follows in this case \[\]

The file name has a dot between the file name and the extension, ('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?

The final expression is

('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?

Can it be made smaller?

I am sure there are characters allowed in a filename or sheet name that I have not considered in this post but I believe it will be easy to add those as well.

This does not take care of named ranges in a formula but it would not be hard to build a list of named ranges and then check if the formula contains named ranges.

Don't forget to use the regular expression in the UDF. Replace this line:

regexpattern = ""

with this:

regexpattern = "'?([a-zA-Z0-9\s\[\]\.])*'?!?\$?[A-Z]+\$?[0-9]+(:\$?[A-Z]+\$?[0-9]+)?"