?
Solved

How would I use formulas in an autopopulated table

Posted on 2011-05-05
11
Medium Priority
?
226 Views
Last Modified: 2012-05-11
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
Comment
Question by:futr_vision
  • 7
  • 4
11 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
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

by:futr_vision
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

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

Daily[Date],DateTable[[#This Row]
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Expert Comment

by:Michael Fowler
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

by:futr_vision
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

by:Michael Fowler
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

by:futr_vision
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

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

Accepted Solution

by:
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
   
   ' Set new header values
   Range(dateCol & headerRow).Value = "Date"
   Range(monthCol & headerRow).Value = "Month"
   
   '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
   ActiveSheet.ListObjects.Add(xlSrcRange, _
         Range(dateCol & headerRow & ":" & monthCol & daydif + headerRow + 1), , xlYes).Name = "DateTable"
   ActiveSheet.ListObjects("DateTable").TableStyle = "TableStyleMedium9"

BeforeExit:
   Application.ScreenUpdating = True

End Sub

Open in new window

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

Open in new window

0
 

Author Comment

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

Author Comment

by:futr_vision
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question