I have written a macro that runs an autofilter which grabs cell info from a different worksheet within that workbook (an entered date). It then uses that date to autofilter. It works fine the first time, but aparently plugs that info permanently into the macro and will not work on subsequent runs. I need a macro that will grab variable info from a certain cell each time and run with that new cell value each time. Any ideas?. (& yes, I am a newbee) sorry.. Your problem is refreshing the autofilter. It is best to clear the old filter before applying the new filter. Here ...

I can't figure out how to increase the "text box" around the Y axis label. In the display, it shows the entire word, but in the actual chart, the last letter of the word is cut off. It appears that I can only move the position of the entire label, but I'm not able to stretch the label so that it includes the entire text. I tried putting a couple spaces after the text in the Title field in the Chart Options screen, but this didn't help. Example: The Y axis label is "Annual Return" and it looks like it says "Annual Returr" due to the "n" b...

I created several customised fields and bulk imported account data - all looks fine except for one numerical field, which does displays only blanks. When you double-click on the account, however, you clearly see there is a non-zero value there. When I sort by this field, it does the right thing, just that somehow the field value does not show up under the field name at all when viewing all accounts. Any help would be great. Textbooks etc don't seem to cover this sort of newbie snag! Thanks ...

Hi, Any help you can provide on this would be appreciated. I have a worksheet that identifies how far a number is from target. I created the lookup below to coorespond to the productivity increase for next year. =LOOKUP(C4,LookUpValues!$A$1:$A$402,LookUpValues!$B$1:$B$402) C4 is -10.9, in the lookupValues tab -10.9 is in row 93 column A as is the desired result return of 5.6 in column b, however the lookup function is returning 5.7. The issue may be that the data and the lookupValues tab numbers are not exactly the same. -10.90001 and -10.9004 may display the same, but w...

Within a single worksheet I have thousand-plus rows of data and one of the columns within that worksheet I have a series of numbers. After I've identified the duplicates within that column I want to assign a value based on the first time that number shows up (in this case by I'v sorted by date), so the first duplicate would have a value like "1." I then want to assign the next duplicate(s) sequentially (2, 3, 4, etc.) When finished I would want a new column of data that would have far more "1s" than "2s," more "2s" than "3s,"...

Hi, I have two series of data as follows: Distance Height 0 0 6 12.5 11 23 12 16.25 14 26.75 24 27.25 42 22.75 45 30 55 52 70 67 90 79 115 83.5 I need to find the heights corresponding to : 5. 10 15 20 25 30 and so on till 115 (ie at distances in multiples of 5) What is the best way to do this? I am new to statistical functions in excel. Thanks in advance for the help. Regards, Raj On Tue, 6 Apr 2010 03:22:50 -0700 (PDT), Raj <rspai9@gmail.com> wrote: >Hi, > >I have two series of data as follows: > >Distance Height >0 0...

XL97 & 2k For many years I've regularly imported downloaded (mainframe) files into XL and, where there were values with trailing minus signs, I used the Import Wizard to define the position as a separate column. I wrote a utility macro that flips the value. Recently someone mentioned that they accomplish this with the import wizard and I looked until I was blue in the face but cannot find it so I assume that-if this individual was correct-it is in a version newer than the one(s) I use (I didn't hear what version he was using). Is this correct, or am I indeed blind? -- Regards; R...

Hi I'm coding a callout for the CRM 3.0, I would like to be able to catch when the opportunity's Probability value has change due to a stage change in a Sales Process workflow. I have the following code: public override void PostUpdate( CalloutUserContext userContext, CalloutEntityContext entityContext, string preImageEntityXml, string postImageEntityXml) { string preProbability = String.Empty; string postProbability = String.Empty; // Get pre-values XmlDocument preImageEntity = new XmlDocument(); preImageEntity.LoadXml(preImageEntityXml); foreach (XmlElement elemen...

Hi all I'll preface this by pointing out that I do NOT want to use text to columns, nor a VBA solution. I'm looking for a formula alternative - maybe an array formula. I have a single column of data extracted from a string, which represents numeric values, single space-separated. The data looks like this: 8.56 2,514.12 3.18 0.35 What I'd ideally like is a formula that sums each of the four values, which in the above example would be 2,526.21. Any suggestions appreciated. >8.56 2,514.12 3.18 0.35 Assume that string is in cell A1. Create this named...

Hi, I'm using the following expression to return the mode of a list of numbers: =IF(H1028="","",MODE(H1028:H1031)) If the four checked cells all have values but there's no two values the same (i.e. no mode) what I need is an expression that will return me a default value of 2 rather than the #N/A error value. Thanks for looking, Steve. =IF(ISERROR(MODE(A1028:A31)),2,MODE(A1028:A31)) Regards, Stefi „Struggling in Sheffield” ezt írta: > Hi, > I'm using the following expression to return the mode of a list of numbers: > > =IF(H1028="...

I have a table with a field that is set to Number, Double, format percent, 3 decimals THere is a form through which the value is entered into the table. Its format is set to Percent and 3 decimals When the value is entered into that field, it displays 3 decimals of a percent - but the actual value is rounded to the second decimal point. How can I get to display the Percents WITH 3 decimals without rounding? Thanks, When I go to the Format tab of the properties sheet for the control that displays the value and set the Format to Percent and the Decimal Places to 3, I get a percent with three d...

How do I search for the second (or third and so on) largest value in the array either in column or row? =LARGE(A:A,2) =LARGE(A:A,3) =LARGE(1:1,2) =LARGE(1:1,3) -- David Biddulph "Golf" <Golf@discussions.microsoft.com> wrote in message news:241155EE-30CA-4E7C-8E8C-2215F1523919@microsoft.com... > How do I search for the second (or third and so on) largest value in the > array either in column or row? Assume that you would like to get the result from A column Data. Column:- This will get the Largest number from A Column =LARGE(A:A,1) Similar ...

I have a chart with data in Columns C and E. I want the columns to zero out when subtracted (ex:=E2-C2). My issue is I only want zeros in the answer column G to display in the rows with data, not in the rest of the column where I put the formula. Is that possible? Any ideas would be helpful. I can't hide zero values becuase then nothing in the column would display at all. I tried an If Statement but I get a circular reasoning error. HELP! Submitted via EggHeadCafe - Software Developer Portal of Choice A C# WaveOut API Player - Recorder Library http://www.eggheadcafe.com/tutorials/...

Hi, I have created a report (column) that list open invoices and I have also attached a sub-report (different table) that displayes comments. Problem: sub-report is showing for all invoices, but I would like it to show ONLY for invoices that has a comment. That is, invoices that has no comments only list their usual values, and invoices with comments I have a sub-report displaying the comment. Is that possible? (otherwise my invoice-list with contain a lot of unecessary space - used by the sub-report). Kindly, Mikael Mikael Lindqvist wrote: >I have created a report (column) that l...

I would like to display in cell A2 a red diamond if the value in U2="Storage"; a Green Circle if the value in U2="Central Files" "Office" or Floor"; and a yellow triangle if the value in U2="Missing". I saw the response to "ICON Arrows UP or DOWN", but I couldn't understand where the different parts were supposed to go. Nolene, you can put an IF statement in column A that reflects the information in column U: =IF(U2="Storage", 1,IF(or(U2="Central File",U2="Office",U2="Floor"),2,...

I would like to chart two sets of x and y values with each set of x values corresponding to its own set of dependant y values. The two sets of x values for the ordinate are entirely different. It seems like I can only chart two sets of data on the same graph with the same values for x and two separate sets of dependant values for y. Is there any way possible to get around this problem? Select your first set of X&Y data, Insert/ Chart (Chart type XY, & choose an appropriate sub-type)/ other options as appropriate. Now select your second set of X&Y data, Copy, select your c...

Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

How do change values of TRUE or FALSE to 0? what is the formula you are using? "Redleg40" wrote: > How do change values of TRUE or FALSE to 0? =IF(AD7<0,AD7*-1) & =IF(AD13>=0,AD13) "Jambruins" wrote: > what is the formula you are using? > > > "Redleg40" wrote: > > > How do change values of TRUE or FALSE to 0? Put a 0 in for the value_if_false argument. Standard IF syntax =IF(condition,value_if_true,value_if_false), so for your first formula =IF(AD7<0,AD7*-1,0 -- MrShort ----------------------------------------...

Hello. I have a form that I would like to lookup values in several taxt boxes. The values I want displayed are from several different Sum Queries. Example: I want to pull Total Working Blance from a query and monthly rent revenue from another query. The values all come from different tables. Is this possible? Thanks for the help. you can use a DLookup() function to retrieve the queries' values. read up on the function in Access Help, so you'll understand how it works. then add an expression to each textbox control's ControlSource property, as =DLookup("MyField", ...

I have a worksheet created in EXCEL 2003 which i use to record swimmers times in several lists. One examlpe is in cells D109 to 113. In cells D111 to 113 are recent times and D109 to 110 are empty. As I add current times to the top of the list. I want to be able to retrive the current value at the top of the list and show it in D106. D107 & 108 are used for other values. At present I have the following formula in cell D106 =OFFSET(D108,COUNTBLANK(D109:D113)+COUNTA(D109,D113),0). This works well but makes updating the list difficult. Is there a simpler way to achive the same...

I want to run a macro when a value appears in a cell. How can this be accomplished? Hi! This is a trivial example but you can build on it: Private Sub Worksheet_Change(ByVal Target As Range) If Range("D3") = 36 Then Range("D3").Interior.ColorIndex = 6 Else Range("D3").Interior.ColorIndex = 2 End If End Sub Put =A3*3 in D3 Try values such as 12, 10 in A3 Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforu...

I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

Under Tools/Options/... I set up the defaul drive in G (net work drive). Today, the network drive is down and I am working on C drive. I'd like the worksheet doesn't show zero values when viewing it, so I unchecked the box "zero values". But it keeps giving me the message "Cannot access directory G:\excel\...", and the zero values are still there. How can I by pass this message and the zero values would not show? Thanks. Mia ...

I have seen similiar examples but not that were not using exact matches to retrieve data. This seems simple and maybe I am over thinking but I have been unable to crack this nut. I have a table where the first tier sales runs between 0 and 20k and awards 1K commission. I want to be able to enter the salse result for various team members and obtain the correct commission amount based on their sales for the period. How should I complete the commission formula for this process? Sales Value Start Sales Value Stop Commission $0 $20,000 $1,000.00 $...

So, in one worksheet I have some data and there is some colums hided. When I selecting all that data and pasting to other worksheet, al these hided colums or rows values appears and I have to hide it again. Is there possibility to copy/paste in such way, that these hided value don't appear again? Thanks in advance Best Regards, Aivara -- Aivaras_Bakana ----------------------------------------------------------------------- Aivaras_Bakanas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2518 View this thread: http://www.excelforum.com/showthread.php?threa...