Reply:

Can I exclude the criteria Value from the query results?
I am using the [Dupe_Joiner_Title_ID] to match a list of records from a duplicates table against an ID form field, but want to exclude the actual value that the query uses as the criteria from being seen in the continous form that is linked to the query, but shows all other matching records for the form's current record. This is the criteria code: [Forms]![frm_Runs]![frm_Street_Joiner_Main].[Form]![frm_Street_Joiner_Sub].[Form]![Joiner_Title_ID] that is in the 'Dupe_Joiner_Title_ID: Joiner_Title_ID' column. This is my SQL: SELECT Tbl_Street_Joiner_Dupes.Address, Tbl_Stree...

Earned Value Report in Project 2007
I'm using MS Project 2007, have input all my tasks, costs, set baseline so all my numbers are populating great. I'm using the visual report to generate the earned value over time report (the pivot table and chart). Unfortunately while I am really good at most things Excel, pivot tables is not one of them. Project is populating the table with Quarters 1, 2 and 3. I only need Quarter 1 for my report. Is there a way to hide or delete the other two quarters from the table? Thanks for the help! Teri Hi Teri, I am guessing that you have your "Time Weekly Calendar&q...

formula to lookup a value in a range
Hi. I am trying to find a formula that would lookup the value of a cel at varying locations in a list. for example =AH3 + 25 to lookup cel AH28 where the number 25 is taken from a calculation. any suggestion would be appreciated -- richardmon ----------------------------------------------------------------------- richardmont's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3498 View this thread: http://www.excelforum.com/showthread.php?threadid=54719 Use the INDIRECT function. E.g, =INDIRECT("AH"&3+A1) "richardmont" <richardmon...

Returning a reference to a control in a form to a query
This is my situation: I have a form that I might use in more than one place, so I don't want to use a fully qualified reference to a control on it within queries on the form. Also, it is fairly deep in the structure and I've never found referencing a control on a deeply nested subform intuitive. So what I want to do is have a function that simply returns something like Me.Control1.Value. The problem is, if I put that function on the form's module where it will know about "Me", it isn't recognized by the query. What's the best way to handle this? Thanks...

ClearContents if #VALUE! found in cell
Hi All........ This code works fine for a text string as "oldest", but I want it to work if the cell value is #VALUE! (basically, clear or delete the row is the #VALUE! error is found) Dim oldest As String oldest = Range("data!k1").Value 'value is a text string With Sheets("ALL12") lastrow = .Cells(.Rows.Count, "a").End(xlUp).Row For r = lastrow To 12 Step -1 If .Cells(r, "b").Value Like oldest Then .Rows(r).EntireRow.ClearContents End If Next r End With Any help would be ...

Filtering Based in a cell value
I have a list which I want to filter based on the value of a cell that is not in that list. Any ideas? eg List of values to be filtered in Column X aaa aaa bbb bbb ccc ddd eee fff ggg The value I want to filter by in A1 derived from Data validation list on separate sheet aaa I want to filter out all the values that are not aaa TIA Steve How about a helper column with a formula like: =a2=sheet2!$a$1 (dragged down) Then you can filter on True/False for that column. Steve Wright wrote: > > I have a list which I want to filter based on the value of a cell that is > not...

Event ID 1221
I read somewhere today that I should see event IDs 1221 in the logs on our Exchange server, and they would show the "white space" in the IS. Maintenance is turned on but, we have NO 1221 logs. What could cause this? Thanks And you are looking in the Application Log in Event Viewer? -- Mark Fugatt "JohnB" <jbrigan@yahoo.com> wrote in message news:%232vZlI8gEHA.2928@TK2MSFTNGP10.phx.gbl... >I read somewhere today that I should see event IDs 1221 in the logs on our > Exchange server, and they would show the "white space" in the IS. > Maintenance ...

Function to format a value into percent with no decimal
I have about 50 rows of data, each with 2 columns. The first column (A) holds a number; the format is General. The second column (B) divides the number in A over 51, using this formula =A1/51 (and =A2/51, =A3/51, etc.). The format for column B is Percentage with 0 decimal places, so the results look like 25%, 4%, etc. I'd like to create a third column (C) which concatenates the results of columns A & B to yield something like: 13 (25%) But instead I get: 13 (0.254901960784314) The function I'm using is: =B1 & " (" & C1 & ")" I'm thinking I...

Usage of // and $ in <xsl:value-of ...
(Type your message here) -------------------------------- From: Aravind G Hello all, I have a peculiar situation. Following is a small xml snippet in use <ROOTNODE> <SPSNODE> <EXTERNALMESSAGE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <PHILIPSRIS> <PERSON xmlns:dt="urn:schemas-microsoft-com:datatypes"> <PATIENT> <SERVICEEPISODE> <ORDERENTRY> <ORDER_STATUS dt:dt=&quo...

Using a macro to hide cells with out a value?
I have a spec sheet from a manufacturer that has shipping weight,volume, pallet count, and price listed per item. I use that sheet to enter purchase quantities per item and let it calculate the total weight, cubic ft, pallet count. and the purchase price. My question is can i make a macro that will hide all of the rows that don't contain a value in column A1 (order quantiti) so when i print the sheet, it will only include items that are desired for that particular order. The entire sheet contains a few hundred rows so it makes it to large to print the whole thing. Hi Scott See http://...

weighting a value?
Let's say I have 10 hours to accomplish 10 tasks. Having previousl accomplished each of these tasks I know some will take longer tha others. Let's say I want to weight them into three categories, easiest receives a weight of 1, harder 1.5 and harder yet 2.5. Wha Excel trick, or someones know how, would be used to spread my 10 hour appropriately over the 10 tasks -- jvan10 ----------------------------------------------------------------------- jvan100's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2922 View this thread: http://www.excelforum.com/showt...

DIsplaying negative time values
Hi, I have two columns of time values. I need to determine the difference between the two values and return either a positive or negative time value. The table below is in hours and minutes Time A Time B 09:00:00 09:00:00 09:00:00 09:03:00 09:00:00 08:58:00 In the above example First row should be "00:00:00", Second row should be "00:03:00", and Third row should be "-00:02:00" When subtracting Time B from Time A for the first two rows i get the correct value, but the third row returns as "##########...

Styles and Set Numbering Value problem
Running MS Office Pro 2007 with Windows XP Pro. I have styles defined for numbered lists. These styles have simple characteristics, like indent, space before, etc. - nothing special. I want the numbered lists to appear throughout the document, with each starting at the number 1. When I try to start a new list using the Set Numbering Value dialog, I get a message saying "Changing the number format for this list updates the style "NumList" and reapplies the style to each paragraph. Do you want to continue?" If I select no, the starting number is not chan...

Changing line appearance for future values
Using Excel 2007. Is it possible to change the appearance of a line in a chart for values in the future (like projections)? I would like to go from solid to dashed and keep the same color and weight. Either you format line pieces individually (manual work). Either you use 2 series to plot in the chart. Use IF functions to see on which series a given value should be shown. Use NA() for values that should not be plotted. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "RhysPieces" wrote: > Using Excel 2007. Is it possible to change the appearance of a line...

Conditional formating based on another cell value
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I've managed to use the solution described below, but I haven't been able to figure out how to use this for formating a cell based on a date entered into another cell. <br><br>Example: When 1/15/10 is entered into AG4, P4 is highlighted green. <br><br>Any help would be appreciated. <br> ----- <br> &quot;On 6/30/08 7:54 PM, in article 59b52d02.-1@webcrossing.caR9absDaxw, <br> &quot;Simon@officeformac.com&quot; wrote:http://www.officeformac.com/macimages/btn_publish.gif &l...

Returning last number(time) in a row of cells
Need some help with this one please; Row A4 thru J4 A4 B4 C4 D4 E4 F4 G4 H4 I4 J4 12:00 15:00 16:00 I need to return to cell K4 the last number in the above row. I do not know what formula to use. Any help would be greatly appreciated. There are several ways to achieve that, - here are two: http://img9.imageshack.us/img9/9398/nonamegi.png Micky "v1rt8" wrote: > Need some help with this one please; > Row A4 thru J4 > A4 B4 C4 D4 E4 F4 G4 H4 I4 J4 > 12:00 15:00 16:00 > ...

Return cell characters after space
I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'. As there are many other examples, I need a function that will seek out the last ' ' in the cell content and then return all characters to the right hand-side of this ' '. e.g. 'Mr K Peters' would return 'Peters' Please let me know if I can clarify! Hi Andy =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)) -- Jacob (MVP - Excel) "Andy" wrote: > I have a cell with 'Mr J. Smith' and want to return in an...

Transferring option group value
I'm trying to create a form for adding new record only. How to do that ?. In that from,I want to put an option button ( group ) to shown or hide some text box ( control ) in my forms.But I also want to transferr the option group value to a field once the form close or the Save_cmd is clicked. Then in another qury I need to join the value transferred from the option group with another table so that the data appear as a text in another table. Please advice how to do that since I have no idea with coding? shiro wrote: >I'm trying to create a form for adding new record only. >...

How do I make values appear in another tab?
I am trying to set up an inspection database I need the values from other pages to appear when they go overdue in a specific cell. Please help!!! Could you give a specific example? If I understand, you'd want 1/1/2005 to show as overdue on sheet 2, but it now resides only on sheet 1? Seems to me you could copy the cell from sheet 1. Go to sheet 2 and Edit-->Paste special-->Paste link. Then, make the font color white. Then, using conditional formatting to show the font as black or red or something when the value is less than today. Type: =today() into a cell. Then, go to yo...

How to suppress zero value check checks and remittance
Any one knows how to avoid printing the zero value remitances during the checks run in PM. GP7.5 Thanks in Advance. ...

Default Values in Unbounded form
Using Access 2003 and an unbounded form/fields. The form collects parameters for reports and has a Clear button. I want the Clear button to put in default values into text/list fields as when the form is opened. I've tried looping through the fields and using the statement "CTL=CTL.DEFAULTVALUE" (and variations thereof) but the date field default values display with # before and after the date and text/list boxes display quotes around the default values. I tried closing the form and re-opening and that works fine, but isn't what I want to do. Can someone provide a...

Yikes! All chart series lines above the correct Y-axis value markers!
Hello, I'm not sure how I managed to sabotage my chart this way but if anyone has done something similar and was able to fix it, I would appreciate you advice. For example, Series 1 has a value of 4.1 but this point plotted at 4.6 on the y-axis. All points for each series in the chart have done this. Thanks, CJ On 11 Jun 2004 11:49:17 -0700, cgreen07@hotmail.com (CJ) wrote: >Hello, > >I'm not sure how I managed to sabotage my chart this way but if anyone >has done something similar and was able to fix it, I would appreciate >you advice. > >For example, Series ...

Maximum value
Hello, I have a table in the following format: ID Diagnosis Fraction 1 X48 0.01 1 Y52 0.5 2 U78 0.85 etc What I need is only those records that have the highest fraction for each ID. ID Diagnosis Fraction 1 Y52 0.5 2 U78 0.85 etc I have used the Max function in my query. However, that does not work because the information is also grouped by diagnosis as well as ID and this causes all of the lines to appear. Does anyone know how to get around this problem? I can't just bring through Fraction ...

Trying to select a specific range based on the time value of user form input
Here is the cmdOK_Click event of my user form. What I need it to do is based on the time input in the pckApptTime on my form to have it offset to a different range of cells (ie: if time is 7:15, then the active cell offset is range A1, looping for a blank cell. If the time is 8:30 am then the range begins at a10, 9:45 range a20, 1:15 range a30, 2:30 range a40, and 3:45 range a50) Where in the Sub do I put this information? and what do I put to have the OK_Click do this automatically? Private Sub cmdOK_Click() Range("A1").Select ActiveWorkbook.Save (this is to update the ...

sum a cell value to a range
Hi I need to sum value in A4 to a range B4:B12 every time I enter a new value in A4. How can I do it? Thanks in advance Osmario "Osmario.Avila@gmail.com" <osmario.avila@gmail.com> wrote: > I need to sum value in A4 to a range B4:B12 every time I > enter a new value in A4. How can I do it? =SUM(A4,B4:B12) I wrote: > "Osmario.Avila@gmail.com" <osmario.avila@gmail.com> wrote: >> I need to sum value in A4 to a range B4:B12 every time I >> enter a new value in A4. How can I do it? > > =SUM(A4,B4:B12) Or did you mean that you want to a...