Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 400500 85 19/03/2010 8 G/032/05/999 400600 238 19/03/2010 9 G/032/03/001/002 400500 170 19/03/2010 10 G/032/02/001/001 400400 170 20/03/2010 11 G/032/05/999 400600 53 20/03/2010 12 G/032/03/001/999 400500 430 21/03/2010 13 G/032/05/999 400600 371 21/03/2010 The result of formula(s) would look something like: G/032/05/999 19/03/2010 967 G/032/03/001/999 19/03/2010 350 G/032/02/001/001 19/03/2010 170 and so on..... I think I'm wishing for the moon, but would solve a major headache in my daily working life!!!
|
0 |
|
Utf
4/9/2010 9:11:01 AM
excel.worksheet.functions
4936 articles. 
2 followers.
5 Replies
1858 Views
The easiest way to do this would be with a PivotTable, but you could use the
following formualae, assuming that your data is in A1:D14,
in F2 (array formula*):
=INDEX($A$2:$A$14&"-"&$D$2:$D$14,MATCH(0,COUNTIF(F$1:$F1,$A$2:$A$14&"-"&$D$2:$D$14),0))
in G2:
=LEFT($F2,FIND("-",$F2)-1)
in H2:
=TEXT(VALUE(RIGHT($F2,5)),"dd/mm/yyyy")
in I2:
=SUMPRODUCT(($A$2:$A$14=$G2)*($D$2:$D$14=VALUE($H2))*$C$2:$C$14)
copy F2:I2 down to F14:I14, and hide column F.
*to enter an array formula press Ctrl+Shift+Enter instead of just Enter.
"Stinky" <Stinky@discussions.microsoft.com> wrote in message
news:AB6FB309-4A74-4FDA-804B-71022CFF336D@microsoft.com...
> Can anyone help me with this one please? I have this table of data. I'd
> like
> to be able to write a formula(s) which sums the No according to date and
> code, but then only returns a sum value (with the code and date in the two
> adjacent cells) if there is a value greater than 0.
>
> Code Ref No Date
> 1 G/032/05/999 400600 212 19/03/2010
> 2 G/032/03/001/999 400500 50 19/03/2010
> 3 G/032/02/001/001 400400 170 19/03/2010
> 4 G/032/05/999 400600 315 19/03/2010
> 5 G/032/03/001/999 400500 300 19/03/2010
> 6 G/032/05/999 400600 202 19/03/2010
> 7 G/032/03/001/002 400500 85 19/03/2010
> 8 G/032/05/999 400600 238 19/03/2010
> 9 G/032/03/001/002 400500 170 19/03/2010
> 10 G/032/02/001/001 400400 170 20/03/2010
> 11 G/032/05/999 400600 53 20/03/2010
> 12 G/032/03/001/999 400500 430 21/03/2010
> 13 G/032/05/999 400600 371 21/03/2010
>
> The result of formula(s) would look something like:
>
> G/032/05/999 19/03/2010 967
> G/032/03/001/999 19/03/2010 350
> G/032/02/001/001 19/03/2010 170 and so on.....
>
> I think I'm wishing for the moon, but would solve a major headache in my
> daily working life!!!
|
|
0 |
|
Steve
4/9/2010 11:15:19 AM
Wow! Thanks Steve it worked - excellent, it's going to save my team huge
amount of time. I'll take your advice and learn PivotTables next!
"Steve Dunn" wrote:
> The easiest way to do this would be with a PivotTable, but you could use the
> following formualae, assuming that your data is in A1:D14,
>
> in F2 (array formula*):
>
> =INDEX($A$2:$A$14&"-"&$D$2:$D$14,MATCH(0,COUNTIF(F$1:$F1,$A$2:$A$14&"-"&$D$2:$D$14),0))
>
> in G2:
>
> =LEFT($F2,FIND("-",$F2)-1)
>
> in H2:
>
> =TEXT(VALUE(RIGHT($F2,5)),"dd/mm/yyyy")
>
> in I2:
>
> =SUMPRODUCT(($A$2:$A$14=$G2)*($D$2:$D$14=VALUE($H2))*$C$2:$C$14)
>
> copy F2:I2 down to F14:I14, and hide column F.
>
>
> *to enter an array formula press Ctrl+Shift+Enter instead of just Enter.
>
>
>
>
>
>
> "Stinky" <Stinky@discussions.microsoft.com> wrote in message
> news:AB6FB309-4A74-4FDA-804B-71022CFF336D@microsoft.com...
> > Can anyone help me with this one please? I have this table of data. I'd
> > like
> > to be able to write a formula(s) which sums the No according to date and
> > code, but then only returns a sum value (with the code and date in the two
> > adjacent cells) if there is a value greater than 0.
> >
> > Code Ref No Date
> > 1 G/032/05/999 400600 212 19/03/2010
> > 2 G/032/03/001/999 400500 50 19/03/2010
> > 3 G/032/02/001/001 400400 170 19/03/2010
> > 4 G/032/05/999 400600 315 19/03/2010
> > 5 G/032/03/001/999 400500 300 19/03/2010
> > 6 G/032/05/999 400600 202 19/03/2010
> > 7 G/032/03/001/002 400500 85 19/03/2010
> > 8 G/032/05/999 400600 238 19/03/2010
> > 9 G/032/03/001/002 400500 170 19/03/2010
> > 10 G/032/02/001/001 400400 170 20/03/2010
> > 11 G/032/05/999 400600 53 20/03/2010
> > 12 G/032/03/001/999 400500 430 21/03/2010
> > 13 G/032/05/999 400600 371 21/03/2010
> >
> > The result of formula(s) would look something like:
> >
> > G/032/05/999 19/03/2010 967
> > G/032/03/001/999 19/03/2010 350
> > G/032/02/001/001 19/03/2010 170 and so on.....
> >
> > I think I'm wishing for the moon, but would solve a major headache in my
> > daily working life!!!
>
|
|
0 |
|
Utf
4/9/2010 12:52:02 PM
You're welcome (and PivotTables are nowhere near as scary as they may at
first seem).
"Stinky" <Stinky@discussions.microsoft.com> wrote in message
news:83886D1C-BD2C-4EC0-BB03-26E069893C5C@microsoft.com...
> Wow! Thanks Steve it worked - excellent, it's going to save my team huge
> amount of time. I'll take your advice and learn PivotTables next!
>
> "Steve Dunn" wrote:
>
>> The easiest way to do this would be with a PivotTable, but you could use
>> the
>> following formualae, assuming that your data is in A1:D14,
>>
>> in F2 (array formula*):
>>
>> =INDEX($A$2:$A$14&"-"&$D$2:$D$14,MATCH(0,COUNTIF(F$1:$F1,$A$2:$A$14&"-"&$D$2:$D$14),0))
>>
>> in G2:
>>
>> =LEFT($F2,FIND("-",$F2)-1)
>>
>> in H2:
>>
>> =TEXT(VALUE(RIGHT($F2,5)),"dd/mm/yyyy")
>>
>> in I2:
>>
>> =SUMPRODUCT(($A$2:$A$14=$G2)*($D$2:$D$14=VALUE($H2))*$C$2:$C$14)
>>
>> copy F2:I2 down to F14:I14, and hide column F.
>>
>>
>> *to enter an array formula press Ctrl+Shift+Enter instead of just Enter.
>>
>>
>>
>>
>>
>>
>> "Stinky" <Stinky@discussions.microsoft.com> wrote in message
>> news:AB6FB309-4A74-4FDA-804B-71022CFF336D@microsoft.com...
>> > Can anyone help me with this one please? I have this table of data.
>> > I'd
>> > like
>> > to be able to write a formula(s) which sums the No according to date
>> > and
>> > code, but then only returns a sum value (with the code and date in the
>> > two
>> > adjacent cells) if there is a value greater than 0.
>> >
>> > Code Ref No Date
>> > 1 G/032/05/999 400600 212 19/03/2010
>> > 2 G/032/03/001/999 400500 50 19/03/2010
>> > 3 G/032/02/001/001 400400 170 19/03/2010
>> > 4 G/032/05/999 400600 315 19/03/2010
>> > 5 G/032/03/001/999 400500 300 19/03/2010
>> > 6 G/032/05/999 400600 202 19/03/2010
>> > 7 G/032/03/001/002 400500 85 19/03/2010
>> > 8 G/032/05/999 400600 238 19/03/2010
>> > 9 G/032/03/001/002 400500 170 19/03/2010
>> > 10 G/032/02/001/001 400400 170 20/03/2010
>> > 11 G/032/05/999 400600 53 20/03/2010
>> > 12 G/032/03/001/999 400500 430 21/03/2010
>> > 13 G/032/05/999 400600 371 21/03/2010
>> >
>> > The result of formula(s) would look something like:
>> >
>> > G/032/05/999 19/03/2010 967
>> > G/032/03/001/999 19/03/2010 350
>> > G/032/02/001/001 19/03/2010 170 and so on.....
>> >
>> > I think I'm wishing for the moon, but would solve a major headache in
>> > my
>> > daily working life!!!
>>
|
|
0 |
|
Steve
4/9/2010 1:07:33 PM
In case you get reply notification, Steve, I just thought I'd let you know
that I conquered the PivotTables and it has completely revolutionised my
life!! I never would have known about it if you hadn't mentioned it - I
only wish I'd tried this forum 100 years ago (well, that's how long it seems
I've been laboriously working with ridiculous amounts of data for
)..........I've now shown other colleagues how brilliant they are for what we
do and it's been declared the 'discovery of the year'.
Taking the time to respond to these threads is SO valuable. Thanks a million.
"Steve Dunn" wrote:
> You're welcome (and PivotTables are nowhere near as scary as they may at
> first seem).
>
>
> "Stinky" <Stinky@discussions.microsoft.com> wrote in message
> news:83886D1C-BD2C-4EC0-BB03-26E069893C5C@microsoft.com...
> > Wow! Thanks Steve it worked - excellent, it's going to save my team huge
> > amount of time. I'll take your advice and learn PivotTables next!
> >
> > "Steve Dunn" wrote:
> >
> >> The easiest way to do this would be with a PivotTable, but you could use
> >> the
> >> following formualae, assuming that your data is in A1:D14,
> >>
> >> in F2 (array formula*):
> >>
> >> =INDEX($A$2:$A$14&"-"&$D$2:$D$14,MATCH(0,COUNTIF(F$1:$F1,$A$2:$A$14&"-"&$D$2:$D$14),0))
> >>
> >> in G2:
> >>
> >> =LEFT($F2,FIND("-",$F2)-1)
> >>
> >> in H2:
> >>
> >> =TEXT(VALUE(RIGHT($F2,5)),"dd/mm/yyyy")
> >>
> >> in I2:
> >>
> >> =SUMPRODUCT(($A$2:$A$14=$G2)*($D$2:$D$14=VALUE($H2))*$C$2:$C$14)
> >>
> >> copy F2:I2 down to F14:I14, and hide column F.
> >>
> >>
> >> *to enter an array formula press Ctrl+Shift+Enter instead of just Enter.
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Stinky" <Stinky@discussions.microsoft.com> wrote in message
> >> news:AB6FB309-4A74-4FDA-804B-71022CFF336D@microsoft.com...
> >> > Can anyone help me with this one please? I have this table of data.
> >> > I'd
> >> > like
> >> > to be able to write a formula(s) which sums the No according to date
> >> > and
> >> > code, but then only returns a sum value (with the code and date in the
> >> > two
> >> > adjacent cells) if there is a value greater than 0.
> >> >
> >> > Code Ref No Date
> >> > 1 G/032/05/999 400600 212 19/03/2010
> >> > 2 G/032/03/001/999 400500 50 19/03/2010
> >> > 3 G/032/02/001/001 400400 170 19/03/2010
> >> > 4 G/032/05/999 400600 315 19/03/2010
> >> > 5 G/032/03/001/999 400500 300 19/03/2010
> >> > 6 G/032/05/999 400600 202 19/03/2010
> >> > 7 G/032/03/001/002 400500 85 19/03/2010
> >> > 8 G/032/05/999 400600 238 19/03/2010
> >> > 9 G/032/03/001/002 400500 170 19/03/2010
> >> > 10 G/032/02/001/001 400400 170 20/03/2010
> >> > 11 G/032/05/999 400600 53 20/03/2010
> >> > 12 G/032/03/001/999 400500 430 21/03/2010
> >> > 13 G/032/05/999 400600 371 21/03/2010
> >> >
> >> > The result of formula(s) would look something like:
> >> >
> >> > G/032/05/999 19/03/2010 967
> >> > G/032/03/001/999 19/03/2010 350
> >> > G/032/02/001/001 19/03/2010 170 and so on.....
> >> >
> >> > I think I'm wishing for the moon, but would solve a major headache in
> >> > my
> >> > daily working life!!!
> >>
>
|
|
0 |
|
Utf
4/20/2010 2:51:02 PM
Thanks for the feedback, it's always good to hear a happy ending... "Stinky" <Stinky@discussions.microsoft.com> wrote in message news:817AD937-9C07-493C-819E-6CC16488A082@microsoft.com... > In case you get reply notification, Steve, I just thought I'd let you know > that I conquered the PivotTables and it has completely revolutionised my > life!! I never would have known about it if you hadn't mentioned it - I > only wish I'd tried this forum 100 years ago (well, that's how long it > seems > I've been laboriously working with ridiculous amounts of data for > )..........I've now shown other colleagues how brilliant they are for what > we > do and it's been declared the 'discovery of the year'. > > Taking the time to respond to these threads is SO valuable. Thanks a > million. >
|
|
0 |
|
Steve
4/20/2010 3:18:21 PM
Similar Artilces:
URL into a view in CRM 3.0
I'd like to be able to set up a link from our intranet to the Account manager screen, passing in a view paramenter. Is there any way to do this? Any documentation anywhere? Being able to pass in query parameters to the Advanced Find screen would work for me to. Thanks. ------=_NextPart_0001_BF73CA4F Content-Type: text/plain Content-Transfer-Encoding: 7bit <AW@discussions.microsoft.com> wrote: > I'd like to be able to set up a link from our intranet to the Account manager > screen, passing in a view paramenter. Is there any way to do this? Any > documentatio...
Count Unique Values In A Filtered Row with Duplicates
Hello, I've tried to copy and paste below the resource spreadsheet I am working on. What I want to be able to do is list out each project and who the project manager is on that project (now some PMs can work on more than one so there are duplicates). Then I want to be able to subtotal the hours (in case I want to review just one project, one city, or one PM) but also remove the duplicates again. Can you help me out please? THANKS, Joe Jan-2006 Feb-2006 Total Hours: 411.00 411.00 Total Resources Total FTE: Total Unallocated: 0.00 0.00 Resource Name Role Regi...
Upgrading 1.2 to 3.0
We Planning to upgrade CRM 1.2 to 3.0 for our clients. We are trying to create a developer environment and perform the update. My question is if we go this route how can we install 1.2 on this dev servers (Key is unique as per domain). Can we have DEV or temp key for that particular domain? If YES... HOW? On the other hand if we install a fresh copy of 3.0 what is the process of data integration from old to new DB and what kinid of information we may loose throught this route. Thanks. As long as you have a different parent OU and a seperate CRM & SQL server, you can install a s...
Changing the default value of the revenue field
Hey all. Interesting question... Is there a way to change the default value on the revenue field on the opportunity form from system calculated to user provided. We don't use quotes here, so it is an extra mouse click everytime someone creates an opportunity. TIA, Dave Hi, this field not is mandatory , then you can let it blank and save record if you dont use quotas in this level best regards! "Dave J" wrote: > Hey all. > > Interesting question... > > Is there a way to change the default value on the revenue field on the > opportunity form from syste...
Intercept query parameter values from data refresh
Hi, Is there a way to determine the value a user specifies for a query parameter when using the "Refresh Data" command? I have a External data query to an SQL server database that contains a single parameter. I'd like to determine what the user entered for that parameter from within Excel vba code or other means. I'm running Excel XP on Windows XP. Any ideas would be greatly appreciated. Thanks in advance! Jim ...
row reference
i would like to sum a series of numbers in columnB and put in cell C1. which rows to sum depends on hard inputs in cells A1 (13) and A2 (29). in other words, i want to sum the numbers from B13 to B29 in this case. i know i can do this by setting C1 to: =sum(b13:b29), but the rows to sum will be changing frequently. next time A1 and A2 might be 9 and 36, respectively, thus summing cells B9:B36. i do not want to manually change the formula in C1 every time i change A1 and A2. i also do not want to create a macro. any thoughts? thanks, mike allen A couple of ways =SUM(INDEX(B:B,A1):IND...
SUMPRODUCT using Time between two values
Thanks in advance for any help you can provide me. I have a sheet with the following layout of data (shortened here and changed for obvious reasons) Venue Date Event Start Event End Act Disney 4/13/10 7:00 PM 10:00 PM Goofy MSG 2/13/10 1:00 PM 3:00 PM Basketball Park 3/14/10 6:00 PM 8:00 PM Harlem Globetrotters So, what I have is a user input a Venue, Date, Time Value, and I would need the Act returned... For example, Disney, 4/13/10, 8:25:13 PM, would ou...
Looups and multiple criteria
I have an spreadsheet with columns like: col A col D col E colF B230 8 1 0 F001 -5 0 10 F002 3 20 0 F010 5 -29 3 F014 40 3 7 F020 -25 -6 -100 F113 0 0 0 H002 34 -50 23 in ascendent order. I want to be able to Sum col D to colF if colA is between F001 and F020 except F010. ...
Change Value on Combo Box upon event
Is there a way that I can change the value on a Combo Box to the last record in its record source? I have a combo box that displays Fiscal Years, from 2000 to Current fiscal year 2007. Users can select any year they wish to pull data from based on the year selected. However, I want the combo Box to change its value to the current Fiscal year (which is the last record presented to them when they select the drop down) based on a specific event. Anybody have any ideas? Thanks! heyy try: Me.CourseID = Me.CourseID.ItemData(Me.CourseID.ListCount - 1) cheers jeff "Angel_G" ...
subract from sum?
How can I sum a column but excluded certain figures based on another cell Hi =SUM(B1:B100)-SUMIF(A1:A100,"X",B1:B100) or =SUMIF(A1:A100,"<>X",B1:B100) -- Regards Frank Kabel Frankfurt, Germany "starrpro" <starrpro@discussions.microsoft.com> schrieb im Newsbeitrag news:0EA544DC-37DD-4197-9344-2BFDFCC0DB66@microsoft.com... > How can I sum a column but excluded certain figures based on another cell ...
Moving CRM 3.0 to new domain
Could you tell me what tool (what version of RT)should be used to move CRM 3.0 to new domain. As I notcied in this newgroup, in similar questions the answer is that Redeployment Tool but for which version? On the www, where I can downlad Redeployment Tool, there is only RT for MS cRM 1.0 or 1.2 Thanks in advance Lidia You'll find the redeployment tool on the 'server setup' cd for crm 3.0 cheers Rich "Lidia" wrote: > Could you tell me what tool (what version of RT)should be used to move CRM > 3.0 to new domain. As I notcied in this newgroup, in similar que...
multiple empty accounts showed up
I have three accounts with Broker X, which I've tracked for some time with Money (2006 Premium). Only one of them was set up for automatic updates. As soon as I enabled the other two for automatic updates, Money created SEVEN new empty accounts ("Broker X Cash Account", "Broker X Cash Account #2", ... "Broker X Margin Account", etc.) separate from the two I wanted to connect. Next thing I know, I have NINE pending statements for these seven imaginary and empty accounts, displaying "bank balance" totalling somewhere in the range of $200,000! Go...
Need Help with a Sum of a Product of a Currency and Product
Does anyone have a way to simplify this formula and make it so it automatically includes newly added rows or cells? =SUM((SUM($K$9:L9)*$H$9),(SUM($K$11:L11)*$H$11),(SUM($K$13:L13)*$H$13), (SUM($K$15:L15)*$H$15),(SUM($K$17:L17)*$H$17),(SUM($K$19:L19)*$H$19), (SUM($K$21:L21)*$H$21),(SUM($K$23:L23)*$H$23),(SUM($K$25:L25)*$H$25), (SUM($K$27:L27)*$H$27),(SUM($K$29:L29)*$H$29),(SUM($K$31:L31)*$H$31), (SUM($K$33:L33)*$H$33),(SUM($K$35:L35)*$H$35),(SUM($K$37:L37)*$H$37), (SUM($K$39:L39)*$H$39),(SUM($K$41:L41)*$H$41),(SUM($K$43:L43)*$H$43), (SUM($K$45:L45)*$H$45),(SUM($K$47:L47)*$H$47),(SUM($K$49:L49)...
Searching multiple excel documents
When searching for P.O. numbers in windows 2000 basic search command I enter the *.xls in the files or folders field, and i enter the P.O. number in the Containing text field but no xls documents show up. i've selected to search through all subdirectories and am positive the P.O. number is in a xls document, as i've cut and pasted it to test this. I've tried doing alpha numeric searches that are withing xls documents and this returns nothing aswell. doing a pure text search in all documents does return positive, so my search funtion does work for containting text. does wind...
Sort hidden rows
Hi, In the knowledge base art 105111 it indicates that Excel 2002 has a problem with sorting hidden rows. I've tried to reproduce this problem but everytime my hidden colums are as well sorted (Excel 2002, sp2)... Has this problem been corrected in the updates. Why I'm asking : I've got this user who has this problem... and wanted to know if it's corrected in the updates. Thank you! I think that the KB article says that MS changed this behavior to not sort hidden rows. So I don't think MS sees it as a problem to be corrected. But I was confused with your reference to...
Migrate CRM 4.0 instance to another server on a different domain
Hello, What would be the best way to migrate our current CRM database to another server on a different domain but still within the same forest? We initially had intended on implementing our CRM at our off-site datacenter but due to some DNS/Network issues we weren't able to add users across different domains. Our deadline was fast approaching so we setup a server locally. Now that we have the issue resolved at our datacenter, we are ready to move the local database to the datacenter. What is involved with this process and what would be the best way to do this? Thank you very much...
Selecting Multiple Shapes and Lines
In Excel 2003, lines and shapes could easily be selected by clicking on the arrow in the drawings menu and drawings a box around the objects. I am now using Excel 2007, but have not found anywhere to change the coursor from the standard cross to a pointer. Is this one of the features that did not get transfered over, or have I just not found it yet? Thanks for the help. If I understood your question - try this: In the Ribbon - Click 'Home' > at the far end 'Edit' section > click on 'Search & Choose' > in the sub-menu click 'choose object...
how to show numbers that begin with 0 in excel
I need to type numbers into an excel spreadsheet some of them begin with a zero some do not. How can I have excel show the 0 in the numbers that begin with 0. If I type 0236547 the number shows as 236547. I don't want all the numbers I type to begin with zero. I am using Excel 2003. Thanks very much for your help. Best regards, Dee use a custom format with how ever many digits you need, 000000 -- Gary Keramidas Excel 2003 "Dee" <Dee@discussions.microsoft.com> wrote in message news:EA60FC8A-C7CB-432A-B2B2-EEEBF86D2646@microsoft.com... >...
[ANN] Office 2008 12.0.1 update
Hi All, The Office 20089 12.0.1 update is now available on Mactopia: http://www.microsoft.com/mac/downloads.mspx It should soon also be available through Auto-update. 114.1MB!!! The KB describing the update is not yet live on the MS site so at this time I do not yet have the release note, but it will be available here: http://go.microsoft.com/fwlink/?LinkId=108496 The updater is a .mpkg package (so it is installable through over a network through ARD :-) ). Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://...
Stop opening multiple versions of Excel when click on xls files
Excel 2002 SP3 Win XP HE Hi, If I click on an xls file in Windows Explorer, it properly starts Excel and opens the file. However, if I need another file opened, and click on another xls file within Windows Explorer, instead of opening the file only, it starts up a new instance of Excel. So I can find myself with 3 or 4 (or more) instances of Excel running simultaneously. It's not a huge problem but I believe not necessary and would like to see if I can find a way of only opening the file in question once Excel is already opened, etc. Any ideas? Thanks. On Jun 28, 2:55 pm, ste...
Cell formating and summing imperial weights
Hi all, Does anybody know how to format cells for using imperial weight (pounds, ounces and drams) and how do I then summ these values to als give the result in pounds, ounces and drams -- Wood ----------------------------------------------------------------------- Woody's Profile: http://www.msusenet.com/member.php?userid=575 View this thread: http://www.msusenet.com/t-187117693 On Wed, 9 Nov 2005 03:39:09 -0600, Woody <Woody.1ydm2c@no-mx.msusenet.com> wrote: > >Hi all, > >Does anybody know how to format cells for using imperial weights >(pounds, ounces and dram...
#Error when summing in Form
I add a NEW control to the Footer of a Subform with a Control Source as follows: =([AmtBilled]). This will give me a value from a record of the Detail section of the same Subform. If I change the Control Source to =Sum([AmtBilled]), I get #Error. All of my controls that contain the Sum function are NOW giving me this #Error. I made this to happen with some change I have made, but I can't figure out what I have done to cause this. Can someone point me in the direction to solving this problem? Thanks... You need to sum by control name not by column name. -- Dorian "...
MS Dynamics CRM 4.0 for Outlook Installation
Hello, I have successfully installed MS CRM server. When I try to install "MS Dynamics CRM 4.0 for Outlook", it is giving "MS Dynamics CRM for Outlook can not be installed on a computer where MS exchange server versions prior to MS exchange server 2007 are installed". I am using a VM with Exchange Server 2003 with SP 2. Please let me know what am I missing. Thanks, I did the same before and I'm currently setting up a new VPC image without Exchange. As you cannot install Exchange 2007 on a 32-bit OS, I uninstalled Exchange 2003 in my previous image but afterward...
Trying to sort by row and getting errors.
I'm trying to do a simple sort by rows instead of columns and every time I try to do it I get this message: This operation requires merged cells to be identically sized. What am I doing wrong? First of all, select all cells then Format>Cells>Alignment. Click the "merge cells" option a couple of times to clear it. Now try your sort. Merged cells cause many problems and are very rarely necessary. Use Center Across Selection instead. Gord Dibben MS Excel MVP On 11 Jun 2006 16:57:42 -0700, joe54345@gmail.com wrote: >I'm trying to do a simple sort by rows in...
applyng a multiple header row across pages in default table style
Hello, I want to set up the default table format on an RTF document that will take the fist 2 rows in my table and repeat them as a header on all the pages the table extends to. so I need to know 2 things 1. how do i edit the default table style 2. is it possible to set the first 2 rows as the header. the data in my table is generated automatically and I don't know in advance how big it will be. thank you 1. Use the Modify Style dialog box to modify a table style in Word. The exact steps depend on your version of Word. 2. As far as I know, this is not possible in...
I'd like to be able to set up a link from our intranet to the Account manager screen, passing in a view paramenter. Is there any way to do this? Any documentation anywhere? Being able to pass in query parameters to the Advanced Find screen would work for me to. Thanks. ------=_NextPart_0001_BF73CA4F Content-Type: text/plain Content-Transfer-Encoding: 7bit <AW@discussions.microsoft.com> wrote: > I'd like to be able to set up a link from our intranet to the Account manager > screen, passing in a view paramenter. Is there any way to do this? Any > documentatio...
Count Unique Values In A Filtered Row with Duplicates
Hello, I've tried to copy and paste below the resource spreadsheet I am working on. What I want to be able to do is list out each project and who the project manager is on that project (now some PMs can work on more than one so there are duplicates). Then I want to be able to subtotal the hours (in case I want to review just one project, one city, or one PM) but also remove the duplicates again. Can you help me out please? THANKS, Joe Jan-2006 Feb-2006 Total Hours: 411.00 411.00 Total Resources Total FTE: Total Unallocated: 0.00 0.00 Resource Name Role Regi...
Upgrading 1.2 to 3.0
We Planning to upgrade CRM 1.2 to 3.0 for our clients. We are trying to create a developer environment and perform the update. My question is if we go this route how can we install 1.2 on this dev servers (Key is unique as per domain). Can we have DEV or temp key for that particular domain? If YES... HOW? On the other hand if we install a fresh copy of 3.0 what is the process of data integration from old to new DB and what kinid of information we may loose throught this route. Thanks. As long as you have a different parent OU and a seperate CRM & SQL server, you can install a s...
Changing the default value of the revenue field
Hey all. Interesting question... Is there a way to change the default value on the revenue field on the opportunity form from system calculated to user provided. We don't use quotes here, so it is an extra mouse click everytime someone creates an opportunity. TIA, Dave Hi, this field not is mandatory , then you can let it blank and save record if you dont use quotas in this level best regards! "Dave J" wrote: > Hey all. > > Interesting question... > > Is there a way to change the default value on the revenue field on the > opportunity form from syste...
Intercept query parameter values from data refresh
Hi, Is there a way to determine the value a user specifies for a query parameter when using the "Refresh Data" command? I have a External data query to an SQL server database that contains a single parameter. I'd like to determine what the user entered for that parameter from within Excel vba code or other means. I'm running Excel XP on Windows XP. Any ideas would be greatly appreciated. Thanks in advance! Jim ...
row reference
i would like to sum a series of numbers in columnB and put in cell C1. which rows to sum depends on hard inputs in cells A1 (13) and A2 (29). in other words, i want to sum the numbers from B13 to B29 in this case. i know i can do this by setting C1 to: =sum(b13:b29), but the rows to sum will be changing frequently. next time A1 and A2 might be 9 and 36, respectively, thus summing cells B9:B36. i do not want to manually change the formula in C1 every time i change A1 and A2. i also do not want to create a macro. any thoughts? thanks, mike allen A couple of ways =SUM(INDEX(B:B,A1):IND...
SUMPRODUCT using Time between two values
Thanks in advance for any help you can provide me. I have a sheet with the following layout of data (shortened here and changed for obvious reasons) Venue Date Event Start Event End Act Disney 4/13/10 7:00 PM 10:00 PM Goofy MSG 2/13/10 1:00 PM 3:00 PM Basketball Park 3/14/10 6:00 PM 8:00 PM Harlem Globetrotters So, what I have is a user input a Venue, Date, Time Value, and I would need the Act returned... For example, Disney, 4/13/10, 8:25:13 PM, would ou...
Looups and multiple criteria
I have an spreadsheet with columns like: col A col D col E colF B230 8 1 0 F001 -5 0 10 F002 3 20 0 F010 5 -29 3 F014 40 3 7 F020 -25 -6 -100 F113 0 0 0 H002 34 -50 23 in ascendent order. I want to be able to Sum col D to colF if colA is between F001 and F020 except F010. ...
Change Value on Combo Box upon event
Is there a way that I can change the value on a Combo Box to the last record in its record source? I have a combo box that displays Fiscal Years, from 2000 to Current fiscal year 2007. Users can select any year they wish to pull data from based on the year selected. However, I want the combo Box to change its value to the current Fiscal year (which is the last record presented to them when they select the drop down) based on a specific event. Anybody have any ideas? Thanks! heyy try: Me.CourseID = Me.CourseID.ItemData(Me.CourseID.ListCount - 1) cheers jeff "Angel_G" ...
subract from sum?
How can I sum a column but excluded certain figures based on another cell Hi =SUM(B1:B100)-SUMIF(A1:A100,"X",B1:B100) or =SUMIF(A1:A100,"<>X",B1:B100) -- Regards Frank Kabel Frankfurt, Germany "starrpro" <starrpro@discussions.microsoft.com> schrieb im Newsbeitrag news:0EA544DC-37DD-4197-9344-2BFDFCC0DB66@microsoft.com... > How can I sum a column but excluded certain figures based on another cell ...
Moving CRM 3.0 to new domain
Could you tell me what tool (what version of RT)should be used to move CRM 3.0 to new domain. As I notcied in this newgroup, in similar questions the answer is that Redeployment Tool but for which version? On the www, where I can downlad Redeployment Tool, there is only RT for MS cRM 1.0 or 1.2 Thanks in advance Lidia You'll find the redeployment tool on the 'server setup' cd for crm 3.0 cheers Rich "Lidia" wrote: > Could you tell me what tool (what version of RT)should be used to move CRM > 3.0 to new domain. As I notcied in this newgroup, in similar que...
multiple empty accounts showed up
I have three accounts with Broker X, which I've tracked for some time with Money (2006 Premium). Only one of them was set up for automatic updates. As soon as I enabled the other two for automatic updates, Money created SEVEN new empty accounts ("Broker X Cash Account", "Broker X Cash Account #2", ... "Broker X Margin Account", etc.) separate from the two I wanted to connect. Next thing I know, I have NINE pending statements for these seven imaginary and empty accounts, displaying "bank balance" totalling somewhere in the range of $200,000! Go...
Need Help with a Sum of a Product of a Currency and Product
Does anyone have a way to simplify this formula and make it so it automatically includes newly added rows or cells? =SUM((SUM($K$9:L9)*$H$9),(SUM($K$11:L11)*$H$11),(SUM($K$13:L13)*$H$13), (SUM($K$15:L15)*$H$15),(SUM($K$17:L17)*$H$17),(SUM($K$19:L19)*$H$19), (SUM($K$21:L21)*$H$21),(SUM($K$23:L23)*$H$23),(SUM($K$25:L25)*$H$25), (SUM($K$27:L27)*$H$27),(SUM($K$29:L29)*$H$29),(SUM($K$31:L31)*$H$31), (SUM($K$33:L33)*$H$33),(SUM($K$35:L35)*$H$35),(SUM($K$37:L37)*$H$37), (SUM($K$39:L39)*$H$39),(SUM($K$41:L41)*$H$41),(SUM($K$43:L43)*$H$43), (SUM($K$45:L45)*$H$45),(SUM($K$47:L47)*$H$47),(SUM($K$49:L49)...
Searching multiple excel documents
When searching for P.O. numbers in windows 2000 basic search command I enter the *.xls in the files or folders field, and i enter the P.O. number in the Containing text field but no xls documents show up. i've selected to search through all subdirectories and am positive the P.O. number is in a xls document, as i've cut and pasted it to test this. I've tried doing alpha numeric searches that are withing xls documents and this returns nothing aswell. doing a pure text search in all documents does return positive, so my search funtion does work for containting text. does wind...
Sort hidden rows
Hi, In the knowledge base art 105111 it indicates that Excel 2002 has a problem with sorting hidden rows. I've tried to reproduce this problem but everytime my hidden colums are as well sorted (Excel 2002, sp2)... Has this problem been corrected in the updates. Why I'm asking : I've got this user who has this problem... and wanted to know if it's corrected in the updates. Thank you! I think that the KB article says that MS changed this behavior to not sort hidden rows. So I don't think MS sees it as a problem to be corrected. But I was confused with your reference to...
Migrate CRM 4.0 instance to another server on a different domain
Hello, What would be the best way to migrate our current CRM database to another server on a different domain but still within the same forest? We initially had intended on implementing our CRM at our off-site datacenter but due to some DNS/Network issues we weren't able to add users across different domains. Our deadline was fast approaching so we setup a server locally. Now that we have the issue resolved at our datacenter, we are ready to move the local database to the datacenter. What is involved with this process and what would be the best way to do this? Thank you very much...
Selecting Multiple Shapes and Lines
In Excel 2003, lines and shapes could easily be selected by clicking on the arrow in the drawings menu and drawings a box around the objects. I am now using Excel 2007, but have not found anywhere to change the coursor from the standard cross to a pointer. Is this one of the features that did not get transfered over, or have I just not found it yet? Thanks for the help. If I understood your question - try this: In the Ribbon - Click 'Home' > at the far end 'Edit' section > click on 'Search & Choose' > in the sub-menu click 'choose object...
how to show numbers that begin with 0 in excel
I need to type numbers into an excel spreadsheet some of them begin with a zero some do not. How can I have excel show the 0 in the numbers that begin with 0. If I type 0236547 the number shows as 236547. I don't want all the numbers I type to begin with zero. I am using Excel 2003. Thanks very much for your help. Best regards, Dee use a custom format with how ever many digits you need, 000000 -- Gary Keramidas Excel 2003 "Dee" <Dee@discussions.microsoft.com> wrote in message news:EA60FC8A-C7CB-432A-B2B2-EEEBF86D2646@microsoft.com... >...
[ANN] Office 2008 12.0.1 update
Hi All, The Office 20089 12.0.1 update is now available on Mactopia: http://www.microsoft.com/mac/downloads.mspx It should soon also be available through Auto-update. 114.1MB!!! The KB describing the update is not yet live on the MS site so at this time I do not yet have the release note, but it will be available here: http://go.microsoft.com/fwlink/?LinkId=108496 The updater is a .mpkg package (so it is installable through over a network through ARD :-) ). Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://...
Stop opening multiple versions of Excel when click on xls files
Excel 2002 SP3 Win XP HE Hi, If I click on an xls file in Windows Explorer, it properly starts Excel and opens the file. However, if I need another file opened, and click on another xls file within Windows Explorer, instead of opening the file only, it starts up a new instance of Excel. So I can find myself with 3 or 4 (or more) instances of Excel running simultaneously. It's not a huge problem but I believe not necessary and would like to see if I can find a way of only opening the file in question once Excel is already opened, etc. Any ideas? Thanks. On Jun 28, 2:55 pm, ste...
Cell formating and summing imperial weights
Hi all, Does anybody know how to format cells for using imperial weight (pounds, ounces and drams) and how do I then summ these values to als give the result in pounds, ounces and drams -- Wood ----------------------------------------------------------------------- Woody's Profile: http://www.msusenet.com/member.php?userid=575 View this thread: http://www.msusenet.com/t-187117693 On Wed, 9 Nov 2005 03:39:09 -0600, Woody <Woody.1ydm2c@no-mx.msusenet.com> wrote: > >Hi all, > >Does anybody know how to format cells for using imperial weights >(pounds, ounces and dram...
#Error when summing in Form
I add a NEW control to the Footer of a Subform with a Control Source as follows: =([AmtBilled]). This will give me a value from a record of the Detail section of the same Subform. If I change the Control Source to =Sum([AmtBilled]), I get #Error. All of my controls that contain the Sum function are NOW giving me this #Error. I made this to happen with some change I have made, but I can't figure out what I have done to cause this. Can someone point me in the direction to solving this problem? Thanks... You need to sum by control name not by column name. -- Dorian "...
MS Dynamics CRM 4.0 for Outlook Installation
Hello, I have successfully installed MS CRM server. When I try to install "MS Dynamics CRM 4.0 for Outlook", it is giving "MS Dynamics CRM for Outlook can not be installed on a computer where MS exchange server versions prior to MS exchange server 2007 are installed". I am using a VM with Exchange Server 2003 with SP 2. Please let me know what am I missing. Thanks, I did the same before and I'm currently setting up a new VPC image without Exchange. As you cannot install Exchange 2007 on a 32-bit OS, I uninstalled Exchange 2003 in my previous image but afterward...
Trying to sort by row and getting errors.
I'm trying to do a simple sort by rows instead of columns and every time I try to do it I get this message: This operation requires merged cells to be identically sized. What am I doing wrong? First of all, select all cells then Format>Cells>Alignment. Click the "merge cells" option a couple of times to clear it. Now try your sort. Merged cells cause many problems and are very rarely necessary. Use Center Across Selection instead. Gord Dibben MS Excel MVP On 11 Jun 2006 16:57:42 -0700, joe54345@gmail.com wrote: >I'm trying to do a simple sort by rows in...
applyng a multiple header row across pages in default table style
Hello, I want to set up the default table format on an RTF document that will take the fist 2 rows in my table and repeat them as a header on all the pages the table extends to. so I need to know 2 things 1. how do i edit the default table style 2. is it possible to set the first 2 rows as the header. the data in my table is generated automatically and I don't know in advance how big it will be. thank you 1. Use the Modify Style dialog box to modify a table style in Word. The exact steps depend on your version of Word. 2. As far as I know, this is not possible in...