Solved

# How would I use formulas in an autopopulated table

Posted on 2011-05-05
Medium Priority
226 Views
Based on the previous question I need to be able to add formulas to some new columns. The formulas would look something like:

=SUMIFS(Daily[Registrations],Daily[Date],A32,Daily[Network],"Yahoo")

As you can see the data would be contained in a separate Excel table on a different worksheet.
Date-Picker2.xlsm
0
Question by:futr_vision
• 7
• 4

LVL 23

Expert Comment

ID: 35703551
futr_vision

Do you want the formula's to be in the created table

if so you can add a formula at the point where the data is entered

eg

Range(monthCol & i + headerRow + 1).Formula = "=SUMIFS(Daily[Registrations],Daily[Date],A32,Daily[Network],""Yahoo"")"

Note where there are double quotes in the formula you need to escape these to let the macro know it is not the end of the formula. You do this by adding another double quote before each quote you need to escape. This is why "Yahoo" becomes ""Yahoo"" above.

Michael
0

Author Comment

ID: 35708496
Hmm. Pretty close. I need to do something with the cell reference A32. It'll have to increment by 1 for each subsequent row. ( A31, A32, A33, etc...)
0

Author Comment

ID: 35709334
I've tried this in place of the cell but it doesn't work

Daily[Date],DateTable[[#This Row]
0

LVL 23

Expert Comment

ID: 35711864
Incrementing is not a problem. you could use something like

For i = 0 To daydif
Range(dateCol & i + headerRow + 1).Value = startDate + i
Range(monthCol & i + headerRow + 1).Formula = _
"=SUMIFS(Daily[Registrations],Daily[Date],A" & i + headerRow + 1 & ",Daily[Network],""Yahoo"")"
Next

This assumes that the formula wants the value from the same row in column A.

The above is building a string that when created will give the formula as it needs to be entered into the cell.

If the data from column A does not start on the same row then all that needs to be done is add the starting row to i at each iteration. So start at cell A32 and have it increment for each row you would use

For i = 0 To daydif
Range(dateCol & i + headerRow + 1).Value = startDate + i
Range(monthCol & i + headerRow + 1).Formula = _
"=SUMIFS(Daily[Registrations],Daily[Date],A" & i + 32 & ",Daily[Network],""Yahoo"")"
Next

Michael

0

Author Comment

ID: 35712933
This works great. I have many more columns to add that will need this same solution. I do have one question. I just noticed as I added this extra processing of data that the population of the table is very slow and that the table refreshes once I choose a new start date and before I can change the end date. Is there anyway to keep the table from refreshing every time a single date is changed? Maybe a "Start" button?
0

LVL 23

Expert Comment

ID: 35715124
Very easily I have attached an example of this in action. The button runs on sheet2

Michael
Date-Picker2-1.xlsm
0

Author Comment

ID: 35722375
What exactly did you do? I could compare all of the code but would prefer to look at the pertinent lines of code.

Also, any way to speed this up? It's very slow when you throw in calculations.
0

Author Comment

ID: 35722813
Would taking the data out of a table speed things up?
0

LVL 23

Accepted Solution

Michael Fowler earned 1000 total points
ID: 35724989
On sheet 2 I removed the call to updateDateTable from the date picker closeup events and instead call it from the click event for the button. This results in the table update method not being called until the button is selected. I have attached the code from sheet2 below.

To speed things up a bit you can try placing the following code in to the updateDateTable method

Application.ScreenUpdating = False

On Error GoTo BeforeExit

and

BeforeExit:
Application.ScreenUpdating = True

This code stops the screen updating until the method has been completed and so can speed up the macro. The full macro has been attached below. The on error section ensures that updating is re-enabled in the case where an error is encountered

Michael
``````Option Explicit

Public Const dateCol As String = "L"
Public Const monthCol As String = "M"
Public Const headerRow As Long = 10

Sub updateDateTable(startDate As Date, endDate As Long)

Dim daydif As Integer, i As Integer, lastRow As Integer

Application.ScreenUpdating = False

On Error GoTo BeforeExit

'Determine the lastrow of data the difference in days between the datepicker values
lastRow = Range(dateCol & Rows.Count).End(xlUp).Row
daydif = DateDiff("d", startDate, endDate)

'Delete the table currently in place
Range(dateCol & headerRow & ":" & monthCol & lastRow).Delete Shift:=xlUp

'Input new dates and month name
For i = 0 To daydif
Range(dateCol & i + headerRow + 1).Value = startDate + i
Range(monthCol & i + headerRow + 1).Value = Format(startDate + i, "mmmm")
Next

'Create new table for the new date range
Range(dateCol & headerRow & ":" & monthCol & daydif + headerRow + 1), , xlYes).Name = "DateTable"
ActiveSheet.ListObjects("DateTable").TableStyle = "TableStyleMedium9"

BeforeExit:
Application.ScreenUpdating = True

End Sub
``````
``````Private Sub CommandButton1_Click()
Call updateDateTable(DTPickerStart2.Value, DTPickerEnd2.Value)
End Sub

Private Sub DTPickerEnd2_Closeup()
If DTPickerEnd2.Value < DTPickerStart2.Value Then
MsgBox "End date must be larger than the start date"
DTPickerEnd2.Value = DTPickerStart2.Value
End If
End Sub

Private Sub DTPickerStart2_Closeup()
If DTPickerStart2.Value > DTPickerEnd2.Value Then
MsgBox "Start date must be lower than the end date"
DTPickerStart2.Value = DTPickerEnd2.Value
End If
End Sub
``````
0

Author Comment

ID: 35769189
Still slow but this definitely works. Thanks
0

Author Comment

ID: 35769197
I wonder if there is a way to prefetch all the data from the source data much like a pivot table does.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URLās and adds a header titled āURL Listā. It then searches through all URLās in column āAā, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URLās are then highligā¦
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tā¦
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month14 days, 15 hours left to enroll