Free Microsoft Excel 2013 Quick Reference

PowerPivot Data Range between two sheets

I am working four sheets within PowerPivot: Date Sheet (in order to establish the relationship between the two data sheets), Customer Sheet (a list of unique customer numbers again to further establish the relationship between the two data sheets), Service Data consisting a list of customer numbers, date of service and Sales Data consisting a list of customer numbers and date of sale and Job Type.

From the Service Data Sheet I added a column with the following formula to answer the question whether or not to customer purchased a product after receiving a service for that day and it works fine.

=CALCULATE(COUNTA('Sales Data'[Job Type]),'Sales Data'[Job Type]="Complete")

What I'm looking for is whether or not the customer purchase a product within 30 days from the date of receiving a service. I tried the following formula:

=CALCULATE(COUNTA('Sales Data'[Job Type]),'Sales Data'[Job Type]="Complete",'Sales Data'[orderdate]>('Service Data'[orderdate]-1))

I received the following error:

The expression contains multiple columns, but only a single column can be used in a Boolean expression that is used as a table filter expression.

Any guidance would be greatly appreciated! Thank you!!


Post your answer or comment

comments powered by Disqus
I am trying to compare the data between two sheets to locate matches. I have
attempted the Countif, Or(exact) and =ISNA(Match) - they all produce
results..but when checking..the results are innaccurate.

For example...Comparing Unigue ID on Sheet 1 to Unigue ID on Sheet 2.

If I use =COUNTIF('sheet1'!AB2:AB210,G2)>0
I get a value of False in all rows...

What should I be using to obtain this information?

Thanks!

Hi All -

So, I'm familiar with sharing data between two sheets using a format like:
=sheet1!A1

However, what I'd like to be able to do is have two cells with the same data so that if I change either one, the other updates also. The value OR the formatting (say I change font color to red. etc.).

An example would be along the lines of a "master list" of customers on sheet1, with columns: Name, Product, Date, Paid (y/n)

On sheet2, I'd like to display a subset of this list, say only the customers with Name=Smith.

But, say on sheet2 I have Bob Smith who is not paid and I want to mark his record to update Paid to "y"... Is there a simple way to do this on sheet2 and have the data on sheet1 be updated? In this case, the data on sheet2 is being "pulled" from sheet1, but is there a way to "push" changes back to the sheet1 data?

I of course also want to be able to make changes to data on sheet1 and have it update on sheet2.

Also, I'd like to be able to apply bold or whatever while working in the subset on sheet2 and have that apply back to the "source" record in sheet1. And vice versa.

Any advice?

Thanks!

Hi

I have som problem matching data between two sheets.

Sheet A is where the data should be filled out based on raw data in sheet B. The only match I have is the JobID number. So If JobID in Sheet A and Sheet B match, copy the values in selected columns from sheet B to A

In some cases it is possible that the raw data (sheet B) has more JobID`s than in sheet B, and if that is true, I would like to add it as a new job in Sheet A (after last row). I have made an example in uploaded sheet that explains it more in details.

Thanks for any help!

Elad

I am trying to compare the data between two sheets to locate matches. I have
attempted the Countif, Or(exact) and =ISNA(Match) - they all produce
results..but when checking..the results are innaccurate.

For example...Comparing Unigue ID on Sheet 1 to Unigue ID on Sheet 2.

If I use =COUNTIF('sheet1'!AB2:AB210,G2)>0
I get a value of False in all rows...

What should I be using to obtain this information?

Thanks!

Hi There,

As a pivotchart cannot represent two views of the same data source unless two pivot tables exist on the spreadsheet, I've created the two object in my file.

However, as the data range I'm interested in is the same for the two charts, I was wondering if it exists an easy way to select the same range in the rowlabel field.

So far I've been able to be successful looping through the master table and assign the same value to the destination table.
It is not elegant, but it works. However, I bet there is something better that is not so obvious.

Can you help?

Thanks
Andrea

I need a formula that would copy and paste between two sheets. The first sheet is the Master Sheet and the second sheet is the Temporary Sheet. I want to be able to automatically paste the current days data from the Master Sheet into the Temporary Sheet and have it update when ever a new row is filled in on the Master Sheet, so the Temporary Sheet, which only consist of a single row, would only roll over when the next days information was entered into the next blank row of the Master Sheet. Any help with this would be greatly appreciated. Below and attached is what I have thus far but I keep receiving a N/A error.

Master_Sheet
A B C D
1 Date Data1 Data2 Data3
2 9/29/2010 3 5 7
3 9/30/2010 8 1 9
4 10/1/2010 5 3 3
5 10/2/2010 7 5 8
6 10/3/2010 10 1 2
7 10/4/2010 10 3 7

Temporary_Sheet
A B C D
1 Date Data1 Data2 Data3
2 10/4/2010 10 3 7



Hi guys, this is my problem.

I have a geographical data (divided into four colums per REGION, PROVINCE, MUNICIPALITY/TOWN, and BARANGAY/STREET) in one sheet. This sheet contains a UNIQUE GEOGRAPHICAL CODE that IS NUMERICAL IN NATURE.

Now, I have also another sheet which lists the same geographical data (divided into four colums per REGION, PROVINCE, MUNICIPALITY/TOWN, and BARANGAY/STREET). The geographic areas included in this sheet are recipients of one government program. But this sheet does not contain the UNIQUE GEOGRAPHICAL CODE.

What I would like to do is to match the geographical data found in two sheets, and if they match, copy/replicate/add the UNIQUE GEOGRAPHICAL CODE found in sheet to the other sheet that does not contain the code.

I would like to include the geographical code, so that it would become the primary key when I create a database in MS Access.

This is the example

SHEET 1
A B C D E
0019 REGION I BASILAN PROVINCE TIPO MUNICIPALITY BORAS STREET

*Column A contains the UNIQUE GEOGRAPHICAL CODE

SHEET 2
A B C D
REGION I BASILAN PROVINCE TIPO MUNICIPALITY BORAS STREET

*Sheet 2 contains only 20k entries of geographical areas while Sheet 1 contains about 40k entries. Only half of the geographical areas are included in the government program

Can you help me?

Hi all,

Does anyopne know how to keep a named range between two dates?, i have a couple of named ranges and for one reason or another interfere with each other when new rows are added, as the new rows that are added contain a date and are sorted by date i was wondering if i could restrict the named ranges to operate between certain dates i.e 1/1/06 - 5/5/06 could be named range 1 etc for all 3 named ranges i have.

Anyone got any ideas?

cheers,
Simon

I need to be able to track changes between two sheets. One will be locked and
the other able to to be edited. I want to turn any cells blue that are
changed and not equal to the static sheet. Conditional formatting won't let
me go between two sheets. Is there any other way to do it? Thanks. D

Hi all,

I am trying to find the range(or may the used range) between two
cells. the situation is as below.

--------------
| First cell |
---------------
The first cell above spans only one colum. Everything that is below
this cell spans over multiple number of columns. The Last cell is also
spans only one column as below.

---------------
| Last Cell |
---------------

Now I am trying to get the used range between these two cells, given
that I have the address for the first cell and the last cell. Thanks in
advance for your help...

Shishi

Efficient Data Transfer Between Two Open Sessons of Excel

I have two sessions of Excel open… Workbooks A in session 1 and B in session 2. Workbook B needs to run by itself since it is using DDE to bring data in from a third party and can not be encumbered by any resource strain, e.g. calculations. Once data has been properly ported into Workbook A, I need to immediately, within a few milliseconds, port the data to Workbook B where some intensive number crunching takes place. Form a resource perspective, this arrangement reaches a good balance.
The challenge is to find the most efficient way of making this transfer of data by crossing over the border from one open session of Excel to another.
I am using Excel 2010, Windows 7 Pro, 64bit. Some trial and error work was done with the following with no luck probably because I am not that famaliar with the protocols:
1) DAO
2) ADO
3) DDE
I am looking for some out-of-the-box solution here that is simple and effective.

Hi,
I am struggeling and I have read many posts similar to this. But noone is covering the same issue.
I need help with a VBA macro which should work as below.

I have two sheets:
Sheet A , where I enter new data manually, which should update empty spaces in Sheet B when triggering macro
Sheet B filled with old data

Basicly I need the data in sheet A to update empty fileds in sheet B.
The common factor between this two sheets is a ref number in column A in both sheets.

So A2 in sheet A should be matched with culomn A in sheet B.

If no match found then colour cell red in sheet A and move on to next row (A3...)
If match found then check if cellE2 or J2 in sheet Ais filled.

If E2 is filled then check if column C,D,E on the matched row is blank in sheet B
If true then copy column B,D,E from sheet A into column C,D,E in sheet B and colour that cell green in sheet A and move on to next row (A3...)
If false then colour that cell red in sheet A and move on to next row (A3...)

If J2 is filled then check if column I,J,K on the matcged row is blank in sheet B
If true then copy column F,H,J from sheet A into column I,J,K in sheet B and colour that cell green in sheet A and move on to next row (A3...)
If false then colour that cell red in sheet A and move on to next row (A3...)

Then keep repeting that until all rows in sheet a is checked.

All help is greatly appreciated

Hi Guys,

I have run into this issue which am not able to solve.Please suggest me a solution.
I have two Hidden sheets namely "Mapping" and "Name"

I also have a userform with a Button "Check Mapping", clicking on which i need to perform search between these two sheets.
Sheet "Mapping" has just one column ( Fixed in all cases).

Content of Column A in sheet "Mapping":

	VB:
	
WIRERES_C3_160nm_Horiz_a~C4~4ptRes@15uA 
WIRERES_C3_160nm_Vert_a~C4~4ptRes@50uA 
WIRERES_C3_200nm_Vert_a~C4~4ptRes@150uA 
....... 
....... 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Sheet "Names" will have data from Cloumn A till G ( Always the same).
I need to search for above shown strings in Column G of sheet "Names".

Content of sheet "Name",Just Column G:

	VB:
	
[V1/constant?V1=WIRERES_M1_108nm_Vert_a~C4~4ptRes@50uA;constant=1000.0 
[V1/constant?V1=WIRERES_M1_160nm_Vert_a~C4~4ptRes@100uA;constant=1000.0 
[V1/constant?V1=WIRERES_M1_180nm_Vert_a~C4~4ptRes@100uA;constant=1000.0 
[V1/constant?V1=WIRERES_M1_32nm_Horiz_iso_a~C4~4ptRes@15uA;constant=1000.0 
[V1/constant?V1=WIRERES_M1_32nm_Vert_b~C4~4ptRes@15uA;constant=1000.0 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So i need to search Column A in sheet "Mapping" with Column G in sheet "Name".
The only condition is the search must match full test and MatchCase.

If the Match is found then that entire row must be marked in Green colour and finally once the complete search is done (All the rows in sheet "Mapping" is matched against sheet "Name)" the Non-green columns shuld be marked in Red at sheet "Names".

Thanx
Ashesh

Dear Gurus,

I've got the issue to compare the data from two sheet. Could you please help on this? My data in two sheet wit hthe same header but the data when input are not match exactly. What I need is to point out the different is where for each sigle row e.g they are different only in one cell vs. other or event two And if they are match perfectly we will color them with green background for example.

Best regards,
TQV

what do you think is the best way to resolve this case?

I have one sheet where are stored the data (pricelist with columns model, description, sale price, cost price). The data are in group like "office furniture", "hospital furniture" etc - I mean there are different range names in sheet with data. I created userforms where I use the data (e.g. useform "Office" use the data from range name "officefurniture"), then I just check some items and after press Enter, the checked items are inserted in another sheet (offer). For example I checked item "cabinet1", then macro start to look up string "cabinet1" in column "model". When the string is found, macro adds the values from columns description, sale price (in entirerow) to the arrays. Values from those arrays are inserted in another sheet (offer). I have thought about the best way to resolve this case.

1. Use an Addin with general macros with reference to other workbook with stored data. E.g.
Addin "AddinOffer" and workbook with data "wbkdata.xls". There is a problem I have to open wbkdata.xls everytime I want to use the data from it (time delay because the size of file with data is 600 KB). The good one is that addin is open automatically by starting excel.
I cannot use Addin for store data, because range "description" is more than 255 chars.

2. Use one workbook, e.g. "OfferHelp.xls" where are stored data and macros too. There is a problem I have open this workbook manually.

I'd like to know your opinion or advices. Thanks.

I am attempting to place a button on each of the two sheets I want to link between. The workbook names are different, but the worksheet names are identical. This works going from one sheet to the other, but I get a Subscript out of range going back to the first sheet.
The first Sub works, the second actually activates the worksheet, but I get the subscript out of range error here. Any ideas? Thanks....

Code:
Option Explicit

Sub HyperLink()
Workbooks("Results Tracking.xls").Activate
Worksheets("Get In Box").Activate
End Sub


Option Explicit

Sub HyperLink()
Workbooks("Shaping Plans.xls").Activate
Worksheets("Get In Box").Activate
End Sub


Hello,
I am using advanced filters to filter out blank rows (using an OR
function) between several columns. I have created a macro that copies
the selected columns(adjacent or non-adjacent)to another sheet and then
performs the filter. This method has worked great, but just recently,
the sheet where I copy all of the columns from is full, so I have to
extend to another. It wouldn't be too much trouble copying the columns
from two sheets into another sheet manually and THEN run the filter
macro, but the criteria range expands over two sheets as well. Is there
a way to expand the criteria range past 256 columns?

-Eddie

--
Lord_Ilpalazo
------------------------------------------------------------------------
Lord_Ilpalazo's Profile: http://www.excelforum.com/member.php...o&userid=25722
View this thread: http://www.excelforum.com/showthread...hreadid=391337

Good afternoon,

My question is a bit hard to explain.. but I will do my best to convey
it.

I have two spreadsheets in an Excel file. In the first sheet I have
"raw data" (hereon called RawData). The second sheet (hereon called
DisplayData) contains basic formulas that just pull the data from
RawData, and arranges them in the way I want them displayed (ultimately
for printing). This excel spreadsheet will be used by laymen who don't
have time (or the understanding) to fool with layouts for their data.
So I am trying to take data, and arrange it into a easily updatable
document. All they will need to do is update the data in RawData, and
DisplayData will reflect the changes for printing. Basically I'm trying
to reduce the redundancies that they've already created.

Creating the links between the two sheets are easy, just hit "=" and
point it to the sheet, then the cell. I set the formula to not change
by using the string prefix before the row and the cell number. (I.e.
='Raw Data'!$E$2). Thats not a problem, I can do THAT much.

The problem is occuring when I insert a row on RawData. It does not
reflect that change on DisplayData. Instead it tries to act "smart" and
updates all of the formulas in DisplayData to make sure that it retains
the correct data and layout (i.e. ='Raw Data'!$E$2 becomes ='Raw
Data'!$E$3 when I want it to stay the same). Well I don't WANT it to
retain the look. When I insert a row in RawData, I want DisplayData to
reflect that, and bump all the data down one row.

How can I stop this "smart" updating from happening? Or is
it not possible? I've heard that this is not possible.

Thanks ahead of time for any advice!

-Chris B

--
FatMagic
------------------------------------------------------------------------
FatMagic's Profile: http://www.excelforum.com/member.php...o&userid=10701
View this thread: http://www.excelforum.com/showthread...hreadid=476270

Hello,
I am using advanced filters to filter out blank rows (using an OR function) between several columns. I have created a macro that copies the selected columns(adjacent or non-adjacent)to another sheet and then performs the filter. This method has worked great, but just recently, the sheet where I copy all of the columns from is full, so I have to extend to another. It wouldn't be too much trouble copying the columns from two sheets into another sheet manually and THEN run the filter macro, but the criteria range expands over two sheets as well. Is there a way to expand the criteria range past 256 columns?

-Eddie

Hello,

Can someone look at the below code and tell me where I am making mistake. I have written this code to match a column between two tabs "Main" and "Backup". If the column number matches, the code should copy the row formatting of that column number from "Backup" sheet to "Main" sheet.

Currently, it is copying the formatting from "Main" sheet to "Main" sheet itself. Dont know where I am making mistake. Thanks.

Sub Rowformat()

Dim Sht1Rng As Range
Dim Sht2Rng As Range
Dim D As Range

'Compares the ID cell in both worksheets to each other

Set Sht1Rng = Worksheets("Main").Range("B6",
Worksheets("Main").Range("B65536").End(xlUp))

Set Sht2Rng = Worksheets("Backup").Range("B6",
Worksheets("Backup").Range("B65536").End(xlUp))

For Each B In Sht1Rng
Set D = Sht2Rng.Find(B.Value, LookIn:=xlValues)

'If same value found in col B of "Backup" sheet then copy
If Not D Is Nothing Then
            
            Sheets("Backup").Select
            Range(Cells(D.Row, 1), Cells(D.Row, 58)).Copy
            
            Sheets("Main").Select
            Range(Cells(D.Row, 1), Cells(D.Row, 58)).PasteSpecial xlPasteFormats
                        
            
End If
Set D = Nothing

Next B

End Sub


I have a sheet for my small business. I want to sum all purchases that have
occurred between two dates i.e. each quarter. I have tried using SUMIF then
specifying for range the date entered, then for the criteria I used say
>1.1.06 AND <1.4.06 then for the sum range the correspoding values for each
date entry. however i justy keep getting an error. Any of you genius' out
there got any ideas please

Hi,

There is any way to stop, the relation between a sheet to other sheet?

I have a sheet "principal" where I have data that can be until 150 different names. In the same workbook I have more seven sheets representing the week days, all the seven sheets are related with "principal" but I only can use 8 "IF's". Normally on the third day I need to change the principal to fill automatically the week day's sheets.

In the end I generate a report, with wrong data, because when I change the "principal", the day one and day two, is also changed.

Can I say to a sheet "with a function or formula", to stop relate with principal, something like "complete and close"????

thanks in advance
João

Good afternoon,

My question is a bit hard to explain.. but I will do my best to convey it.

I have two spreadsheets in an Excel file. In the first sheet I have "raw data" (hereon called RawData). The second sheet (hereon called DisplayData) contains basic formulas that just pull the data from RawData, and arranges them in the way I want them displayed (ultimately for printing). This excel spreadsheet will be used by laymen who don't have time (or the understanding) to fool with layouts for their data. So I am trying to take data, and arrange it into a easily updatable document. All they will need to do is update the data in RawData, and DisplayData will reflect the changes for printing. Basically I'm trying to reduce the redundancies that they've already created.

Creating the links between the two sheets are easy, just hit "=" and point it to the sheet, then the cell. I set the formula to not change by using the string prefix before the row and the cell number. (I.e. ='Raw Data'!$E$2). Thats not a problem, I can do THAT much.

The problem is occuring when I insert a row on RawData. It does not reflect that change on DisplayData. Instead it tries to act "smart" and updates all of the formulas in DisplayData to make sure that it retains the correct data and layout (i.e. ='Raw Data'!$E$2 becomes ='Raw Data'!$E$3 when I want it to stay the same). Well I don't WANT it to retain the look. When I insert a row in RawData, I want DisplayData to reflect that, and bump all the data down one row.

How can I stop this "smart" updating from happening? Or is it not possible? I've heard that this is not possible.

Thanks ahead of time for any advice!

-Chris B

I am looking for assistance in having two fields in an existing userform of mine place data into two separate worksheets but in the same workbook. Currently the data in my userform places data in various columns on a sheet labeled "2010". I would like my userform field data for "assigned" and "status" to remain in the same place for the 2010 sheet, but to be also placed in another sheet labeled "Assignment"
In the "Assignment" sheet the "assigned" data would begin in column C, and the "Status" data would be in column D. It would be preferred to have the data placed in these columns, but in the corresponding row number from the 2010 sheet. I hope this makes sense and is possible. Here is my existing code which is affiliated to my current user form, which places the data in the 2010 sheet only. Thanks in advance for any assistance and/or time looking at this!
I've attached a sample workbook to hopefully help with understanding what I'm trying to do. Thanks again!

Private Sub CommandButton1_Click()
    Dim NextRw As Long
    If CboNames.Value = "" Then MsgBox "You need to pick a worksheet to save data to!"
        On Error Resume Next
        Set ws = Worksheets(Me.CboNames.Value)
    With ws
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = Me.CboWeek.Value
        .Cells(NextRw, 2).Value = Me.CboMonth.Value
        .Cells(NextRw, 3).Value = Me.CboDay.Value
        .Cells(NextRw, 4).Value = Me.CboYear.Value
        .Cells(NextRw, 5).Value = Me.TxtVictimName.Value
        .Cells(NextRw, 6).Value = Me.TxtAddressNum.Value
        .Cells(NextRw, 7).Value = Me.TxtAddressStreet.Value
        .Cells(NextRw, 8).Value = Me.CboCitybox.Value
        .Cells(NextRw, 9).Value = Me.CboStatebox.Value
        .Cells(NextRw, 10).Value = Me.CboZipCode.Value
        .Cells(NextRw, 11).Value = Me.CboCrime.Value
        .Cells(NextRw, 12).Value = Me.CboAreabox.Value
        .Cells(NextRw, 13).Value = Me.CboTimebox.Value
        .Cells(NextRw, 14).Value = Me.TxtProperty.Value
        .Cells(NextRw, 15).Value = Me.TxtRecovered.Value
        .Cells(NextRw, 16).Value = Me.TxtLost.Value
        .Cells(NextRw, 17).Value = Me.CboAssigned.Value
        .Cells(NextRw, 18).Value = Me.CboStatus.Value
        .Cells(NextRw, 19).Value = Me.CboCharge.Value
        .Cells(NextRw, 20).Value = Me.CboGang.Value
        .Cells(NextRw, 21).Value = Me.CboArrest.Value
        .Cells(NextRw, 22).Value = Me.TxtComplaint.Value
        .Cells(NextRw, 23).Value = Me.CboComplaintYear.Value
        .Cells(NextRw, 24).Value = Me.CboDetective1.Value
        .Cells(NextRw, 25).Value = Me.CboDetective2.Value
        .Cells(NextRw, 26).Value = Me.CboDetective3.Value
        .Cells(NextRw, 27).Value = Me.CboDetective4.Value
        .Cells(NextRw, 28).Value = Me.CboDetective5.Value
        .Cells(NextRw, 29).Value = Me.CboPrints.Value
        .Cells(NextRw, 30).Value = Me.TxtBusiness.Value
        .Cells(NextRw, 31).Value = Me.TxtRemarks.Value
        .Cells(NextRw, 68).Value = Me.TxtDate.Value
        .Cells(NextRw, 69).Value = Me.CboCrimetwo.Value
        .Cells(NextRw, 70).Value = Me.CboWeapons.Value
    End With

Call CmdRefresh_Click

End Sub

Private Sub Label32_Click()

End Sub

Private Sub Label34_Click()

End Sub

Private Sub TxtAddressNum_Change()

End Sub

Private Sub TxtAddressStreet_Change()

End Sub

Private Sub TxtDate_Change()

End Sub

Private Sub TxtLost_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me
If Len(.TxtLost.Value) > 0 Then .TxtLost.Value = Format(TxtLost.Value, _
    "Currency")
End With
End Sub

Private Sub TxtRecovered_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me
If Len(.TxtRecovered.Value) > 0 Then .TxtRecovered.Value = Format(TxtRecovered.Value, _
    "Currency")
End With
End Sub

Private Sub TxtRemarks_Change()

End Sub

Private Sub UserForm_Initialize()
    Me.CboNames.List = Array("2010")

    With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 49), .Cells(.Rows.Count, 49).End(xlUp))
        Me.CboWeek.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 51), .Cells(.Rows.Count, 51).End(xlUp))
        Me.CboMonth.List = rng2.Value
    End With
       With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 50), .Cells(.Rows.Count, 50).End(xlUp))
        Me.CboDay.List = rng2.Value
    End With
       With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 52), .Cells(.Rows.Count, 52).End(xlUp))
        Me.CboYear.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 63), .Cells(.Rows.Count, 63).End(xlUp))
        Me.CboCitybox.List = rng2.Value
    End With
       With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 54), .Cells(.Rows.Count, 54).End(xlUp))
        Me.CboStatebox.List = rng2.Value
    End With
       With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 64), .Cells(.Rows.Count, 64).End(xlUp))
        Me.CboZipCode.List = rng2.Value
    End With
       With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 56), .Cells(.Rows.Count, 56).End(xlUp))
        Me.CboCrime.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 56), .Cells(.Rows.Count, 56).End(xlUp))
        Me.CboCrimetwo.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 57), .Cells(.Rows.Count, 57).End(xlUp))
        Me.CboAreabox.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 58), .Cells(.Rows.Count, 58).End(xlUp))
        Me.CboTimebox.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 59), .Cells(.Rows.Count, 59).End(xlUp))
        Me.CboAssigned.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 60), .Cells(.Rows.Count, 60).End(xlUp))
        Me.CboStatus.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 61), .Cells(.Rows.Count, 61).End(xlUp))
        Me.CboCharge.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 62), .Cells(.Rows.Count, 62).End(xlUp))
        Me.CboGang.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 50), .Cells(.Rows.Count, 50).End(xlUp))
        Me.CboArrest.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 52), .Cells(.Rows.Count, 52).End(xlUp))
        Me.CboComplaintYear.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 59), .Cells(.Rows.Count, 59).End(xlUp))
        Me.CboDetective1.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 59), .Cells(.Rows.Count, 59).End(xlUp))
        Me.CboDetective2.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 59), .Cells(.Rows.Count, 59).End(xlUp))
        Me.CboDetective3.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 59), .Cells(.Rows.Count, 59).End(xlUp))
        Me.CboDetective4.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 59), .Cells(.Rows.Count, 59).End(xlUp))
        Me.CboDetective5.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 72), .Cells(.Rows.Count, 72).End(xlUp))
        Me.CboPrints.List = rng2.Value
    End With
        With Sheets("2010")
       Set rng2 = .Range(.Cells(1, 71), .Cells(.Rows.Count, 71).End(xlUp))
        Me.CboWeapons.List = rng2.Value
    End With
End Sub



No luck finding an answer? You could always try Google.