Look up Data from Worksheet within same Workbook

Hi All,

Sorry, this may seem like an elementary question but....

First of all I have three seperate worksheets containing data within my
workbook.

I want to return all rows from each of those worksheets that matches a
certain criteria to another worksheet, without having to manipulate the
data.

e.g.
I have charge codes beginning in 1-90XX, 1-91xx, etc...
These appear multiple times in the worksheets that contain data
I want to search sheets 1 - 3 to see if any rows contain them & if they
do, I want all these complete rows to appear in another worksheet
within that same workbook.



VLOOKUP function will not work for me as the charge code is in column O
& I can only get it to return data within that row for column O onward.

Also, I am unsure if you can get VLOOKUP to check for multiple
conditions

* What function should I be using?
* Can you use VLOOKUP in a nested statement to search for the various
conditions(charge codes)?
* Can VLOOKUP return a whole row, without having to specify the column?
* Can VLOOKUP return data previous to the column where the criteria of
the search is met?

* Finally: Should I try to use Macros & if so, can someone point me in
the right direction???


Thanks,

Aine

0
aineog1 (2)
5/10/2006 12:56:11 AM
excel 39879 articles. 2 followers. Follow

8 Replies
406 Views

Similar Articles

[PageSpeed] 22

Short answers:
Yes - you can use VLOOKUP() within nested (IF) statements.
No - VLOOKUP() only returns a single value out of the matched row
No - VLOOKUP() can't return data to the left of the column the match is 
sought in, But!! the LOOKUP() function can do that.

All of the various lookups (HLOOKUP, VLOOKUP and LOOKUP) are pretty much 
limited to finding the first entry meeting the lookup parameters, although 
LOOKUP can be used to "You can also use the LOOKUP function as an alternative 
the IF function for elaborate tests or tests for more than seven conditions. 
See the examples in the array form." - from the Help topic on LOOKUP

But in your case, I think that a macro is probably going to be the better 
way to develop a solution since you want to return entire rows and you need 
to look for mulitple occurances of the same value in your data.

Not knowing how familiar you are with writing macros or coding in general, 
it's difficult to point you to far.  But I'll give a rough logic flow for you:

The could would have to go to the sheet with the list of charge codes to 
find on the other sheets and then loop through all cells containing those 
charge codes, on at a time, saving the contents for comparisons on the other 
sheets.  Then for each sheet with data to be matched it would go to the 
beginning of the list and look through it for matches and on each match, copy 
that row to a sheet designated to be used to receive those rows of 
information, then move to next sheet and repeat and once it has examined all 
sheets, then move to the next cell on your charge code sheet, get the next 
lookup value and work through the other sheets from beginning to end again.  
This could be quite time consuming if you have really long lists.

Do you want to give the macro solution a shot?


"Aine" wrote:

> Hi All,
> 
> Sorry, this may seem like an elementary question but....
> 
> First of all I have three seperate worksheets containing data within my
> workbook.
> 
> I want to return all rows from each of those worksheets that matches a
> certain criteria to another worksheet, without having to manipulate the
> data.
> 
> e.g.
> I have charge codes beginning in 1-90XX, 1-91xx, etc...
> These appear multiple times in the worksheets that contain data
> I want to search sheets 1 - 3 to see if any rows contain them & if they
> do, I want all these complete rows to appear in another worksheet
> within that same workbook.
> 
> 
> 
> VLOOKUP function will not work for me as the charge code is in column O
> & I can only get it to return data within that row for column O onward.
> 
> Also, I am unsure if you can get VLOOKUP to check for multiple
> conditions
> 
> * What function should I be using?
> * Can you use VLOOKUP in a nested statement to search for the various
> conditions(charge codes)?
> * Can VLOOKUP return a whole row, without having to specify the column?
> * Can VLOOKUP return data previous to the column where the criteria of
> the search is met?
> 
> * Finally: Should I try to use Macros & if so, can someone point me in
> the right direction???
> 
> 
> Thanks,
> 
> Aine
> 
> 
0
JLatham (219)
5/10/2006 3:43:01 AM
Should have been...
"The code would have to...", not "The could would have to..."

"JLatham" wrote:

> Short answers:
> Yes - you can use VLOOKUP() within nested (IF) statements.
> No - VLOOKUP() only returns a single value out of the matched row
> No - VLOOKUP() can't return data to the left of the column the match is 
> sought in, But!! the LOOKUP() function can do that.
> 
> All of the various lookups (HLOOKUP, VLOOKUP and LOOKUP) are pretty much 
> limited to finding the first entry meeting the lookup parameters, although 
> LOOKUP can be used to "You can also use the LOOKUP function as an alternative 
> the IF function for elaborate tests or tests for more than seven conditions. 
> See the examples in the array form." - from the Help topic on LOOKUP
> 
> But in your case, I think that a macro is probably going to be the better 
> way to develop a solution since you want to return entire rows and you need 
> to look for mulitple occurances of the same value in your data.
> 
> Not knowing how familiar you are with writing macros or coding in general, 
> it's difficult to point you to far.  But I'll give a rough logic flow for you:
> 
> The could would have to go to the sheet with the list of charge codes to 
> find on the other sheets and then loop through all cells containing those 
> charge codes, on at a time, saving the contents for comparisons on the other 
> sheets.  Then for each sheet with data to be matched it would go to the 
> beginning of the list and look through it for matches and on each match, copy 
> that row to a sheet designated to be used to receive those rows of 
> information, then move to next sheet and repeat and once it has examined all 
> sheets, then move to the next cell on your charge code sheet, get the next 
> lookup value and work through the other sheets from beginning to end again.  
> This could be quite time consuming if you have really long lists.
> 
> Do you want to give the macro solution a shot?
> 
> 
> "Aine" wrote:
> 
> > Hi All,
> > 
> > Sorry, this may seem like an elementary question but....
> > 
> > First of all I have three seperate worksheets containing data within my
> > workbook.
> > 
> > I want to return all rows from each of those worksheets that matches a
> > certain criteria to another worksheet, without having to manipulate the
> > data.
> > 
> > e.g.
> > I have charge codes beginning in 1-90XX, 1-91xx, etc...
> > These appear multiple times in the worksheets that contain data
> > I want to search sheets 1 - 3 to see if any rows contain them & if they
> > do, I want all these complete rows to appear in another worksheet
> > within that same workbook.
> > 
> > 
> > 
> > VLOOKUP function will not work for me as the charge code is in column O
> > & I can only get it to return data within that row for column O onward.
> > 
> > Also, I am unsure if you can get VLOOKUP to check for multiple
> > conditions
> > 
> > * What function should I be using?
> > * Can you use VLOOKUP in a nested statement to search for the various
> > conditions(charge codes)?
> > * Can VLOOKUP return a whole row, without having to specify the column?
> > * Can VLOOKUP return data previous to the column where the criteria of
> > the search is met?
> > 
> > * Finally: Should I try to use Macros & if so, can someone point me in
> > the right direction???
> > 
> > 
> > Thanks,
> > 
> > Aine
> > 
> > 
0
JLatham (219)
5/10/2006 3:50:01 AM
Here's one play using non-array formulas which might also deliver this ..

A sample construct is available at:
http://www.savefile.com/files/9870225
Multiple search & auto-extract rows from 3 sheets based on key charge code 
col.xls

Assume source data is in Sheets 1 to 3 (identically structured)
data within cols A to J, from row6 down to say, 
a max expected row15 (Col headers in row5) 
The key charge codes are assumed in col A

In Sheet1,

Put in K6
=IF(A6="","",IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A$1:$A$3,A6))*(Summ!$A$1:$A$3<>""))>0,ROW(A1),""))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet2

Put in K6
=IF(A6="","",IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A$1:$A$3,A6))*(Summ!$A$1:$A$3<>""))>0,ROW(A1)+MAX(Sheet1!K:K),""))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet3

Put in K6
=IF(A6="","",IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A$1:$A$3,A6))*(Summ!$A$1:$A$3<>""))>0,ROW(A1)+MAX(Sheet2!K:K),""))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In a new sheet: Summ,

Entry of the charge code(s) to search will be made within A1:A3 (can be in 
any order). To facilitate search input(s), format A1:A3 as text (Format > 
Cells > Text)
Input 2 charge codes into A1:A2, say: 1-90, 1-91

Place the same col labels in A5:J5

Then put in A6:

=IF(ISERROR(SMALL(Sheet1!$K:$K,ROW(A1))),
IF(ISERROR(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K)))
IF(ISERROR(SMALL(Sheet3!$K:$K,ROW(A1)-(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K)))),""
INDEX(Sheet3!A:A,MATCH(SMALL(Sheet3!$K:$K,ROW(A1)-(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K))),Sheet3!$K:$K,0)))
INDEX(Sheet2!A:A,MATCH(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K)),Sheet2!$K:$K,0))),
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0)))

Copy A6 across to J6, fill down to cover the *total* expected range in the 3 
sheets, ie to J35 (in this example, the expected max data range is: 10 rows 
per sheet x 3 sheets = 30 rows)

The auto-extracted results from Sheets 1 to 3 will be returned within 
A6:J35, all neatly bunched at the top. Extracted lines will be listed in the 
order: Lines from Sheet1, then those from Sheet2, then those from Sheet3. 
Adapt to suit ..
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aine" wrote:
> Hi All,
> 
> Sorry, this may seem like an elementary question but....
> 
> First of all I have three seperate worksheets containing data within my
> workbook.
> 
> I want to return all rows from each of those worksheets that matches a
> certain criteria to another worksheet, without having to manipulate the
> data.
> 
> e.g.
> I have charge codes beginning in 1-90XX, 1-91xx, etc...
> These appear multiple times in the worksheets that contain data
> I want to search sheets 1 - 3 to see if any rows contain them & if they
> do, I want all these complete rows to appear in another worksheet
> within that same workbook.
> 
> 
> 
> VLOOKUP function will not work for me as the charge code is in column O
> & I can only get it to return data within that row for column O onward.
> 
> Also, I am unsure if you can get VLOOKUP to check for multiple
> conditions
> 
> * What function should I be using?
> * Can you use VLOOKUP in a nested statement to search for the various
> conditions(charge codes)?
> * Can VLOOKUP return a whole row, without having to specify the column?
> * Can VLOOKUP return data previous to the column where the criteria of
> the search is met?
> 
> * Finally: Should I try to use Macros & if so, can someone point me in
> the right direction???
> 
> 
> Thanks,
> 
> Aine
0
demechanik (4694)
5/10/2006 5:50:02 AM
That would be ideal.

Have a good background in programming & logic, but have never touched
macros
I normally work with databases but the company I am contracting for
want it all through excel for visibility purposes(plus they prefer us
not to use MS Access)

Do you know any good site with macros tutorials for these types of
queries?

0
aineog1 (2)
5/10/2006 6:15:41 AM
Dear Aine

May I ask why the data is spread across three worksheets?  If it was
all on one worksheet you would have a lot more options without macros. 
Also, why do you want to move the returned rows to a separate
worksheet?

Regards

Twig


-- 
timwight
------------------------------------------------------------------------
timwight's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34286
View this thread: http://www.excelforum.com/showthread.php?threadid=540508

0
5/10/2006 6:51:04 AM
To be honest, I don't roam around a lot looking for code solutions - I 
probably should do more of that since they almost always teach something new.

But an excellent book on practical programming within Excel was written by 
John Walkenbach - I have a copy titled "Excel 2000 Power Programming with 
VBA" (ISBN 0-7645-3263-4, and I believe there's an updated edition "Excel 
2003 ..."  Should be available through Amazon or your local Barnes & Noble, 
Borders, etc.

"Aine" wrote:

> That would be ideal.
> 
> Have a good background in programming & logic, but have never touched
> macros
> I normally work with databases but the company I am contracting for
> want it all through excel for visibility purposes(plus they prefer us
> not to use MS Access)
> 
> Do you know any good site with macros tutorials for these types of
> queries?
> 
> 
0
JLatham (219)
5/11/2006 3:01:02 AM
If you want an example of code that will work for this, you can upload a 
workbook I quickly tossed together from:
http://www.jlathamsite.com/uploads/codebook_foraine.xls

If you just want to see the working code (which is dependent on layout and 
sheet names used in the sample workbook), here it is.  It presumes that no 
list goes all the way to the end of a worksheet - doesn't trap for that 
situation.

Sub GatherDataTogether()
    Dim ToBeCompared As String
    
    Dim ListSheet As String
    Dim CollateSheet As String
    Dim DataSheets(1 To 3) As String
    Dim SecondDataSheet As String
    Dim ThirdDataSheet As String
    Dim ListRowOffset As Long
    Dim DataOffsetRow As Long
    Dim LC As Integer ' loop counter
    
    'initialize to find our sheets later
    ListSheet = "ListSheet"
    CollateSheet = "GatheredSheet"
    DataSheets(1) = "Sheet1"
    DataSheets(2) = "Sheet2"
    DataSheets(3) = "Sheet3"
    
    'get ready
    'activate the next instruction to speed things up
    'Application.ScreenUpdating = False
    Worksheets(CollateSheet).Activate
    'choose place to move 1st match to
    Range("A1").Select
    Sheets(ListSheet).Activate
    'choose first 'to match' entry
    Range("A1").Select
    Do Until IsEmpty(ActiveCell)
        ToBeCompared = ActiveCell.Value
        For LC = LBound(DataSheets) To UBound(DataSheets)
            Worksheets(DataSheets(LC)).Activate
            Range("A1").Select ' start of data
            DataOffsetRow = 0 ' (re)initialize
            Do Until IsEmpty(ActiveCell.Offset(DataOffsetRow, 0))
                If ActiveCell.Offset(DataOffsetRow, 0) = ToBeCompared Then
                    DataOffsetRow = DataOffsetRow + 1
                    Rows(DataOffsetRow & ":" & DataOffsetRow).Copy
                    'move to destination sheet
                    Sheets(CollateSheet).Activate
                    'Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
                    ActiveSheet.Paste
                    Application.CutCopyMode = False
                    ActiveCell.Offset(1, 0).Activate ' prepare for next
                    'back to data sheet for more checking
                    Sheets(DataSheets(LC)).Activate
                    Range("A1").Select ' back to top of list
                Else
                    DataOffsetRow = DataOffsetRow + 1
                End If
            Loop ' data on current data sheet
        Next ' LC loop
        Sheets(ListSheet).Activate
        ActiveCell.Offset(1, 0).Activate ' move down 1 row
    Loop ' lookup list
End Sub


"Aine" wrote:

> That would be ideal.
> 
> Have a good background in programming & logic, but have never touched
> macros
> I normally work with databases but the company I am contracting for
> want it all through excel for visibility purposes(plus they prefer us
> not to use MS Access)
> 
> Do you know any good site with macros tutorials for these types of
> queries?
> 
> 
0
JLatham (219)
5/11/2006 4:32:02 AM
Aine,

If you were to use MSQuery you can return all the rows from each sheet or 
you can try a pivot table that could consolidate and totalise!

"Aine" <aineog1@gmail.com> wrote in message 
news:1147241741.194428.207110@j33g2000cwa.googlegroups.com...
> That would be ideal.
>
> Have a good background in programming & logic, but have never touched
> macros
> I normally work with databases but the company I am contracting for
> want it all through excel for visibility purposes(plus they prefer us
> not to use MS Access)
>
> Do you know any good site with macros tutorials for these types of
> queries?
> 


0
dkso (36)
5/14/2006 5:58:28 PM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

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 ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

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...

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 ...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

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...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

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...

If a worksheet name is = to test then a msgbox appears
I'm looking for a macro that will display a msgbox if a worksheet is = to test. For example, if the name of a sheet in a workbook is equal to test then display msgbox saying sheet already exists. Thanks Vick dim ws as worksheet set ws = nothing on error resume next set ws = worksheets("test") on error goto 0 if ws is nothing then msgbox "doesn't exist" else msgbox "already exists" end if Vick wrote: > > I'm looking for a macro that will display a msgbox if a worksheet is = to test. > > For example, if the name of a sheet in a w...

Start macro creating a mail with contact data and autotext
Hallo, I am working with an user form. The developing of that form started with Outlook XP with a lot of code inside for different buttons. I changed to Outlook 2007 and unfortunately the code of the form was not longer displayed. What I learned about this is that MS does not support to much code in the form (or maybe a bug). They also do not support any longer. I was sending this form to MS support but they told it is do much code inside and they do not know, why the code is not displayed. In Outlook 2003 the code is displayed as in Outlook XP. Because I do not know real...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...

Outlook always looks to the web
Whenever I click on calendar or tasks or anything, I'm redirected to a web page that it can't find for a search. This just started. How do I shut it off ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

Disappearing data in sync'd forms
I have a small sized text box in a form called frmMain. This text box may or may not contain a large amount of text. If the text box does contain alot of text I want to open up a new form called frmLargeText that contains a larger text box to allow the user to easily see and edit the large amount of text. I also need the two forms to stay in sync. i.e. if the user moves to a new record then both forms move to the same record. I thought I could do this by setting the recordset of frmLargeText to equal the recordset of frmMain as follows: Dim frm as Form_frmLargeText Dim rst As DAO.Rec...

Calculating Subsets of Data
I have data for records that can span between 1 to 16 months.. I want to be able to calculate the average activity for the most recent six months in a query and be able to display that in a report. I can create the calculation in the query for the entire duration of months for each record but am stuck on how to have it choose only the most recent six months of data to do the calculation and understand that the six months may really only be between 1 to 6 months of data.. Any suggestions would be greatly appreciated..My skill level is somewhere between meatball surgeon and ki...

How can I sum only amounts that are in BOLD format within a column
Hi! I need to get a total from a worksheet that has hundreds of amounts in it. However, I only need the total of the amounts that were marked with have BOLD font. Please help me I don't have much experience with EXCEL. Thank you very much! W a n d a try this for column D Sub sumbold() x = Cells(Rows.Count, "d").End(xlUp).Row For Each c In Range(Cells(2, 4), Cells(x, 4)) If c.Font.Bold Then mysum = mysum + c Next MsgBox mysum End Sub -- Don Guillett SalesAid Software donaldb@281.com "Wanda" <Wanda@discussions.microsoft.com> wrote in message news:89A268A6-...

Multipule Worksheets
How do you set Excel 2002 to open all spreadsheets into one document instead of having multipule documents open up across your toolbar? ...

CRM Error
Hello When a user replies to an CRM email, clicks the "reply" button or the "reply all" button, clicks in the body of the email message and clicks "insert template", this error appears. This does not happen every time, and happens to various users. Does anyone know why we would get this error? ...