Posts by Bartman

Re: Autofilter Column Data Created By Formula

Thansk StephenR

That did it :cheers:

I am experinacing some difficulty in using the Autofilter in the attached sheet.

The workbook consisits of the following sheets:

Main

Contains a button taht crates data for sheet "Working Data" by filtering and formating data found in Raw Data sheet.

The code behind it ain't preetty but it worsk.

Raw data

Just as the name implies

Working Data

Filter and formated raw data.

After the VB code behind the button on sheet Main runs, I Insert an Autofilter on the coluimns of Working data.

on column I ( Length ) I try and filter for rows were the value in column I is >= 35.

Nothing shows up. I know this is wrong, just by inspection.

I can ask for rows were column I is = 35 and get 1 rows.

I can ask for rows were column I is = 45 and get 1 rows.

in fact I have Raw Data that comprises about 2000 rows but had to chop it down to meat board size req'ts:>
Can anyone see an obvious glitch ?

Re: Format Date With No Separators Or As Text

Works like charm Dave..

Much thanks

How do I mark this as solved ?

Hi

I get data from a web site that EXCEL sometimes thinks is a date.

Example: Web site stores as 05Jan02 and EXCEL displays as 5-Jan-02

Somer of the data I hget is in the proper format already because the data cannot be resolved out as a date by EXCEL

Example: Web site stores as 05Jan100213 and EXCEL displays it as 05Jan100213

Is there a way to format all the data so it displays properly.

In the first example I would like it to display as 05Jan02 in EXCEL.

I also would like to thank y'all for the great stuff on thsi site... Discoverd the power of EXCEL Filters through the posts here.. Saved me tons of work.

John

Re: Swapping Multiple Cells Accross A Row

it was an attempt to get a wider audiance

My apologoes

--------------------------------------------------------------------------------

I am attaching a sample sheet as a reference.

in fact the sheet could contain hundreds if not thousands of rows.

Problem...

Look at column Player 1, the last 3 characters are AoS.
I want to find any rows where Player 1 has those three characters ( AoS ) and swap them with the contents of the Player 2.

BUT if that swap happens then the following swaps must occur as well:

Contents of Column "Side 1" swaps with Contents of Colum "Side 2"
Contents of Column "Player 1 Pts" swaps with Contents of Column "Player 2 Pts"

Ultimately I want this extended so that is the letters AoS appear in any of the following columns ( Player 4, Player 6, Player 8, Player 10)
the following swaps happens:

Contents of Column "Player 3" swaps with Contents of Column "Player 4"
Contents of Column "Side 3" swaps with Contents of Column "Side 4"
Contents of Column "Player 3 Pts" swaps with Contents of Column "Player 4 Pts"

Contents of Column "Player 5" swaps with Contents of Column "Player 6"
Contents of Column "Side 5" swaps with Contents of Column "Side 6"
Contents of Column "Player 5 Pts" swaps with Contents of Column "Player 6 Pts"

Contents of Column "Player 7" swaps with Contents of Column "Player 8"
Contents of Column "Side 7" swaps with Contents of Column "Side 8"
Contents of Column "Player 7 Pts" swaps with Contents of Column "Player 8 Pts"

Contents of Column "Player 9" swaps with Contents of Column "Player 10"
Contents of Column "Side 9" swaps with Contents of Column "Side 10"
Contents of Column "Player 9 Pts" swaps with Contents of Column "Player 10 Pts"

Appreciate any help

John

Hi

I have a task to perform in Outlook 2003.

I have some experiance in programming but as Outlook seems to lack a record macro capability, I must write the code from scartch, I need help getting this done.

Basically, I get an email ( I fact I get a lot of these emails ) That I am required to respond to in a particular fashion. These emails all have the same subject so I have no issues in determining what emails to respond to.

This project will be built in multiple phase's so I can learn as I go.
any macros or functions should be accessible either by a key sequence or customized menu options.

Phase 1

After I hit reply or reply all for a given email. and examine it I need
a macro or macros that do the following:

Step 1 - Clear the To, CC, BCC and Subject Lines.

Step 2 - The Subject Line gets populated with canned text.

Step 3 - The macro puts in canned text in the email body.

Phase 2 - Expanding the above 3 steps.

Step 1 - After clearing the 3 fields I search the email body to determine who to respond to and put the equired info in the To and CC list.

Step 2 - The canned text is augmented with information gleamed from the email body.

Step 3 - The text used in step 3 above is augmented with information gleamed from the original email body.

What I would like is for someone to map out the steps and guide me in writing the code.

I DO NOT WANT THE CODE WRITTEN FOR ME.

Any takers?

John

I have a bitmap in a cell..

I can size it but am having trouble trying to center the bitmap in the cell..

is there a way to do this ?

Re: Creating Unique Form Numbers For Word Documents

This is rather new to me..

could you please post some sample code

Hi...

I have a word doc ( Word Version 2000 ) that has a field in the top left Corner of the page.

this filed is in the form of :

CCF-YYYYMMDD-######

I am in need of VBA code to automatically create this field ( This part YYYYMMDD-###### )

The CCF part is always there.

The other requirement is that the ###### is unique.

Example

CCF-20061109-000001
CCF-20061109-000002
CCF-20061110-000003
CCF-20061111-000004

My initial thought would be that I would have to track the numbers used in the ###### part in a separate file.
Whenever a new number is used, a tracking file is updated.

Any thoughts ?

Thank you

HI...

I have an EXCEL 200 workbook with teh firts bsheet setup like a Database.

I also have a requierment to set up SQL to perform a standard and predefined set of queries.

I know hwo to write SQL queries and use MS-SQL.

What I want is to set up buttons that I can just click and run a query....

How do I assign the SQL to a button ?

Hi

I have 2 columns which contain numbers that have laeding zeros..

example:

Column H has 002345 and Column I has 0678

I have tried H1&I1 but the leading zeros are stripped off.

Is there a way to combine 2 cells AND preserve the leading zeros in both cells ?

Re: Count Data Based on 2 Criteria

I ack the thread about post titles....

Re: Count Data Based on 2 Criteria

The l;ast thing I would like to do is take thsi foirmual and modifyu as follwos:

SUMPRODUCT(1*($B$1:$B$5="Major Victory"),1*($A$1:$A$5="USA"))

suppose the words "Major Victory" was part of a larger string. I think I need to use FIND but am not sure it works over a range.

Re: Countif ( maybe ) that matchs 2 criteria

The light came on... I now understand what you are saying....

Much Thanks !!!

Re: Countif ( maybe ) that matchs 2 criteria

No.. I do not think I am being clear

Sheet2 has 5 totals to calculate based on the data in Sheet1

Major Victory
=> Where Column L on Sheet1 = USA and Column M = Major Victory

Minor Victory
=> Where Column L on Sheet1 = USA and Column M = Minor Victory

Draw => Where Column L on Sheet1 = USA or CSA and Column M = Draw

Major Loss
=> Where Column L on Sheet1 = CSA and Column M = Major Victory

Minor Loss
=> Where Column L on Sheet1 = CSA and Column M = Minor Victory

Re: Countif ( maybe ) that matchs 2 criteria

That will not work...

Sheet2 will have sveral reports that I nedc to get totlas for each having different criteria

Re: Countif ( maybe ) that matchs 2 criteria

Quote from JimFuller1

Could you add a column and sum it after applying the formula below?

=IF(AND(B1="Major Victory",A1="USA"),1,0)

Jim

not following... Where would I add this extra column ?

I've looked at the various examples in a search of topics simillar to my topic but I still can not get thsi to work.

I have a work book that contains 2 sheets

Sheet1 is called RawData of Multiple columns
Sheet2 is a quick report basded on Sheet1 data.

I need to find a way to do counts where 2 criteria are met.

Example.

Count the number of Times the phrase Major Victory appears in column M
( Range M1 to M50 ) of Sheet1 IF the value in Column L ( Range L1 to L50 ) of Sheet1 = USA

I tried this:

=COUNTIF(RawData!M1:M50,B1( AND(RawData!L1:L50=$A1)))

I am using B1 and A1 ( from Sheet2 ) as search criteria

but it gave me 0 ( and I know this is wrong ).

Based on the reading I did on simillar topics it appears that I cannot use CountIF in this case...

can anyone help me out ?

I have attached the workbook I am using

Re: Number of characters in a cell

Quote from norie

You are wrong.:)

It sounds to me that either you've formatted as text, calculation is set to manual or you've set options to view formulas.

How do I check this out then ?