Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 29th 04, 05:14 PM
Minitman
 
Posts: n/a
Default Conditional Format With SUMIF

Greetings,

I have to see if one three conditions exists:

1) Missing BS items for existing DT items - <Pink
2) Missing DT items from existing BS items - <Light Blue
3) Both have some missing - <Light Purple

The first Conditional Format will be in A1, B1, C1 and D1. These are
the 4 columns that have information:

A) Date (BS & DT)
B) Account (BS & DT)
C) BS Amount
D) DT Amount

I need to turn the contents of A and B into some kind of transaction
ID. The date need only be in dd format. The Account has either
xxxxxxxx or xxxxxxxxx-x format, they are both present.

BS entries have Date, Account and Amount
DT entries also have Date, Account and Amount, only different amounts.
DT Amounts for the same Date-Account added together will be equal to
BS Amounts for the same Date-Account added together. This is the
default condition.

I think SUMIF is involved, but I am not sure how.

Anyone have any ideas?

Any help would be appreciated.

TIA

-Minitman

  #2   Report Post  
Old October 29th 04, 06:20 PM
Frank Kabel
 
Posts: n/a
Default

Hi
not really sure. Best would be if you could post some example rows of
data (plain text please) and explain your expected result

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Greetings,

I have to see if one three conditions exists:

1) Missing BS items for existing DT items - <Pink
2) Missing DT items from existing BS items - <Light Blue
3) Both have some missing - <Light Purple

The first Conditional Format will be in A1, B1, C1 and D1. These are
the 4 columns that have information:

A) Date (BS & DT)
B) Account (BS & DT)
C) BS Amount
D) DT Amount

I need to turn the contents of A and B into some kind of transaction
ID. The date need only be in dd format. The Account has either
xxxxxxxx or xxxxxxxxx-x format, they are both present.

BS entries have Date, Account and Amount
DT entries also have Date, Account and Amount, only different

amounts.
DT Amounts for the same Date-Account added together will be equal to
BS Amounts for the same Date-Account added together. This is the
default condition.

I think SUMIF is involved, but I am not sure how.

Anyone have any ideas?

Any help would be appreciated.

TIA

-Minitman


  #3   Report Post  
Old October 30th 04, 12:12 AM
Minitman
 
Posts: n/a
Default

Hey Frank,

Ok, here goes....

|______A_____|______B_____|______C______|______D__ ___|
1 |____Date____|___Account___|__BS Amount_|__DT Amount_|
2 |__01/15/98___|_900460123-8_|$______120.00|_____________| <OK
3 |__01/15/98___|_900460123-8_|_____________|$_______70.00| <OK
4 |__01/15/98___|_900460123-8_|_____________|$_______50.00| <OK
5 |__01/15/98___|__15006254___|$_______75.00|_____________| <OK
6 |__01/15/98___|__15006254___|____________|$________35.00| <OK
7 |__01/15/98___|__15006254___|____________|$________40.00| <OK
8 |__01/18/98___|__15006254___|$______205.00|_____________| <OK
9 |__01/18/98___|__15006254___|____________|$_______120.00| <OK
10|__01/18/98___|__15006254___|____________|$________85.00| <OK
11|__01/18/98___|__64012813___|____________|$________45.00| <Pink
12|__01/21/98___|_900460123-8_|$_______80.00|_____________| <OK
13|__01/21/98___|_900460123-8_|$______120.00|_____________| <OK
14|__01/21/98___|_900460123-8_|_____________|$_______25.00| <OK
15|__01/21/98___|_900460123-8_|_____________|$_______54.00| <OK
16|__01/21/98___|_900460123-8_|_____________|$_______42.00| <OK
17|__01/21/98___|_900460123-8_|_____________|$_______26.00| <OK
18|__01/21/98___|_900460123-8_|_____________|$_______53.00| <OK
19|__01/25/98___|_900460123-8_|$_______21.00|_____________| <L Bl
20|__01/25/98___|_900460123-8_|_____________|$_____-179.00| <L Bl
21|__01/25/98___|_900460123-8_|_____________|$_______93.00| <L Bl
22|__01/25/98___|_900460123-8_|_____________|$_______87.00| <L Bl

I am looking to see if the sum of BS Amount per day per account is the
same as the sum of DT Amount for the same day and account. If the DT
Amount is smaller then the BS Amount I need for the rows for that
day-account to be light blue <L Bl. If the BS Amount is smaller then
the DT Amount, then I need for the rows for that day-account to be
pink <Pink.

If a BS Amount is missing, there is no way knowing how many DT Amounts
are supposed to match up with it, so some of them could be missing
also. If A BS Amount is missing, it is missing for all of that month
for that account number. For example, any DT Amounts with an account
number of 64012813 are going to be missing a BS Amount for that entire
month. Which is why that row is colored pink.

I have a conditional format on these cells now (except row 1) which
colors them beige and removes the borders if =$An="" which is why I
don't think I can use conditional formatting but I will need a vba
solution.

Thank you for looking at this. I hope this gives you enough to work
with.

I appreciate any help that you can give.

-Minitman


On Fri, 29 Oct 2004 19:20:53 +0200, "Frank Kabel"
wrote:

Hi
not really sure. Best would be if you could post some example rows of
data (plain text please) and explain your expected result

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Greetings,

I have to see if one three conditions exists:

1) Missing BS items for existing DT items - <Pink
2) Missing DT items from existing BS items - <Light Blue
3) Both have some missing - <Light Purple

The first Conditional Format will be in A1, B1, C1 and D1. These are
the 4 columns that have information:

A) Date (BS & DT)
B) Account (BS & DT)
C) BS Amount
D) DT Amount

I need to turn the contents of A and B into some kind of transaction
ID. The date need only be in dd format. The Account has either
xxxxxxxx or xxxxxxxxx-x format, they are both present.

BS entries have Date, Account and Amount
DT entries also have Date, Account and Amount, only different

amounts.
DT Amounts for the same Date-Account added together will be equal to
BS Amounts for the same Date-Account added together. This is the
default condition.

I think SUMIF is involved, but I am not sure how.

Anyone have any ideas?

Any help would be appreciated.

TIA

-Minitman


  #4   Report Post  
Old November 1st 04, 02:58 PM
Minitman
 
Posts: n/a
Default

Hey Frank,

I guess this post got lost over the weekend.

Anyone have any ideas as to how to see if the sum of BS Amount per
day per account is the same as the sum of DT Amount for the same day
and account. If the DT Amount is smaller then the BS Amount, I need
for all of the rows for that day-account to be light blue <L Bl. If
the BS Amount is smaller then the DT Amount, then I need for all of
the rows for that day-account to be pink <Pink.

Any help would be appreciated.

TIA

-Minitman


On Fri, 29 Oct 2004 18:12:50 -0500, Minitman
wrote:

Hey Frank,

Ok, here goes....

|______A_____|______B_____|______C______|______D__ ___|
1 |____Date____|___Account___|__BS Amount_|__DT Amount_|
2 |__01/15/98___|_900460123-8_|$______120.00|_____________| <OK
3 |__01/15/98___|_900460123-8_|_____________|$_______70.00| <OK
4 |__01/15/98___|_900460123-8_|_____________|$_______50.00| <OK
5 |__01/15/98___|__15006254___|$_______75.00|_____________| <OK
6 |__01/15/98___|__15006254___|____________|$________35.00| <OK
7 |__01/15/98___|__15006254___|____________|$________40.00| <OK
8 |__01/18/98___|__15006254___|$______205.00|_____________| <OK
9 |__01/18/98___|__15006254___|____________|$_______120.00| <OK
10|__01/18/98___|__15006254___|____________|$________85.00| <OK
11|__01/18/98___|__64012813___|____________|$________45.00| <Pink
12|__01/21/98___|_900460123-8_|$_______80.00|_____________| <OK
13|__01/21/98___|_900460123-8_|$______120.00|_____________| <OK
14|__01/21/98___|_900460123-8_|_____________|$_______25.00| <OK
15|__01/21/98___|_900460123-8_|_____________|$_______54.00| <OK
16|__01/21/98___|_900460123-8_|_____________|$_______42.00| <OK
17|__01/21/98___|_900460123-8_|_____________|$_______26.00| <OK
18|__01/21/98___|_900460123-8_|_____________|$_______53.00| <OK
19|__01/25/98___|_900460123-8_|$_______21.00|_____________| <L Bl
20|__01/25/98___|_900460123-8_|_____________|$_____-179.00| <L Bl
21|__01/25/98___|_900460123-8_|_____________|$_______93.00| <L Bl
22|__01/25/98___|_900460123-8_|_____________|$_______87.00| <L Bl

I am looking to see if the sum of BS Amount per day per account is the
same as the sum of DT Amount for the same day and account. If the DT
Amount is smaller then the BS Amount, I need for all of the rows for that
day-account to be light blue <L Bl. If the BS Amount is smaller then
the DT Amount, then I need for all of the rows for that day-account to be
pink <Pink.

If a BS Amount is missing, there is no way knowing how many DT Amounts
are supposed to match up with it, so some of them could be missing
also. If A BS Amount is missing, it is missing for all of that month
for that account number. For example, any DT Amounts with an account
number of 64012813 are going to be missing a BS Amount for that entire
month. Which is why that row is colored pink.

I have a conditional format on these cells now (except row 1) which
colors them beige and removes the borders if =$An="" which is why I
don't think I can use conditional formatting but I will need a vba
solution.

Thank you for looking at this. I hope this gives you enough to work
with.

I appreciate any help that you can give.

-Minitman


On Fri, 29 Oct 2004 19:20:53 +0200, "Frank Kabel"
wrote:

Hi
not really sure. Best would be if you could post some example rows of
data (plain text please) and explain your expected result

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Greetings,

I have to see if one three conditions exists:

1) Missing BS items for existing DT items - <Pink
2) Missing DT items from existing BS items - <Light Blue
3) Both have some missing - <Light Purple

The first Conditional Format will be in A1, B1, C1 and D1. These are
the 4 columns that have information:

A) Date (BS & DT)
B) Account (BS & DT)
C) BS Amount
D) DT Amount

I need to turn the contents of A and B into some kind of transaction
ID. The date need only be in dd format. The Account has either
xxxxxxxx or xxxxxxxxx-x format, they are both present.

BS entries have Date, Account and Amount
DT entries also have Date, Account and Amount, only different

amounts.
DT Amounts for the same Date-Account added together will be equal to
BS Amounts for the same Date-Account added together. This is the
default condition.

I think SUMIF is involved, but I am not sure how.

Anyone have any ideas?

Any help would be appreciated.

TIA

-Minitman




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional format of data tables in charts [email protected] Charts and Charting in Excel 2 January 25th 05 03:56 PM
How do I set a Conditional Format for ROWs based on the value of . Wes T Excel Discussion (Misc queries) 3 December 29th 04 06:10 PM
copy conditional format as ACTUAL format Dana Zulager Excel Discussion (Misc queries) 7 December 7th 04 11:02 PM
make a conditional format the default Fred Evans Excel Discussion (Misc queries) 3 December 6th 04 05:01 AM
Conditional format rexmann Excel Discussion (Misc queries) 4 December 2nd 04 12:01 PM


All times are GMT +1. The time now is 04:46 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017