Help With Multiple Conditional Sum

Hi, everyone...
Building formulas is not yet a strong area of mine.
I'll keep it concise.

The scenario:

Given:
Columns A, B and D, respectively:
Vendor, Date, Amount.

I want to build a table of annual sums as follows:
Columns: G, H-Q, respectively:
Vendor, 1998-2006

Each vendor, then, will be listed once in the Vendor column (G). I
should be able to scan across columns H-Q for each vendor row and see
how much I spent at any given vendor in any given year.

Any working solutions would be greatly appreciated.

Thanks,

KZ

0
imafellow (28)
12/19/2005 12:07:40 AM
excel.misc 78881 articles. 5 followers. Follow

18 Replies
683 Views

Similar Articles

[PageSpeed] 3

In H1:Q1, input the year numbers

In G2:Gn, add all vendor ids

In H2, add

=SUMPRODUCT(--($A$2:$A$20=$G2),--(YEAR($B$2:$B$20)=H$1),$C$2:$C$20)

and copy down and across

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Ken Zenachon" <imafellow@gmail.com> wrote in message
news:1134950860.162458.55380@g44g2000cwa.googlegroups.com...
> Hi, everyone...
> Building formulas is not yet a strong area of mine.
> I'll keep it concise.
>
> The scenario:
>
> Given:
> Columns A, B and D, respectively:
> Vendor, Date, Amount.
>
> I want to build a table of annual sums as follows:
> Columns: G, H-Q, respectively:
> Vendor, 1998-2006
>
> Each vendor, then, will be listed once in the Vendor column (G). I
> should be able to scan across columns H-Q for each vendor row and see
> how much I spent at any given vendor in any given year.
>
> Any working solutions would be greatly appreciated.
>
> Thanks,
>
> KZ
>


0
bob.phillips1 (6510)
12/19/2005 12:28:52 AM
The easiest way would be to use a pivot table. Use the vendor as your 
row field, the date as the column field and the Amount as the data 
field. Then right click on one of the date headings and choose "Group 
and Show Detail" and then "Group". Click on years and make sure no other 
selections are made and then Ok.

Otherwise if you wanted to do it with formulas enter the vendor names in 
G2 downwards and in H1 - Q1 enter 1998, 1999, 2000 etc.
In H2 enter the formula:
=SUMPRODUCT(($A$2:$A$100=$G2)*(YEAR($B$2:$B$100)=H$1)*$C$2:$C$100)
and adjust the ranges to suit your data (I've used rows 2 to 100).
Then copy down and across as required.

Hope this helps
Rowan

Ken Zenachon wrote:
> Hi, everyone...
> Building formulas is not yet a strong area of mine.
> I'll keep it concise.
> 
> The scenario:
> 
> Given:
> Columns A, B and D, respectively:
> Vendor, Date, Amount.
> 
> I want to build a table of annual sums as follows:
> Columns: G, H-Q, respectively:
> Vendor, 1998-2006
> 
> Each vendor, then, will be listed once in the Vendor column (G). I
> should be able to scan across columns H-Q for each vendor row and see
> how much I spent at any given vendor in any given year.
> 
> Any working solutions would be greatly appreciated.
> 
> Thanks,
> 
> KZ
> 
0
12/19/2005 12:32:15 AM
Hey, guys, thanks for the replies.
I'm not able to implement them successfully as of yet, but given my
level of frustration over this project tonight and my unfamiliarity
with the operations you outlined it's no surprise. I'm gonna leave it
till tomorrow.

Will keep you posted.

Thanks again,


]-[

0
imafellow (28)
12/19/2005 1:28:43 AM
OK, I calmed down and read up on PivotTables.

I don't think a PivotTable is the best solution for me in this case,
for several reasons. For starters, for the Invoice Date data field
Excel creates a separate column for every transaction; hundreds of
columns, which I then have to manually group by year. This would be
fine if I needed to do it just once. However, additional transactions
don't automatically sort themselves into the appropriate year group.
Also, PivotTables don't update automatically, requiring me to manually
refresh the table if I want to see (and sort) the new data.

OK, as for SUMPRODUCT, I read up on it in Excel's help file and the
examples they give for it are nothing like what you are suggesting,
leaving me at a loss as to what's happening in your formulas. I mean, I
*kind of* see what's going on but I'm missing a solid understanding of
the workings of this function.

I figured I wanted to add certain numbers based on multiple criteria
that I'd have to create a statement in the form of SUM(IF()). Aparantly
not. Pease explain.

]-[

0
imafellow (28)
12/19/2005 2:36:07 AM
OK, I calmed down and read up on PivotTables.

I don't think a PivotTable is the best solution for me in this case,
for several reasons. For starters, for the Invoice Date data field
Excel creates a separate column for every transaction; hundreds of
columns, which I then have to manually group by year. This would be
fine if I needed to do it just once. However, additional transactions
don't automatically sort themselves into the appropriate year group.
Also, PivotTables don't update automatically, requiring me to manually
refresh the table if I want to see (and sort) the new data.

OK, as for SUMPRODUCT, I read up on it in Excel's help file and the
examples they give for it are nothing like what you are suggesting,
leaving me at a loss as to what's happening in your formulas. I mean, I
*kind of* see what's going on but I'm missing a solid understanding of
the workings of this function.

I figured I wanted to add certain numbers based on multiple criteria
that I'd have to create a statement in the form of SUM(IF()). Aparantly
not. Pease explain.

Also, Bob and Rowan, you each seem to have a completely different
understanding of how to use the SUMPRODUCT function. Could one or both
of you expound briefly on your methods?

0
imafellow (28)
12/19/2005 2:39:34 AM
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

-- 
Regards,

Peo Sjoblom

(No private emails please)


"Ken Zenachon" <imafellow@gmail.com> wrote in message 
news:1134959767.364695.219060@g43g2000cwa.googlegroups.com...
> OK, I calmed down and read up on PivotTables.
>
> I don't think a PivotTable is the best solution for me in this case,
> for several reasons. For starters, for the Invoice Date data field
> Excel creates a separate column for every transaction; hundreds of
> columns, which I then have to manually group by year. This would be
> fine if I needed to do it just once. However, additional transactions
> don't automatically sort themselves into the appropriate year group.
> Also, PivotTables don't update automatically, requiring me to manually
> refresh the table if I want to see (and sort) the new data.
>
> OK, as for SUMPRODUCT, I read up on it in Excel's help file and the
> examples they give for it are nothing like what you are suggesting,
> leaving me at a loss as to what's happening in your formulas. I mean, I
> *kind of* see what's going on but I'm missing a solid understanding of
> the workings of this function.
>
> I figured I wanted to add certain numbers based on multiple criteria
> that I'd have to create a statement in the form of SUM(IF()). Aparantly
> not. Pease explain.
>
> ]-[
> 

0
terre081 (3244)
12/19/2005 2:39:48 AM
Bob, who suggested the SumProduct solution, is the author of a web site,
where an entire section is devoted to explaining the intricacies of the
SumProduct() function.

This function has evolved way beyond what the originators at Redmond had
originally designed it for, so that's why there's really nothing definitive
in the XL Help files.
That evolution is primarily due to the efforts and expertise of the folks
who frequent these groups.

Grab a pot of coffee, OR a bottle of adult beverage, and read a little:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ken Zenachon" <imafellow@gmail.com> wrote in message
news:1134959767.364695.219060@g43g2000cwa.googlegroups.com...
> OK, I calmed down and read up on PivotTables.
>
> I don't think a PivotTable is the best solution for me in this case,
> for several reasons. For starters, for the Invoice Date data field
> Excel creates a separate column for every transaction; hundreds of
> columns, which I then have to manually group by year. This would be
> fine if I needed to do it just once. However, additional transactions
> don't automatically sort themselves into the appropriate year group.
> Also, PivotTables don't update automatically, requiring me to manually
> refresh the table if I want to see (and sort) the new data.
>
> OK, as for SUMPRODUCT, I read up on it in Excel's help file and the
> examples they give for it are nothing like what you are suggesting,
> leaving me at a loss as to what's happening in your formulas. I mean, I
> *kind of* see what's going on but I'm missing a solid understanding of
> the workings of this function.
>
> I figured I wanted to add certain numbers based on multiple criteria
> that I'd have to create a statement in the form of SUM(IF()). Aparantly
> not. Pease explain.
>
> ]-[
>

0
ragdyer1 (4060)
12/19/2005 2:42:23 AM
Hi Ken

Bob has a brilliant article describing the use of Sumproduct at 
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You will see from this that our two solutions are essentially the same 
thing.

Hope this helps
Rowan

Ken Zenachon wrote:
> OK, I calmed down and read up on PivotTables.
> 
> I don't think a PivotTable is the best solution for me in this case,
> for several reasons. For starters, for the Invoice Date data field
> Excel creates a separate column for every transaction; hundreds of
> columns, which I then have to manually group by year. This would be
> fine if I needed to do it just once. However, additional transactions
> don't automatically sort themselves into the appropriate year group.
> Also, PivotTables don't update automatically, requiring me to manually
> refresh the table if I want to see (and sort) the new data.
> 
> OK, as for SUMPRODUCT, I read up on it in Excel's help file and the
> examples they give for it are nothing like what you are suggesting,
> leaving me at a loss as to what's happening in your formulas. I mean, I
> *kind of* see what's going on but I'm missing a solid understanding of
> the workings of this function.
> 
> I figured I wanted to add certain numbers based on multiple criteria
> that I'd have to create a statement in the form of SUM(IF()). Aparantly
> not. Pease explain.
> 
> Also, Bob and Rowan, you each seem to have a completely different
> understanding of how to use the SUMPRODUCT function. Could one or both
> of you expound briefly on your methods?
> 
0
12/19/2005 2:53:00 AM
Wow, thanks everyone, that really clears things up!
Bob, I gather you get this a lot around here-- Great article!

The creative misuse of tools, man, I'm all over it!

Just as an aside, how might one construct a multiple condition test for
my problem using traditional (non-creative) methods?

]-[

0
imafellow (28)
12/19/2005 3:07:47 AM
Hey, folks,

Just wanted to let you all know that with your help (no small part of
which constitues Bob's tutorial) I taught myself enough about the funky
uses of SUMPRODUCT to create a working formula all by my lonesome.

Teach a man to fish...


KZ

0
imafellow (28)
12/19/2005 4:44:35 AM
Hi Ken

Whilst I see from other postings in this thread you have reached a solution 
with Sumproduct which will do exactly what you want, I have to contest your 
assertions regarding Pivot Tables.

They will do just as you want.
As Rowan said in his posting, you can Group dates by Years.
Pivot tables will automatically sort themselves, in whatever way you define.
A very simple one line macro pasted onto the sheet holding the Pivot table 
will cause it to automatically refresh, each time you view the sheet.

Private Sub Worksheet_Activate()
  ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Copy the above macro, right click on the sheet tab with the Pivot table and 
paste the code into the white pane at the right of the screen. You may need 
to change "PivotTable1" if your Pivot has a different name (this can be 
determined by looking at Table Options from the PT toolbar)

For more information on Pivot Table use, take a look at Debra Dalgleish's site
http://www.contextures.com/xlPivot07.html

Do persevere with Pivot Tables, they were destined for exactly the sort of 
data analysis you are trying to do, and once learned will allow you to do 
things without having to type any formulae.

Regards

Roger Govier


Ken Zenachon wrote:
> OK, I calmed down and read up on PivotTables.
> 
> I don't think a PivotTable is the best solution for me in this case,
> for several reasons. For starters, for the Invoice Date data field
> Excel creates a separate column for every transaction; hundreds of
> columns, which I then have to manually group by year. This would be
> fine if I needed to do it just once. However, additional transactions
> don't automatically sort themselves into the appropriate year group.
> Also, PivotTables don't update automatically, requiring me to manually
> refresh the table if I want to see (and sort) the new data.
> 
> OK, as for SUMPRODUCT, I read up on it in Excel's help file and the
> examples they give for it are nothing like what you are suggesting,
> leaving me at a loss as to what's happening in your formulas. I mean, I
> *kind of* see what's going on but I'm missing a solid understanding of
> the workings of this function.
> 
> I figured I wanted to add certain numbers based on multiple criteria
> that I'd have to create a statement in the form of SUM(IF()). Aparantly
> not. Pease explain.
> 
> ]-[
> 
0
roger1272 (620)
12/19/2005 10:44:57 AM
Hi, Roger,

The reason I thought PivotTables wouldn't work was because I kept
getting a "Cannot Group that Selection" error when I tried invoking the
Group command. The site you linked to, however, sorted that out for me
(as did a quick Google search on the matter).

Moving on, right-clicking on the sheet tab brings up a context menu and
no white are at the right of the screen. Should I click on "View Code"
in this context menu and past the macro into the VBA sheet that
appears?

Also, I apply formats to my PivotTable but every time I refresh the
data the formats are lost and the table is resized. How can I make
formats stick?

KZ

0
imafellow (28)
12/19/2005 7:21:39 PM
Also, is there any way to get the PivotTable to refresh as soon as I
enter new data?


]-[

0
imafellow (28)
12/19/2005 7:23:09 PM
Hi, Roger,

The reason I thought PivotTables wouldn't work was because I kept
getting a "Cannot Group that Selection" error when I tried invoking the
Group command. The site you linked to, however, sorted that out for me
(as did a quick Google search on the matter).

Moving on, right-clicking on the sheet tab as you suggested just brings
up a context menu and no white area at the right of the screen. Should
I click on "View Code" in this context menu and past the macro into the
VBA sheet that appears?

Also, I apply formats to my PivotTable but every time I refresh the
data the formats are lost and the table is resized. How can I make
formats stick?

Also also, is there any way to get the PivotTable to refresh as soon as
I
enter new data?

]-[

0
imafellow (28)
12/19/2005 7:27:30 PM
Hi Ken

Sorry I missed the Select View Code option in my original posting, before 
saying paste the code provided.

After applying your formats, from the PT toolbar, select the dropdown for 
Pivot Table and select Table Options. Remove the checkmark from Autoformat 
table.

If your data is on a separate page to the PT, then when you select the tab 
with the PT the data will automatically refresh.
If you have the PT on the same sheet as your data, then you would need to 
create a Selection Change macro for the column where the data is entered, to 
fire the same macro as provided.


Regards

Roger Govier


Ken Zenachon wrote:
> Hi, Roger,
> 
> The reason I thought PivotTables wouldn't work was because I kept
> getting a "Cannot Group that Selection" error when I tried invoking the
> Group command. The site you linked to, however, sorted that out for me
> (as did a quick Google search on the matter).
> 
> Moving on, right-clicking on the sheet tab as you suggested just brings
> up a context menu and no white area at the right of the screen. Should
> I click on "View Code" in this context menu and past the macro into the
> VBA sheet that appears?
> 
> Also, I apply formats to my PivotTable but every time I refresh the
> data the formats are lost and the table is resized. How can I make
> formats stick?
> 
> Also also, is there any way to get the PivotTable to refresh as soon as
> I
> enter new data?
> 
> ]-[
> 
0
roger1272 (620)
12/20/2005 12:23:04 AM
And if you want your pivot table to automatically include new rows of 
data added you can base the pivot table input on a dynamic named range.

See http://contextures.com/xlNames01.html#Dynamic for dynamic named ranges.

Hope this helps
Rowan

Roger Govier wrote:
> Hi Ken
> 
> Sorry I missed the Select View Code option in my original posting, 
> before saying paste the code provided.
> 
> After applying your formats, from the PT toolbar, select the dropdown 
> for Pivot Table and select Table Options. Remove the checkmark from 
> Autoformat table.
> 
> If your data is on a separate page to the PT, then when you select the 
> tab with the PT the data will automatically refresh.
> If you have the PT on the same sheet as your data, then you would need 
> to create a Selection Change macro for the column where the data is 
> entered, to fire the same macro as provided.
> 
> 
> Regards
> 
> Roger Govier
> 
> 
> Ken Zenachon wrote:
> 
>> Hi, Roger,
>>
>> The reason I thought PivotTables wouldn't work was because I kept
>> getting a "Cannot Group that Selection" error when I tried invoking the
>> Group command. The site you linked to, however, sorted that out for me
>> (as did a quick Google search on the matter).
>>
>> Moving on, right-clicking on the sheet tab as you suggested just brings
>> up a context menu and no white area at the right of the screen. Should
>> I click on "View Code" in this context menu and past the macro into the
>> VBA sheet that appears?
>>
>> Also, I apply formats to my PivotTable but every time I refresh the
>> data the formats are lost and the table is resized. How can I make
>> formats stick?
>>
>> Also also, is there any way to get the PivotTable to refresh as soon as
>> I
>> enter new data?
>>
>> ]-[
>>
0
12/20/2005 1:58:45 AM
Good point Rowan, I had missed that in my suggestions.

Regards

Roger Govier


Rowan Drummond wrote:
> And if you want your pivot table to automatically include new rows of 
> data added you can base the pivot table input on a dynamic named range.
> 
> See http://contextures.com/xlNames01.html#Dynamic for dynamic named ranges.
> 
> Hope this helps
> Rowan
> 
> Roger Govier wrote:
> 
>> Hi Ken
>>
>> Sorry I missed the Select View Code option in my original posting, 
>> before saying paste the code provided.
>>
>> After applying your formats, from the PT toolbar, select the dropdown 
>> for Pivot Table and select Table Options. Remove the checkmark from 
>> Autoformat table.
>>
>> If your data is on a separate page to the PT, then when you select the 
>> tab with the PT the data will automatically refresh.
>> If you have the PT on the same sheet as your data, then you would need 
>> to create a Selection Change macro for the column where the data is 
>> entered, to fire the same macro as provided.
>>
>>
>> Regards
>>
>> Roger Govier
>>
>>
>> Ken Zenachon wrote:
>>
>>> Hi, Roger,
>>>
>>> The reason I thought PivotTables wouldn't work was because I kept
>>> getting a "Cannot Group that Selection" error when I tried invoking the
>>> Group command. The site you linked to, however, sorted that out for me
>>> (as did a quick Google search on the matter).
>>>
>>> Moving on, right-clicking on the sheet tab as you suggested just brings
>>> up a context menu and no white area at the right of the screen. Should
>>> I click on "View Code" in this context menu and past the macro into the
>>> VBA sheet that appears?
>>>
>>> Also, I apply formats to my PivotTable but every time I refresh the
>>> data the formats are lost and the table is resized. How can I make
>>> formats stick?
>>>
>>> Also also, is there any way to get the PivotTable to refresh as soon as
>>> I
>>> enter new data?
>>>
>>> ]-[
>>>
0
roger1272 (620)
12/20/2005 10:37:18 AM
Thanks, everyone.
Everything's working like a charm.

KZ

0
imafellow (28)
12/22/2005 5:50:42 PM
Reply:

Similar Artilces:

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Help with DLookup
I have a DLookup function on my form and it works by finding the information once I exit and then re-enter the form, it does not bring in the information as soon as I enter the information in the lookup field. =DLookUp("[Employee Name]","[Employee Information]","[Employee ID]=forms![Timesheet Entry]![Employee ID]") Does anybody know why it is not updating immediately? All help is greatly appreciated Where are you using this expression -- as the control source of a textbox? More details about the form's design and how you're using the form, please....

Search Multiple Worksheets #2
Is there a way to search trough multiple worksheets for a specific value? Other posts have mentioned to use VBA, but I have never used that before. If anyone can give me some advice on using that or a type of formula to perform that can search multiple worksheets. Thank You There may be other ways but, while holding down the ctrl key select each of the worksheet tabs you want to search in then select Edit|Find from the menu. Type in the value you want and it will go to the first instance of that value. Now if you are wanting to preserve a specifice value for reference or ???, then...

Conditional Formating Help
Hi I wonder if anyone could help me, I am after code for the following. cell e6 = Keys Sent Column f6 = Keys due Column g6 = Keys received What I want the script to do is if there is no value in g6 and todays date is greater then the date stated in F6, then the cell turns red (prompt to inform me that keys are late). Many thanks Dan Use a CF formula of =AND(G6="",TODAY()>F6) HTH Bob "housinglad" <housinglad@discussions.microsoft.com> wrote in message news:A5887369-33DA-489A-BEC9-8173707313C6@microsoft.com... > Hi > ...

Conditional Formating Furmula
I have to enter a 14 digit licence number in a field this is a mix of numbers and leters. can some one please give me a formula that i can use in conditional formating tol show if there are to few or to many digits. (Using excel 2003) Thaks in advance. CF/ Formula is/ =LEN(A1)<14 Format as desired for too few CF/ Formula is/ =LEN(A1)>14 Format as desired for too many Rather than using CF you could, of course, use Data validation to require LEN(A1) to be 14. -- David Biddulph "jockj215" <jockj215@discussions.microsoft.com> wrote in message news:A24...

HELP- error Outlook
What's this mean and how to fix? in error dialogue box: "c:\program~1\micro~2 \office\outlook.exe" Abnormal program termination .. ...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

Conditional Formatting
Hi All, I need help on conditional Formatting. I have a column of data with future DATE, such as 2 Jan 09, 4 Des 09, etc I would like to assign automatically different colors to those data that match these condition: If (TODAY's date < Data's date) And more than 30 days, COLOUR is Orange If (TODAY's date < Data's date) And more than 60 days, COLOUR is Yellow If (TODAY's date < Data's date) And more than 90 days, COLOUR is Green If (TODAY's date >= Data's date) And more than 30 days, COLOUR is Red I would like to gave it AUTO...

help diagnosing what's going on in ExchangeServer2000
I'm fairly ignorant of exactly how ES2000 works and would like some general information to help in my diagnosing why some emails are not being delivered to certain email domains (more correctly, why some emails are not being forwarded actually...ES2K is part of a voicemail system where some users have ..wav copies of the voicemails forwarded to their various off-site email accounts) . Here are my questions: 1. How can I see what emails are being forward? Is there a file/folder akin to the SentItems folder in Outook? 2. If there are send failures, where can I find them and how can ...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

Noob Question For Selecting Multiple Fields On A Form
Hello... In versions earlier than 2007 I would be able to go to the Menu Bar and click Edit -> Select All to select all fields on the form. Now, my question is... where in 2007 did the put that functionality? If Microsoft removed it from there... where did they recreate it? Thanks! Squirrel "SQLSQUIRREL" <SQLSQUIRREL@discussions.microsoft.com> wrote in message news:333547A1-9C6A-422B-9CD5-97D79D6037DF@microsoft.com... > Hello... > > In versions earlier than 2007 I would be able to go to the Menu Bar and > click Edit -> Select All to se...

Urgent Question Please Help: Credit Card Setup Problem
We have an urgent question regarding credit card setup/cash receipts/bank rec We understand that in Great Plains when setting up a Credit Card to be used by a Customer, it must be setup as a "Bank Card", not a "Charge Card", in order for the transaction to hit Bank Rec. However, we mistakenly setup all the Credit Cards as "Charge Cards", therefore after posting Credit Card Cash Receipt transactions in Receivables, the transaction does not appear in Bank Rec for us to perform Bank Deposits. Please Please Help!!! Is there any way to "fix" those pos...

Conditional Statement in a Macro
I have a time sheet program with a pivot table on each sheet to sum the hours worked on each job #. Each pivot table is named pivot table 7. I have set up a macro to update the pivot table easily. It is as follows: Sub Update_Pivot_Table() ' ' Update_Pivot_Table Macro ' Keyboard Shortcut: Ctrl+p ' Range("E63").Select ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh Range("H62").Select End Sub Thus, since each table is named "PivotTable7" the macro works on any of the sheets. I have added a pivot tab...

Multiple AND OR functions
Is it possible to make this function work? =IF(AND(AND($B9="Z",AE9=35),OR(J9=1,J9="M",J9="C")),1000,IF(AND(AND($B9="A+",AE9=35),OR(J9=1,J9="M",J9="C")),750)) I need to find out if Cell B9 = Z and if Cell AE9 = 35, if this true then check cell J9 and check if it equals 1, M or C then return the value as 1000. (that bit works OK) I also want it to check if an alternative statement is true if the first is false whereby it checks the the same set of cells but this time, check if B9 =A+, if Cell AE9 = 35, if this true then...

Lump sum calculation
Does any one know what function (PMT - PPMT- PV- FV) I have to use the get my results? I want to have 18 years from now the amount of $70,000 If the interest rate will always be 12% I want to make only one payment new. Can any one help me find the function? I couldn't do it by my self, I need your help. Thanks Joe Hi Joe! Try: =PV(12%,18,0,70000,0) Returns: -9102.77130820508 The negative reflects a payment out of 9102 in return for a payment in of 70000 You could negate the function or negate the 70000 if you want a positive sign but in general terms life is less confusing if you fol...

Offline Address Book, Multiple Administrative Groups
Hello, We recently deployed an additional Exchange server in our organization and placed it into its own administrative group, now users on the new server are getting 8004010F errors in Outlook when attempting to download the offline addresss book. The users who are on the original server do not receive these errrors when downloading the address book. If anyone can provide some assitance it would be greatly appreciated. It's look like this http://support.microsoft.com/default.aspx?scid=kb;en-us;162703 "jballin" <jballin@discussions.microsoft.com> wrote in message...

Conditional Cell Fill?
Hello, Is there a way to use fill colors based on formulas? Thanks, Rusty Look at conditional formatting in help -- Regards, Peo Sjoblom "Rusty Williamson" <rusty@uno.sd.znet.com> wrote in message news:RUGlb.45764$Z86.33887@twister.socal.rr.com... > Hello, > > Is there a way to use fill colors based on formulas? > > Thanks, > Rusty > > ...

help with unusual request
Hello everyone, My boss has hit me with an odd request and I would like to ask for someone to point me in the right direction. What he wants is everytime anyone sends or recieves an email that a copy is made that he can get to anytime he wants. I talked to him about our daily backups, but he wants something that's realtime and that he can get to whenever he wants. I'm stuck and looking for ideas. We're currently on Exchange 5.5 with all of the latest service packs and patches. Any and all help is very much appreciated. You can enable "message journaling" fea...

Help designing a checklist in access
Hello All My company currently uses excel checklists to ensure that projects are completed fully. we have around 10 different checklists. each checklist (excel file) has 6 sheets, each sheet 15 questions or steps. most answers are Y,N,NA and the employees put their initials in the correct answer column. Many questions are redundant throughout the 10 checklists. I would like to create the checklists in access because their are too many excel files getting lost and if we change question/ step. we have to change it multiple times. the employees need to enter a clients name and then choose the c...

Why are checks printing incorrect amounts? Please help.
When I print a Payables check batch, several invoices within the batch are printing incorrectly as $0.00 amount, even though it should be paying off a certain amount. The problem seems to be with invoices that are applied to credit memos. Say I have an invoice for $500 and I apply a credit memo of $80 to the invoice, when I go back to pay the invoice, it should pay $420, but instead it shows $0.00 under amount paid column of check stub. Does anyone know why this is occuring? By the way, I am using MICR check printing. ...

Timesheet Pseudo Tied Mode
I'm hoping that I might have a colleague out there who could help me offline figure out how to install this open source utility (unsupported by Microsoft but apparently works OK for some of their clients) that streamlines the Timesheet process (automatically synchronizes the data into My Tasks whenever a timesheet is saved). I'm not sure where to start and may need someone to bounce questions off of - especially if you've been successful with it. Thanks, Andy Novak UNT Hi Anovak, We installed it (still using the version 1.3 based on event handler) and we're usi...

Please help with Exchange 2003 SMTP Connector
I'm trying to create an SMTP Connector in Exchange Server 2003, to have our company's email sent out (Internet/External emails) to a specific mail forwarder server (the "specific" server is our ISP/Firewall provider server). But I'm having problems understanding how to set this up properly with our Exchange 2003 Server, I know I have to create a new SMTP Connector but not sure what settings to make. Could someone please help me with this. Thanks William On Thu, 25 Feb 2010 22:41:39 -0500, "Bill" <wstyler@verizon.net> wrote: >I&...

Multiple copies of E-Mail messages
I am using Outlook 2002 (10.4219.4219) SP2 with a Windows XP Professional operating system. Just in the last couple days I've started to experience a problem with incoming e-mail messages. I use Outlook to retrieve e-mail from at least four different accounts, from at least two different servers. When I receive a new e-mail message that is addressed to one of these e-mail accounts, I get two extra copies of that message, and each of these extra copies are addressed to two of my other accounts. So what I end up with is three copies with three different TO: addresses. This only happens ...

Parse multiple text lines into 1 line in excel
help. I am an excel beginner and can't find out how to turn multipl lines of text into 1 row in excel. It's probably really easy but m manual is USELESS. Can anyone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com debbie You're a little short on details. If nothing below fits the bill post back. "Multiple lines" is how many and is each line in a separate cell down one column? Do you want all lines to go into one cell? You can use this form...

Help in turning off underlining and blue font
Hello, Does anyone know if there is a way to turn off the automatic underlining and blue font of the text when entering a email address in a cell? Thanks, Bob Tools / Autocorrect options / Autoformat Tab / Replace Network paths with hyperlinks - Deselect it -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ----------------------------------------------------------------------...