Search a worksheet, extract rows using a list from another sheet

I am trying to use a list of names (first, last) in a colum in worksheetA to 
find corresponding entries in an imported worksheet B,  Once I find the name 
in worksheet B I want to extract the entire row of data into another new 
worksheet.

For example
Worksheet A:
Column A
Bill Jones
Fred Smith
           
Worksheet B:
Place  Overall    Name                                         Swim     Bike 
    Run     Finish
1        12          Bill Jones 24 anytown ST              13:45     54:45   
23:00  1:34:45
4         78         Fred Smith 56 anothertown ST      15:00    56:12   
24:34  1:48:34

The data in worksheet B in the Name column is all in the same cell (not 
different columns)

Now if there is a match between worksheetA name and the name exists in 
worksheet B I want to copy the entire row from worksheet B into a new row in 
Worksheet C.  Not all names in worksheet A will have an entry in worksheet B

Any ideas?
0
BobF (10)
8/29/2005 8:53:36 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
580 Views

Similar Articles

[PageSpeed] 7

One play ..

In sheet: A
-----------
Names are listed in col A, in A1 down

In sheet: B
-----------
Assume the table is in cols A to I, data from row2 down
Names are assumed to be in col C, from C2 down

Use an empty column to the right of the table, say col K
Put in K2: =IF(ISNUMBER(MATCH(C2,A!A:A,0)),ROW(),"")
Copy K2 down to say, K100, to cover the max expected data
(Leave K1 empty)

In sheet: C
----------
Copy > paste the same col headers over from sheet B

Put in A2:
=IF(ISERROR(SMALL(B!$K:$K,ROWS($A$1:A1))),"",INDEX(B!A:A,MATCH(SMALL(B!$K:$K
,ROWS($A$1:A1)),B!$K:$K,0)))

Copy A2 across to I2, fill down to I100
(cover the same range size as done in col K in sheet B)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bobf" <bobf@discussions.microsoft.com> wrote in message
news:2CCA514B-7DC7-4377-A440-4100443C610A@microsoft.com...
> I am trying to use a list of names (first, last) in a colum in worksheetA
to
> find corresponding entries in an imported worksheet B,  Once I find the
name
> in worksheet B I want to extract the entire row of data into another new
> worksheet.
>
> For example
> Worksheet A:
> Column A
> Bill Jones
> Fred Smith
>
> Worksheet B:
> Place  Overall    Name                                         Swim
Bike
>     Run     Finish
> 1        12          Bill Jones 24 anytown ST              13:45     54:45
> 23:00  1:34:45
> 4         78         Fred Smith 56 anothertown ST      15:00    56:12
> 24:34  1:48:34
>
> The data in worksheet B in the Name column is all in the same cell (not
> different columns)
>
> Now if there is a match between worksheetA name and the name exists in
> worksheet B I want to copy the entire row from worksheet B into a new row
in
> Worksheet C.  Not all names in worksheet A will have an entry in worksheet
B
>
> Any ideas?


0
demechanik (4694)
8/29/2005 10:28:08 PM
Here's a link to a demo file with the implemented set-up:
http://www.savefile.com/files/5254674
File:  Search_n_extract_rows_using_list_from_anothersheet.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


0
demechanik (4694)
8/30/2005 1:01:51 AM
Bob f,  you really didn't specify if wanted formula or vba code to solve
your problem. Here is alittle bit of vba code as macro that should solve
your problem.  This macro can be run fom any sheet.


rgds to ya

Rick

Public Sub FindNameMoveData()
Dim A As Worksheet, B As Worksheet, C As Worksheet
Dim iArow As Integer, iBrow As Integer, iCrow As Integer
Dim Name As String

                        ' set object pointer for each sheet "A","B","C"
Set A = Worksheets("A")
Set B = Worksheets("B")
Set C = Worksheets("C")


   iArow = 1            ' set beginning row position
   iBrow = 2
   iCrow = 2

   Application.ScreenUpdating = False    ' stop screenupdate
   Do Until IsEmpty(A.Cells(iArow, "A")) ' loop thru names on sheet "A"
      Name = A.Cells(iArow, "A")         ' save name for compare
         Do Until IsEmpty(B.Cells(iBrow, "C")) ' loop thru names on sheet
"B"
            If Name = B.Cells(iBrow, "C") Then ' find same name in sheet "B"
               ' copy from sheet "B"
               B.Range("A" & CStr(iBrow) & ":" & "I" & CStr(iBrow)).Copy
               ' paste to sheet "C"
               C.Range("A" & CStr(iCrow) & ":" & "I" &
CStr(iCrow)).PasteSpecial (xlPasteValues)
               ' increment sheet "C" row pointer
               iCrow = iCrow + 1
               Exit Do          ' name was found, exit this loop
            End If
            iBrow = iBrow + 1
         Loop
         iBrow = 2
         iArow = iArow + 1
   Loop
   Application.CutCopyMode = False   ' clear clipbroad

End Sub  ' we B done


"bobf" <bobf@discussions.microsoft.com> wrote in message
news:2CCA514B-7DC7-4377-A440-4100443C610A@microsoft.com...
> I am trying to use a list of names (first, last) in a colum in worksheetA
to
> find corresponding entries in an imported worksheet B,  Once I find the
name
> in worksheet B I want to extract the entire row of data into another new
> worksheet.
>
> For example
> Worksheet A:
> Column A
> Bill Jones
> Fred Smith
>
> Worksheet B:
> Place  Overall    Name                                         Swim
Bike
>     Run     Finish
> 1        12          Bill Jones 24 anytown ST              13:45     54:45
> 23:00  1:34:45
> 4         78         Fred Smith 56 anothertown ST      15:00    56:12
> 24:34  1:48:34
>
> The data in worksheet B in the Name column is all in the same cell (not
> different columns)
>
> Now if there is a match between worksheetA name and the name exists in
> worksheet B I want to copy the entire row from worksheet B into a new row
in
> Worksheet C.  Not all names in worksheet A will have an entry in worksheet
B
>
> Any ideas?


0
rlhansen (6)
8/30/2005 5:38:22 AM
Thanks Max, I'll try that today to see if it's what I want.

"Max" wrote:

> Here's a link to a demo file with the implemented set-up:
> http://www.savefile.com/files/5254674
> File:  Search_n_extract_rows_using_list_from_anothersheet.xls
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> 
> 
> 
0
BobF (10)
8/30/2005 1:09:13 PM
At this point I'll take either.  Thanks for your reply.  I'll try your VBA 
code to see if it's what I am looking for.

Thanks

"Rick Hansen" wrote:

> Bob f,  you really didn't specify if wanted formula or vba code to solve
> your problem. Here is alittle bit of vba code as macro that should solve
> your problem.  This macro can be run fom any sheet.
> 
> 
> rgds to ya
> 
> Rick
> 
> Public Sub FindNameMoveData()
> Dim A As Worksheet, B As Worksheet, C As Worksheet
> Dim iArow As Integer, iBrow As Integer, iCrow As Integer
> Dim Name As String
> 
>                         ' set object pointer for each sheet "A","B","C"
> Set A = Worksheets("A")
> Set B = Worksheets("B")
> Set C = Worksheets("C")
> 
> 
>    iArow = 1            ' set beginning row position
>    iBrow = 2
>    iCrow = 2
> 
>    Application.ScreenUpdating = False    ' stop screenupdate
>    Do Until IsEmpty(A.Cells(iArow, "A")) ' loop thru names on sheet "A"
>       Name = A.Cells(iArow, "A")         ' save name for compare
>          Do Until IsEmpty(B.Cells(iBrow, "C")) ' loop thru names on sheet
> "B"
>             If Name = B.Cells(iBrow, "C") Then ' find same name in sheet "B"
>                ' copy from sheet "B"
>                B.Range("A" & CStr(iBrow) & ":" & "I" & CStr(iBrow)).Copy
>                ' paste to sheet "C"
>                C.Range("A" & CStr(iCrow) & ":" & "I" &
> CStr(iCrow)).PasteSpecial (xlPasteValues)
>                ' increment sheet "C" row pointer
>                iCrow = iCrow + 1
>                Exit Do          ' name was found, exit this loop
>             End If
>             iBrow = iBrow + 1
>          Loop
>          iBrow = 2
>          iArow = iArow + 1
>    Loop
>    Application.CutCopyMode = False   ' clear clipbroad
> 
> End Sub  ' we B done
> 
> 
> "bobf" <bobf@discussions.microsoft.com> wrote in message
> news:2CCA514B-7DC7-4377-A440-4100443C610A@microsoft.com...
> > I am trying to use a list of names (first, last) in a colum in worksheetA
> to
> > find corresponding entries in an imported worksheet B,  Once I find the
> name
> > in worksheet B I want to extract the entire row of data into another new
> > worksheet.
> >
> > For example
> > Worksheet A:
> > Column A
> > Bill Jones
> > Fred Smith
> >
> > Worksheet B:
> > Place  Overall    Name                                         Swim
> Bike
> >     Run     Finish
> > 1        12          Bill Jones 24 anytown ST              13:45     54:45
> > 23:00  1:34:45
> > 4         78         Fred Smith 56 anothertown ST      15:00    56:12
> > 24:34  1:48:34
> >
> > The data in worksheet B in the Name column is all in the same cell (not
> > different columns)
> >
> > Now if there is a match between worksheetA name and the name exists in
> > worksheet B I want to copy the entire row from worksheet B into a new row
> in
> > Worksheet C.  Not all names in worksheet A will have an entry in worksheet
> B
> >
> > Any ideas?
> 
> 
> 
0
BobF (10)
8/30/2005 1:10:03 PM
Max, I tried your download and it appears to work except for one thing.  The 
column of names in the worksheet that I am trying to search is not just 
names.  It MAY be just names but it may also be a first name, last name, age, 
city and state, all in the same column.  so now somehow I have to search that 
column and be able to filter out the rest of the information for the search.

Hope that makes sense

Thanks again

"Max" wrote:

> Here's a link to a demo file with the implemented set-up:
> http://www.savefile.com/files/5254674
> File:  Search_n_extract_rows_using_list_from_anothersheet.xls
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> 
> 
> 
0
BobF (10)
8/30/2005 1:22:04 PM
In sheet: B
------
Try changing the formula in K2 to:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A!$A$1:$A$100,C2))*(A!$A$1:$A$100<>""))=1,R
OW(),"")

Copy K2 down to K100, as before

Adapt the range: A!$A$1:$A$100 in the formula to suit
(use the smallest possible range)

(No change to formulas in sheet C)

Now it should work even if you have, say:

Bill Jones 24 anytown ST
Fred Smith 56 anothertown ST
etc

instead of just the names:

Bill Jones
Fred Smith
etc

within col C in sheet: B
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bobf" <bobf@discussions.microsoft.com> wrote in message
news:8282EF72-9C53-4FA2-9E99-03075BEBA067@microsoft.com...
> Max, I tried your download and it appears to work except for one thing.
The
> column of names in the worksheet that I am trying to search is not just
> names.  It MAY be just names but it may also be a first name, last name,
age,
> city and state, all in the same column.  so now somehow I have to search
that
> column and be able to filter out the rest of the information for the
search.
>
> Hope that makes sense
>
> Thanks again


0
demechanik (4694)
8/30/2005 4:21:13 PM
Here's the link to the revised demo file (just in case needed):
http://www.savefile.com/files/4670322
File:  Search_n_extract_rows_using_list_from_othersht_v2

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


0
demechanik (4694)
8/31/2005 1:50:23 AM
Hello again Bobf,
  After reading yours and max's threads, I modified my vba code now to
search  name in column C as you originaly requested (see code below). Maybe
this code will do you better.  if have any questions email me at
rlhansen73@yahoo.com .

    HTH
  Rick

Public Sub FindNameMoveData3()
Dim A As Worksheet, B As Worksheet, C As Worksheet
Dim iArow As Integer, iBrow As Integer, iCrow As Integer
Dim x As Integer
Dim Name As String, CkName As String

                        ' set object pointer for each sheet "A","B","C"
Set A = Worksheets("A")
Set B = Worksheets("B")
Set C = Worksheets("C")


   iArow = 1            ' set beginning row position
   iBrow = 2
   iCrow = 2

   Application.ScreenUpdating = False    ' stop screenupdate
   Do Until IsEmpty(A.Cells(iArow, "A")) ' loop thru names on sheet "A"
      Name = A.Cells(iArow, "A")         ' save name for compare
         Do Until IsEmpty(B.Cells(iBrow, "C")) ' loop thru names on sheet
"B"
           CkName = B.Cells(iBrow, "C")  ' get name,address, etc
           For x = 1 To Len(CkName)
             ' search and compare names
             If Name = Mid(CkName, x, Len(Name)) Then
               ' copy from sheet "B"
               B.Range("A" & CStr(iBrow) & ":" & "I" & CStr(iBrow)).Copy
               ' paste to sheet "C"
               C.Range("A" & CStr(iCrow) & ":" & "I" &
CStr(iCrow)).PasteSpecial (xlPasteValues)
               ' increment sheet "C" row pointer
               iCrow = iCrow + 1
               Exit Do          ' name was found, exit this loop
             End If
           Next x
           iBrow = iBrow + 1
         Loop
         iBrow = 2
         iArow = iArow + 1
   Loop
   Application.CutCopyMode = False   ' clear clipbroad

End Sub  ' we B done




"bobf" <bobf@discussions.microsoft.com> wrote in message
news:80BC7D24-E982-4728-A1A8-A893AFC49FD3@microsoft.com...
> At this point I'll take either.  Thanks for your reply.  I'll try your VBA
> code to see if it's what I am looking for.
>
> Thanks
>
> "Rick Hansen" wrote:
>
> > Bob f,  you really didn't specify if wanted formula or vba code to solve
> > your problem. Here is alittle bit of vba code as macro that should solve
> > your problem.  This macro can be run fom any sheet.
> >
> >
> > rgds to ya
> >
> > Rick
> >
> > Public Sub FindNameMoveData()
> > Dim A As Worksheet, B As Worksheet, C As Worksheet
> > Dim iArow As Integer, iBrow As Integer, iCrow As Integer
> > Dim Name As String
> >
> >                         ' set object pointer for each sheet "A","B","C"
> > Set A = Worksheets("A")
> > Set B = Worksheets("B")
> > Set C = Worksheets("C")
> >
> >
> >    iArow = 1            ' set beginning row position
> >    iBrow = 2
> >    iCrow = 2
> >
> >    Application.ScreenUpdating = False    ' stop screenupdate
> >    Do Until IsEmpty(A.Cells(iArow, "A")) ' loop thru names on sheet "A"
> >       Name = A.Cells(iArow, "A")         ' save name for compare
> >          Do Until IsEmpty(B.Cells(iBrow, "C")) ' loop thru names on
sheet
> > "B"
> >             If Name = B.Cells(iBrow, "C") Then ' find same name in sheet
"B"
> >                ' copy from sheet "B"
> >                B.Range("A" & CStr(iBrow) & ":" & "I" & CStr(iBrow)).Copy
> >                ' paste to sheet "C"
> >                C.Range("A" & CStr(iCrow) & ":" & "I" &
> > CStr(iCrow)).PasteSpecial (xlPasteValues)
> >                ' increment sheet "C" row pointer
> >                iCrow = iCrow + 1
> >                Exit Do          ' name was found, exit this loop
> >             End If
> >             iBrow = iBrow + 1
> >          Loop
> >          iBrow = 2
> >          iArow = iArow + 1
> >    Loop
> >    Application.CutCopyMode = False   ' clear clipbroad
> >
> > End Sub  ' we B done
> >
> >
> > "bobf" <bobf@discussions.microsoft.com> wrote in message
> > news:2CCA514B-7DC7-4377-A440-4100443C610A@microsoft.com...
> > > I am trying to use a list of names (first, last) in a colum in
worksheetA
> > to
> > > find corresponding entries in an imported worksheet B,  Once I find
the
> > name
> > > in worksheet B I want to extract the entire row of data into another
new
> > > worksheet.
> > >
> > > For example
> > > Worksheet A:
> > > Column A
> > > Bill Jones
> > > Fred Smith
> > >
> > > Worksheet B:
> > > Place  Overall    Name                                         Swim
> > Bike
> > >     Run     Finish
> > > 1        12          Bill Jones 24 anytown ST              13:45
54:45
> > > 23:00  1:34:45
> > > 4         78         Fred Smith 56 anothertown ST      15:00    56:12
> > > 24:34  1:48:34
> > >
> > > The data in worksheet B in the Name column is all in the same cell
(not
> > > different columns)
> > >
> > > Now if there is a match between worksheetA name and the name exists in
> > > worksheet B I want to copy the entire row from worksheet B into a new
row
> > in
> > > Worksheet C.  Not all names in worksheet A will have an entry in
worksheet
> > B
> > >
> > > Any ideas?
> >
> >
> >


0
rlhansen (6)
8/31/2005 3:56:35 AM
Reply:

Similar Artilces:

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Outlook 2007 Search always returns no matches
Any search I do in Outlook 2007's new search box returns no matches. I watched the Indexing Status as it progressed, and now it says "Outlook has finished indexing all of your items" But, searching on anything, even the word "The" or "a" returns no results. I'm searching all Outlook items. I'm on an Exchange Server via VPN. Anyone have any suggestions? I uninstalled Office and re-installed and the problem went away... "Tom" <none@none.com> wrote in message news:%23yGP6NPOHHA.2140@TK2MSFTNGP03.phx.gbl... > Any search I do i...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...

Contacts Search
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Email Client: pop I recently deleted some vital Microsoft files, I reinstalled the package and my backup emails, contacts etc, the only problem is that if I do a quick search in contacts it brings up totally unrelated contact and not the contact I was originally looking for ?? On 1/31/10 4:01 AM, in article 59bb1f13.-1@webcrossing.JaKIaxP2ac0, "David_Marriott@officeformac.com" <David_Marriott@officeformac.com> wrote: > I recently deleted some vital Microsoft files, I reinstalled the package and > my backu...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Search FRustration
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Someone sends me an email from: <br><br>Headshot Newsletter <jessesverito@verizon.net> <br><br>I talk the person on phone. I get their email. <br> I want to now delete them out of my newsletter folder. <br> I search the email and nothing comes up. Is it becuase <br> Headshot Newsletter preceedes the email? <br> What poor search fuction no? Spotlight would nail it. <br> anything I can do? On 2010-01-23 17:55:39 -0500, kevs12@of...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Sorting by file extension in a worksheet
Hello, I have an excel worksheet which has a list of file names from a directory and various stats about each file. I need to be able to sort them by the file extension. Eg. c:\documents\folderA\picture1.eps c:\documents\folderB\document.doc I would like to sort them by the ".eps" extension. I have tried using the Data,Sort menu and something like *?*.eps as the criteria. But am obvisouly missing something. Any help would be most appreciated. Thanks Karl You could use a helper column to extract just the extension then include this in your sort range and sort on the helper col...

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

WLM transfer to another computer
Hi, I finally moved from Windows 7 RTM to Win7 Pro 64. I did it by installing the new OS on a brand new hard drive, then installed my old hard drive in a 2.5" external enclosure. I've been successful in moving most of my files and settings over, but WLM is the exception. Can someone help answer these questions for me: 1. Where are the actual mail files stored? 2. Where is the account login info stored? 3. In Outlook and OE installing on a new computer, even after moving files, prompted for a full redownload off of the POP server. Anyway to avoid this? Is ther...