Conditional Sum Problem
I have three columns of data something like this:
A B C
1 Balance Report Product Type
2 3,000 Y Oranges
3 1,000 N Oranges
4 4,000 N Apples
5 500 Y Pears
6 2,500 N Pears
7 8,000 Y Oranges
Range A2:A7 is named "Balance", B2:B7 is named "Report" and C2:C7 is named
"Product_Type".
I am trying the following:
=SUMPRODUCT((Balance)*(Product_Type="Oranges")*(Report="Y"))
The result I am getting is #N/A and I can't figure out what I am doing
wrong.
Can anyone help me?
Thanks,
Andrew.
a_d_mackenzie
excel.misc
78881 articles. 
5 followers.
4 Replies
572 Views
Andrew, It works fine for me with that data. Are you sure the names are correctly setup. Try it with actual ranges rather than names. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew Mackenzie" <a_d_mackenzie@hotmail.com> wrote in message news:exjzjHWAGHA.2900@TK2MSFTNGP10.phx.gbl... > I have three columns of data something like this: > > A B C > 1 Balance Report Product Type > 2 3,000 Y Oranges > 3 1,000 N Oranges > 4 4,000 N Apples > 5 500 Y Pears > 6 2,500 N Pears > 7 8,000 Y Oranges > > Range A2:A7 is named "Balance", B2:B7 is named "Report" and C2:C7 is named > "Product_Type". > > I am trying the following: > > =SUMPRODUCT((Balance)*(Product_Type="Oranges")*(Report="Y")) > > The result I am getting is #N/A and I can't figure out what I am doing > wrong. > > Can anyone help me? > > Thanks, > > Andrew. > > >
bob.phillips1It works for me if I have just one condition. If I leave out the Report="Y" condition its fine. So I agree that there appears to be something wrong with the way the named range is set up BUT it also doesn't work if I replace the name with the range. I also tried swapping Y and N for 1 and 0 but this didn't work either. Is there anything else I should look at in terms of the way the range is named? Cheers, Andrew. "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message news:#tPB$LWAGHA.1408@TK2MSFTNGP15.phx.gbl... > Andrew, > > It works fine for me with that data. Are you sure the names are correctly > setup. Try it with actual ranges rather than names. > > -- > HTH > > Bob Phillips > > (remove nothere from email address if mailing direct) > > "Andrew Mackenzie" <a_d_mackenzie@hotmail.com> wrote in message > news:exjzjHWAGHA.2900@TK2MSFTNGP10.phx.gbl... > > I have three columns of data something like this: > > > > A B C > > 1 Balance Report Product Type > > 2 3,000 Y Oranges > > 3 1,000 N Oranges > > 4 4,000 N Apples > > 5 500 Y Pears > > 6 2,500 N Pears > > 7 8,000 Y Oranges > > > > Range A2:A7 is named "Balance", B2:B7 is named "Report" and C2:C7 is named > > "Product_Type". > > > > I am trying the following: > > > > =SUMPRODUCT((Balance)*(Product_Type="Oranges")*(Report="Y")) > > > > The result I am getting is #N/A and I can't figure out what I am doing > > wrong. > > > > Can anyone help me? > > > > Thanks, > > > > Andrew. > > > > > > > >
If it behaves the same way with ranges rather than names I am flummoxed. As I say, it doesn't happen here, with 1,2 or 3 ranges. I cannot remember how to get #N/A with SUMPRODUCT, I can only create conditions that generate a #VALUE. Are you testing the ranges correctly, you know Balance - A2:A& etc.? Do you want to send me the workbook to see? Put Excel - Misc - Andrew Mackenzie in the subject, -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew Mackenzie" <a_d_mackenzie@hotmail.com> wrote in message news:O1SBgRWAGHA.3456@TK2MSFTNGP11.phx.gbl... > It works for me if I have just one condition. If I leave out the Report="Y" > condition its fine. So I agree that there appears to be something wrong > with the way the named range is set up BUT it also doesn't work if I replace > the name with the range. I also tried swapping Y and N for 1 and 0 but this > didn't work either. Is there anything else I should look at in terms of the > way the range is named? > > Cheers, > > Andrew. > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message > news:#tPB$LWAGHA.1408@TK2MSFTNGP15.phx.gbl... > > Andrew, > > > > It works fine for me with that data. Are you sure the names are correctly > > setup. Try it with actual ranges rather than names. > > > > -- > > HTH > > > > Bob Phillips > > > > (remove nothere from email address if mailing direct) > > > > "Andrew Mackenzie" <a_d_mackenzie@hotmail.com> wrote in message > > news:exjzjHWAGHA.2900@TK2MSFTNGP10.phx.gbl... > > > I have three columns of data something like this: > > > > > > A B C > > > 1 Balance Report Product Type > > > 2 3,000 Y Oranges > > > 3 1,000 N Oranges > > > 4 4,000 N Apples > > > 5 500 Y Pears > > > 6 2,500 N Pears > > > 7 8,000 Y Oranges > > > > > > Range A2:A7 is named "Balance", B2:B7 is named "Report" and C2:C7 is > named > > > "Product_Type". > > > > > > I am trying the following: > > > > > > =SUMPRODUCT((Balance)*(Product_Type="Oranges")*(Report="Y")) > > > > > > The result I am getting is #N/A and I can't figure out what I am doing > > > wrong. > > > > > > Can anyone help me? > > > > > > Thanks, > > > > > > Andrew. > > > > > > > > > > > > > > >
Hi Bob, Thanks for your efforts (and in fact your help becuase you pointed me in the right direction). I have now figured out the problem - my named ranges did not all start and end with the same row. Andrew "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message news:#F46NfXAGHA.220@TK2MSFTNGP10.phx.gbl... > If it behaves the same way with ranges rather than names I am flummoxed. As > I say, it doesn't happen here, with 1,2 or 3 ranges. I cannot remember how > to get #N/A with SUMPRODUCT, I can only create conditions that generate a > #VALUE. Are you testing the ranges correctly, you know Balance - A2:A& etc.? > > Do you want to send me the workbook to see? Put Excel - Misc - Andrew > Mackenzie in the subject, > > -- > HTH > > Bob Phillips > > (remove nothere from email address if mailing direct) > > "Andrew Mackenzie" <a_d_mackenzie@hotmail.com> wrote in message > news:O1SBgRWAGHA.3456@TK2MSFTNGP11.phx.gbl... > > It works for me if I have just one condition. If I leave out the > Report="Y" > > condition its fine. So I agree that there appears to be something wrong > > with the way the named range is set up BUT it also doesn't work if I > replace > > the name with the range. I also tried swapping Y and N for 1 and 0 but > this > > didn't work either. Is there anything else I should look at in terms of > the > > way the range is named? > > > > Cheers, > > > > Andrew. > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message > > news:#tPB$LWAGHA.1408@TK2MSFTNGP15.phx.gbl... > > > Andrew, > > > > > > It works fine for me with that data. Are you sure the names are > correctly > > > setup. Try it with actual ranges rather than names. > > > > > > -- > > > HTH > > > > > > Bob Phillips > > > > > > (remove nothere from email address if mailing direct) > > > > > > "Andrew Mackenzie" <a_d_mackenzie@hotmail.com> wrote in message > > > news:exjzjHWAGHA.2900@TK2MSFTNGP10.phx.gbl... > > > > I have three columns of data something like this: > > > > > > > > A B C > > > > 1 Balance Report Product Type > > > > 2 3,000 Y Oranges > > > > 3 1,000 N Oranges > > > > 4 4,000 N Apples > > > > 5 500 Y Pears > > > > 6 2,500 N Pears > > > > 7 8,000 Y Oranges > > > > > > > > Range A2:A7 is named "Balance", B2:B7 is named "Report" and C2:C7 is > > named > > > > "Product_Type". > > > > > > > > I am trying the following: > > > > > > > > =SUMPRODUCT((Balance)*(Product_Type="Oranges")*(Report="Y")) > > > > > > > > The result I am getting is #N/A and I can't figure out what I am doing > > > > wrong. > > > > > > > > Can anyone help me? > > > > > > > > Thanks, > > > > > > > > Andrew. > > > > > > > > > > > > > > > > > > > > > > > >
Similar Artilces:
Today Conditional Format
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...
Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...
Sending XML data to asp server... Is there any problem?
Hi, I transform client XML data to IIS server by WinInet API. In IIS server, asp parse request XML data and result save in MS-SQL. I can read XML data by this function, but I can send XML data... The function that I use is that... BOOL CXMLUtil::RequestHttpServer(LPCTSTR lpszMethod, LPCTSTR lpszURL, LPCTSTR lpszRequest, LPCTSTR lpszHeader, CString &strResponse, CString &strStatus) { BOOL fResult = TRUE; CInternetSession iSession; CHttpConnection *pHttpConnection = NULL; CHttpFile *pHttpFile = NULL; CString str_ErrorText; DWORD dwErrorCode = 0; CString strUrl = lpszURL; ...
Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...
Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...
Problem installing Vista SP2
I am having a problem installing Vista SP2 on a laptop. The laptop is an HP with Vista Home Premium SP1 originally installed. When I try to install SP2, (either from Microsoft Update or downloaded manually), I get the message that SP1 is not installed. However, it IS installed. I have run CheckSUR, checked for Malware, disabled anti-virus programs, booted with a selective startup, all to no avail. Help! Hi, there is a known issue where SP2 isn't offered in windows update unless every post SP1 update important or otherwise has been installed. This doesn't ...
Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...
Odd date problem
I'm reposting this as this is a very strange problem in Access. I'm using 2003. After my entry is an entry by missinglinq via AccessMonster.com who was able to reproduce the error. Looking forward to comments/fixes. Thanks, Rob I have the following in the DblClick event for the control CaseStart which is a date/time field. The problem is that once the control is double clicked the form remains in edit mode, even though I've told it not be editable. Additionally, when the control is double clicked, it shoudn't enter in the time until the Edit button has been clicked o...
Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...
How do i use Conditional formatting for 3+ conditions ?
I have a text box in a report which highlights in colour current members in a list of past and present member names. I have six types of current members that I want to highlight, but with conditional formatting I can only use 3 conditions in the members [Type] field. I have tried [Type]="Full member" Or "Life member" but the "Or" doesn't work ! Is there a way to do this ? thanks .. Roger CF is limited to 3 contitions, but you can use OR in those expressions. If you set Condition1 to Expression, you can use: ([Type] = "Full member") Or (...
Help with Conditional formatting with Dates
Hi there, I have a spreadsheet in which I have to monitor various dates which are in different columns (i.e. (1) Date Tender Released, (2) Date Company Selected, (3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...). Would if be possible to Conditional Format and have diffirent ROWs highlighted for each individual Contracts (I have 300 running contracts running) every time a date is filled in these different columns: Initial Step (1) = Yellow (Call for Tender released) Step (2) = Blue (Company Selected) Step (3) = Brown (Contract under Process) Step (4) = Green (Contract Si...
Excel print out problem
I have export several files from Lotus Notes to MS XP Excel files. When the click print preview those files at Excel, the file is OK. However, if the file is more then 1 page, the 1 st row will be repeated on everypage after printing. If I delete the 1 st row, then the 2 nd row will become the 1 st row and the print out is OK. I try to clear all the format in the cell and then re-created the format at excel file but when the file print out, it will keep repeating the 1 st row again. Any help? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com...
outlook strange print problem
outlook 2003, sp3 all office updates applied on winxp machine with at least sp2, but i didn't check that part. when the user goes to print, they get a multiple page print job even though the email has only one page. at the top center of every page in roughly 16-18 pt font is P... and the very middle of the page prints a small small section of the email. the next page has another small small section of the email, and so on until all pages have printed. i have a PDF of what i mean if anyone needs to see visual proof of what is happening. the sections that are printing are coming from th...
Conditional null value
Wondering if anyone can suggest a way to simplify the following code, which I’m using in the OnFormat event of a report. If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me![PtAcct#]), ".", " ") End If If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field194] = Replace(DLookup("[ICD...
stat analysis problem
et al, Here is the problem I'm having. I have a column of data (say column 'b') that has numbers in it. both in the format of '5' '98' '444' etc. What I'm trying to do is come up w/ a formula that will tell me the number of occurrences in that column of any number. i.e.. say 5. when I would do count on the above I get an answer of 3 ( =count(cell1:cell2,5). Or even in the formula when I put the 5 in as '5'. Is there a way to do this in excel? TIA. -JD The COUNT function counts the number of cells with containing a number...any number...
IF nested problem ..!
Good afternoon, I have been attempting to create a formula to do the following: In three columns: B, C & H. If B3 & C3 do not contain a value I want to H3 to also be blank despite H2 containing a figure. However, if B2 and or C2 contain a figure, I wish to display the result - if this makes sense! The existing formula in H3 is: =H2+C3-B3 and I've been trying to create the appropriate formula without success. What am I doing wrong? Regards Peter =IF(B3 & C3="","",H2+C3-B3) -- Gary''s Student - gsnu201001 "Peter&...
Help
Forgive me if "Conditional Sums" is not the accurate word for what I am trying to accomplish... I'm at a loss for what to call this situation. What I have is a spreadsheet totaling attendance figures for classes taught by two people (Dave and Cary). Column A lists their name, and next to it in Column B is the number of people who attended that class. What I need is a formula that will look at the sheet and report in a given cell the total for attendance for each person. I can work basic formulas like SUM, but that will give the total for the entire column, when what I n...
Powerpoint Mac problem
Hello. I have a 2.5gHz MacBook Pro running OS X 10.5.8. I recently attempted to use PowerPoint for the first time on this machine and all of my templates (including the default templates) and notes pages display in Greek (the language, not letter size). I've gone through all of my preference/property settings, made sure the default language was English...not sure what else I can do. If I create new text on the page myself I can plug in an acceptable font, but it defeats the purpose of using PPT for me then. I'd like to use the templates. Any suggestions are welcome. T...
XML Receipt and Conditions
I wanted to make a small adjustment to the receipt when a certain condition is met. If a particular word (eg. family) is typed into the comment on an item I wanted something slightly different to happen to the receipt I've just about got it, just stuck on the condition tag <IF> <CONDITION>len(Entry.Comment)</CONDITION> <THEN> I can get it using len but this makes it happen for any word, is there anyway I can make it so that it is more specific ie Entry.Comment contains '%family%' try <IF> <CONDITION>Entry.Comment like '%family%'&...
Problem in displaying number
Having problem in displaying number! A1=25.25 (outcome of some calculations, formatted as number with two decimal places) In A10, I am trying to display Please pay a sum of Rs. 25.25 only I am using the formula A10="Please pay a sum of Rs. "&A1&" only" Problem is coming with amounts like 25.00 or 25.50, the cell displays for 25.50 ----> Please pay a sum of Rs. 25.5 only (what I want is 25.50) for 25.00-----> Please pay a sum of Rs. 25 only (what I want is 25.00) separating Integar and Fraction part is not of help. Using =right(a1...
Category problem
Hi, Is it possible to add/delete category in Money Essential ??? I can't do this. Mariusz I note that no one has respond to your query. That is probably because there is nobody in this group who uses MEss and thus has any idea how to answer your question. Essentials is a totally useless product. If you actually paid for it, I would suggest you return under the 30 day money back guarantee. If you got it with your new computer, just delete it. Buy Money Plus Deluxe (or Quicken) if you want a decent way of tracking your finances. -- Peace, BobJ "TodLoch" <TodLoch@di...
XL2000 "Custon Views" "Column width" Problem
Although the help file states that custom views will change colum widths, it does not. I have seen a few postings stating the sam problem but no solutions. Does anyone know if there is a fix for this? If not, is it possible to use Autofit just for a range in a column not for the whole column? De -- De ----------------------------------------------------------------------- Des's Profile: http://www.msusenet.com/member.php?userid=69 View this thread: http://www.msusenet.com/t-187096409 Instead of using custom views, could you just record some macros that set the columnwidths the way yo...
conditionally restricting values in excel
Hello, I want to restrict values in a particular column based on the value in other column. For eg. if column A has value int then column B should be restricted to values 1,2,3,4,5 if column A has value bool then column B should be restricted to values true and false. if column A has value string I dont want any validation to be done and the list box should not appear. Is this possible..... How do I achieve this ? Can I use macros to do this? How? Thanks Hi as a starting point: http://www.contextures.com/xlDataVal02.html >-----Original Message----- >Hello, >I want to restrict ...
CRM 1.2 Install Problem
I am attempting to install CRM 1.2 on a new Windows 2003 Server I just added to the domain. It has all the prereq's loaded SQL 2000, sp3a, msxml, IIS, etc... However, when I attempt to install CRM, I get the message that I need to be part of the Domain Admins group in order to do this. I am logged in with the Domain Admin account. I was able to add the new server to the domain earlier today. Also, about two weeks ago, I installed CRM 1.2 on a test server in this same domain using the same login and password that I am using today. My only thought is that somehow AD is not recognizin...
auto sum function
I just realized my final sum changes if I use the "sort ascending" function..... How do I fix this ? thank u Hi where is your sum formula located? -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > I just realized my final sum changes if I use the "sort > ascending" function..... > > How do I fix this ? > > thank u Down a colum But also, i just found out info changes on each lines when i use the "sort ascending" tool... >-----Original Message----- >Hi >where is your sum formula located? &...
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...
Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...
Sending XML data to asp server... Is there any problem?
Hi, I transform client XML data to IIS server by WinInet API. In IIS server, asp parse request XML data and result save in MS-SQL. I can read XML data by this function, but I can send XML data... The function that I use is that... BOOL CXMLUtil::RequestHttpServer(LPCTSTR lpszMethod, LPCTSTR lpszURL, LPCTSTR lpszRequest, LPCTSTR lpszHeader, CString &strResponse, CString &strStatus) { BOOL fResult = TRUE; CInternetSession iSession; CHttpConnection *pHttpConnection = NULL; CHttpFile *pHttpFile = NULL; CString str_ErrorText; DWORD dwErrorCode = 0; CString strUrl = lpszURL; ...
Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...
Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...
Problem installing Vista SP2
I am having a problem installing Vista SP2 on a laptop. The laptop is an HP with Vista Home Premium SP1 originally installed. When I try to install SP2, (either from Microsoft Update or downloaded manually), I get the message that SP1 is not installed. However, it IS installed. I have run CheckSUR, checked for Malware, disabled anti-virus programs, booted with a selective startup, all to no avail. Help! Hi, there is a known issue where SP2 isn't offered in windows update unless every post SP1 update important or otherwise has been installed. This doesn't ...
Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...
Odd date problem
I'm reposting this as this is a very strange problem in Access. I'm using 2003. After my entry is an entry by missinglinq via AccessMonster.com who was able to reproduce the error. Looking forward to comments/fixes. Thanks, Rob I have the following in the DblClick event for the control CaseStart which is a date/time field. The problem is that once the control is double clicked the form remains in edit mode, even though I've told it not be editable. Additionally, when the control is double clicked, it shoudn't enter in the time until the Edit button has been clicked o...
Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...
How do i use Conditional formatting for 3+ conditions ?
I have a text box in a report which highlights in colour current members in a list of past and present member names. I have six types of current members that I want to highlight, but with conditional formatting I can only use 3 conditions in the members [Type] field. I have tried [Type]="Full member" Or "Life member" but the "Or" doesn't work ! Is there a way to do this ? thanks .. Roger CF is limited to 3 contitions, but you can use OR in those expressions. If you set Condition1 to Expression, you can use: ([Type] = "Full member") Or (...
Help with Conditional formatting with Dates
Hi there, I have a spreadsheet in which I have to monitor various dates which are in different columns (i.e. (1) Date Tender Released, (2) Date Company Selected, (3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...). Would if be possible to Conditional Format and have diffirent ROWs highlighted for each individual Contracts (I have 300 running contracts running) every time a date is filled in these different columns: Initial Step (1) = Yellow (Call for Tender released) Step (2) = Blue (Company Selected) Step (3) = Brown (Contract under Process) Step (4) = Green (Contract Si...
Excel print out problem
I have export several files from Lotus Notes to MS XP Excel files. When the click print preview those files at Excel, the file is OK. However, if the file is more then 1 page, the 1 st row will be repeated on everypage after printing. If I delete the 1 st row, then the 2 nd row will become the 1 st row and the print out is OK. I try to clear all the format in the cell and then re-created the format at excel file but when the file print out, it will keep repeating the 1 st row again. Any help? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com...
outlook strange print problem
outlook 2003, sp3 all office updates applied on winxp machine with at least sp2, but i didn't check that part. when the user goes to print, they get a multiple page print job even though the email has only one page. at the top center of every page in roughly 16-18 pt font is P... and the very middle of the page prints a small small section of the email. the next page has another small small section of the email, and so on until all pages have printed. i have a PDF of what i mean if anyone needs to see visual proof of what is happening. the sections that are printing are coming from th...
Conditional null value
Wondering if anyone can suggest a way to simplify the following code, which I’m using in the OnFormat event of a report. If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me![PtAcct#]), ".", " ") End If If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field194] = Replace(DLookup("[ICD...
stat analysis problem
et al, Here is the problem I'm having. I have a column of data (say column 'b') that has numbers in it. both in the format of '5' '98' '444' etc. What I'm trying to do is come up w/ a formula that will tell me the number of occurrences in that column of any number. i.e.. say 5. when I would do count on the above I get an answer of 3 ( =count(cell1:cell2,5). Or even in the formula when I put the 5 in as '5'. Is there a way to do this in excel? TIA. -JD The COUNT function counts the number of cells with containing a number...any number...
IF nested problem ..!
Good afternoon, I have been attempting to create a formula to do the following: In three columns: B, C & H. If B3 & C3 do not contain a value I want to H3 to also be blank despite H2 containing a figure. However, if B2 and or C2 contain a figure, I wish to display the result - if this makes sense! The existing formula in H3 is: =H2+C3-B3 and I've been trying to create the appropriate formula without success. What am I doing wrong? Regards Peter =IF(B3 & C3="","",H2+C3-B3) -- Gary''s Student - gsnu201001 "Peter&...
Help
Forgive me if "Conditional Sums" is not the accurate word for what I am trying to accomplish... I'm at a loss for what to call this situation. What I have is a spreadsheet totaling attendance figures for classes taught by two people (Dave and Cary). Column A lists their name, and next to it in Column B is the number of people who attended that class. What I need is a formula that will look at the sheet and report in a given cell the total for attendance for each person. I can work basic formulas like SUM, but that will give the total for the entire column, when what I n...
Powerpoint Mac problem
Hello. I have a 2.5gHz MacBook Pro running OS X 10.5.8. I recently attempted to use PowerPoint for the first time on this machine and all of my templates (including the default templates) and notes pages display in Greek (the language, not letter size). I've gone through all of my preference/property settings, made sure the default language was English...not sure what else I can do. If I create new text on the page myself I can plug in an acceptable font, but it defeats the purpose of using PPT for me then. I'd like to use the templates. Any suggestions are welcome. T...
XML Receipt and Conditions
I wanted to make a small adjustment to the receipt when a certain condition is met. If a particular word (eg. family) is typed into the comment on an item I wanted something slightly different to happen to the receipt I've just about got it, just stuck on the condition tag <IF> <CONDITION>len(Entry.Comment)</CONDITION> <THEN> I can get it using len but this makes it happen for any word, is there anyway I can make it so that it is more specific ie Entry.Comment contains '%family%' try <IF> <CONDITION>Entry.Comment like '%family%'&...
Problem in displaying number
Having problem in displaying number! A1=25.25 (outcome of some calculations, formatted as number with two decimal places) In A10, I am trying to display Please pay a sum of Rs. 25.25 only I am using the formula A10="Please pay a sum of Rs. "&A1&" only" Problem is coming with amounts like 25.00 or 25.50, the cell displays for 25.50 ----> Please pay a sum of Rs. 25.5 only (what I want is 25.50) for 25.00-----> Please pay a sum of Rs. 25 only (what I want is 25.00) separating Integar and Fraction part is not of help. Using =right(a1...
Category problem
Hi, Is it possible to add/delete category in Money Essential ??? I can't do this. Mariusz I note that no one has respond to your query. That is probably because there is nobody in this group who uses MEss and thus has any idea how to answer your question. Essentials is a totally useless product. If you actually paid for it, I would suggest you return under the 30 day money back guarantee. If you got it with your new computer, just delete it. Buy Money Plus Deluxe (or Quicken) if you want a decent way of tracking your finances. -- Peace, BobJ "TodLoch" <TodLoch@di...
XL2000 "Custon Views" "Column width" Problem
Although the help file states that custom views will change colum widths, it does not. I have seen a few postings stating the sam problem but no solutions. Does anyone know if there is a fix for this? If not, is it possible to use Autofit just for a range in a column not for the whole column? De -- De ----------------------------------------------------------------------- Des's Profile: http://www.msusenet.com/member.php?userid=69 View this thread: http://www.msusenet.com/t-187096409 Instead of using custom views, could you just record some macros that set the columnwidths the way yo...
conditionally restricting values in excel
Hello, I want to restrict values in a particular column based on the value in other column. For eg. if column A has value int then column B should be restricted to values 1,2,3,4,5 if column A has value bool then column B should be restricted to values true and false. if column A has value string I dont want any validation to be done and the list box should not appear. Is this possible..... How do I achieve this ? Can I use macros to do this? How? Thanks Hi as a starting point: http://www.contextures.com/xlDataVal02.html >-----Original Message----- >Hello, >I want to restrict ...
CRM 1.2 Install Problem
I am attempting to install CRM 1.2 on a new Windows 2003 Server I just added to the domain. It has all the prereq's loaded SQL 2000, sp3a, msxml, IIS, etc... However, when I attempt to install CRM, I get the message that I need to be part of the Domain Admins group in order to do this. I am logged in with the Domain Admin account. I was able to add the new server to the domain earlier today. Also, about two weeks ago, I installed CRM 1.2 on a test server in this same domain using the same login and password that I am using today. My only thought is that somehow AD is not recognizin...
auto sum function
I just realized my final sum changes if I use the "sort ascending" function..... How do I fix this ? thank u Hi where is your sum formula located? -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > I just realized my final sum changes if I use the "sort > ascending" function..... > > How do I fix this ? > > thank u Down a colum But also, i just found out info changes on each lines when i use the "sort ascending" tool... >-----Original Message----- >Hi >where is your sum formula located? &...