I'm using VBA code in Excel 2007. I am having a strange problem. The cells have content. Some of them, but not all of them, are selected from Data Validation lists (drawn from a named range). However, when I try to access their values in VBA, it says they are empty!'' For example, the simple code: Debug.Print "Cell value is: " & ActiveSheet.Cells(2, 7).Value results in the output: Cell value is: I even tried the following code, to make sure it was not a problem with the ActiveSheet reference: ThisWorkbook.Sheets("Form").Cells(2, 7)....

I am trying to use sort function just to delete blank cells in between Sort order doesn't matter actually. Data is coming by the use of simple cell reference of "another sheet -- Prais ----------------------------------------------------------------------- Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27144 Hi you may use the following addin to filter out blank cells: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany Praise wrote: &g...

Hi All, I'm just starting to learn Crystal right now but it's all very new to me so I was just curious if anyone knows if you can do formulas in it. I'd like to take a custom CRM field called setup cost on a product and add all the values of this field up in a quote. Ie if there are 3 products all with seperate setup costs I want to add those together. Or will this have to be done a different way? Thanks all Tom Hi, Crystal supports both VB and Crystal Reports formulas. the help files are quite good regarding formulas, and the 9.2.2 CR with enhanced edition has an excel...

Is there a way to set a formula to calculate how many rows above (COUNT) with no text or numbers in the column? try =COUNTBLANK(F3:F9) -- Don Guillett SalesAid Software donaldb@281.com "deniseS" <dstafiej@dykema.com> wrote in message news:150b101c3c3fa$68119950$a601280a@phx.gbl... > Is there a way to set a formula to calculate how many rows > above (COUNT) with no text or numbers in the column? > =COUNTIF(A1:A100,"") -- HTH. Best wishes Harald Followup to newsgroup only please. "deniseS" <dstafiej@dykema.com> wrote in message news:15...

i have values in a series like c5 9:01 d5 21:01 e5 =(d5-c5) I would like to use e5 in a bar graph it gives values which would make sense only to excel If the axis is formatted as time, it shouldn't be a problem. If it doesn't choose the scales to give clean divisions, you can choose an appropriate unit on the axis, such as 03:00. -- David Biddulph "pdfrone" <pdfrone@discussions.microsoft.com> wrote in message news:A8C14E9F-104A-464A-81B6-39DA2C679E71@microsoft.com... >i have values in a series > > like > > c5 9:01 > d5 21:01 > e5 =(d5-c5...

Hi Reddance, Try instead: Sub Tester() Dim rng As Range Dim i As Long If LCase(Sheets("Ctrl").Range("A1").Value) _ <> "yes" Then Exit Sub End If With Sheets("ToPrint") For i = 5 To .UsedRange.Rows.Count Step 2 If Not rng Is Nothing Then Set rng = Union(rng, .Cells(i, "A")) Else Set rng = .Cells(i, "A") End If Next i End With If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If ...

I have a form that is populated from a query using several tables. In the detail section I have the following fields: Category Code qty PO number ... .... ... The qty is populated in the query based on whether or not a PO number is available. If the PO number is not available then it comes from table a, if it is available then it comes from table b. On the form, I have my fields color-coded for easy reading. I would like to be able to color the border and column heading based on where this value comes from. If table A, then it is blue, if table b then it...

Hello all. I have the following formula in a cell that works just fine. However, I need to add one more SUMIF statement but I get an error stating that the formula is too long. Is there an easy way around this - thank you. NWO =SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E11",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$10000<>"110",IF(GoodBoth!$S$2:$S$10000<>"115",1,0))))))+SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E12",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBo...

I've got a sheet that gets appended to regularly but have to do a lot of fiddling to make sure the formatting is correct, calculated fields get added and chart series ranges collect all the data every time I add a new row. The insert (row) command seems to take care of all of this but it inserts the empty row above the current row. As I'm always appending data to the sheet, I would prefer this to be below the current row. I've tried adding a dummy row under all the data containing the formulas and formatting I need but unfortunately I get formula errors and it screws up my chart....

I have a task list that in one column lists the person primarily responsible for it and in the 2nd column is the back up person. The 3rd column is the task. On another sheet I want to put in someones name and bring up all tasks they are responsible for and all the tasks they are backup for. What is an easy way to do this. Easiest way IMHO would be to use advanced filter http://www.contextures.com/xladvfilter01.html -- Regards, Peo Sjoblom (No private emails please) "Greg" <Greg@discussions.microsoft.com> wrote in message news:FA4CB596-6451-4569-81AF-24EC18C50180...

I am trying to use Conditional Formatting to find the Max value in a Row using =Max(B6:AC6), having that cell Highlight to a different color but the =Max is highlighting them all. Help phuser wrote: >I am trying to use Conditional Formatting to find the Max value in a Row >using =Max(B6:AC6), having that cell Highlight to a different color but the >=Max is highlighting them all. > There may be other ways, but what I do is create a column, or in your case cell, (can be hidden, or way out of sight) having the formula =Max(B6:AC6). Then use the value of that cell as the co...

I am trying ot put in a formula. I have a two columns with Revenue listed. One is the Estimate, one is the actual. If there is not an amount in the Actual amount column, I added the cell that was in the Estimate cell. I color coded the cell because I am breakingit out by Sales Person. I have 3 Sales People. I went in and did the SUM and went into each cell that is colored coded to each person. I am getting an error. Please help. Thanks, Vanessa Not enough information to more than guess at what you might be doing wrong. How are you going into SUM? Are you clicking on the sum icon? ...

I am trying to complete the following. In s141 i have a drop list with 'yes' and 'no' as possiblities. In x141 i have a drop list with 3 possible choices, .02 , .04 , or .08 g141 is the originating cell p159 is the answer cell. if s141 remains empty or has 'no' chosen from the drop list i need p159 to remain blank, however if 'yes' is in s141 then i need the answer from g141 multiplied by .02 or .04 or .08 (which ever one is chosen from the drop list in x141) to be displayed in p159. Can anybody help? Thanks in advance. Scoob...

Is is possible to use XPath with multiple values like in SQL Queries (AND Clause).For example: If I have xml: <rows> <row code=1/> <row code=2/> <row code=3/> <row code=5/> <row code=6/> <row code=7/> <row code=100/> </rows> Now I want only to select rows 1,2,3 an 100 XPath("rows/row[@code='1' and @code='2' and @code='3' or @code='100']"). I tryed like this above, but I got nothing. Any examples. Regards. Try using ORs all the way. An AND situation is impossible here...

I am looking for VB code for command button that fills two combo box values and a toggle value (Y/N) behind each other in one cell. Bart Excel 2003 ...

Hi. I am trying to assign a set of values, in separate fields ,to a each selection from a drop-down list in Excel (using data validation). What I am trying to do is very smilar to, say, to selecting a SKU from a list and having the product description, unit price etc. fill into their corresponding fields automatically. For example, I pick SKU "11111" from a drop-down list to fill the SKU field, then, automatically, "5-inch widget" comes up in the product description field and "$5.00" shows up in the unit price field. Please help. Mike Along with the d...

Hi Using Excel 2003. In sheet 1, cell A1: =200/1200 In sheet 2, cell B2: =300/1200 In sheet 3 in a cell, I want to do =200/1200 + 300/1200 by cell reference. How do I do so? Thanks. Your formula would be: =sheet1!a1+sheet2!b2 The best way to create these formulae is to get Excel to do it. 1: Type = into your cell 2: Navigate to your sheet1!a1 cell. You will notice in the formula bar that Excel is adding its address to your formula 3: Type + 4: Navigate to your sheet2!b2 cell. 5: Hit enter Once you learn this, you will never type a cell address again. Regar...

I have some code that cleans up an xml file before running xsl transformation on it due to what I call bad characters in the data. I have as an example: str = Replace(str, "", ".") str = Replace(str, "", ".") In there as filters to strip out characters XSL doesn't like and replace it with periods. I am now getting an exception of a hex value of 0x0E however can't figure out what the string replace value should be for this one. From what I can tell, it's some kind of "shift out" character whatever that is. A...

I have a pretty simple worksheet that lists the date in column A. In column B, I have a formula that converts the date in Column A to the proper day of week. When I put it in a pivot table, it treats column B as an individual date, not the proper DOW. I attempted to copy/paste special/values into a new column, but it doesn't change it to the DOW, it leaves it as the date. I am trying to get Excel to permanently change the cells in column B to say "Tuesday" instead of 4/19/07. Does that make sense? All of the conventional answers don't seem to work for me. Any help? It...

If I have an array (B2:B170), what formula do I need to get the final figure in the list reading from top to bottom? It could be cell 125, or cell 37, it will vary. On Nov 17, 4:41=A0pm, Esradekan <esrade...@gmail.com> wrote: > If I have an array (B2:B170), what formula do I need to get the final > figure in the list reading from top to bottom? =A0It could be cell 125, > or cell 37, it will vary. Forgot to say TIA. Sorry, stressed. Esra Hi, Try this =LOOKUP(TRUE,ISNUMBER(1/B2:B170),B2:B170) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashish...

Hey peeps! I have a formula that turns the cell red if the number of received items is less than the number ordered, and only if the scheduled date they were to come in is past today's date. I would like to add in the formula the ability to recognize in the ordered and recieved column if I needed to order more at a later date, or if I received the total items at different times...like 11+3 in the ordered column, meaning I ordered 11, realized I needed more and ordered 3 more. Can I make a formula to recognize this additional requirement? Here is the original formula I have in the cell...

Hi All, I have a range of cells and some of them contain this formula: =IF($C$1="Glamour","15.688",IF($C$1="Econo","15.563","Select Style")). The problem I have is when I query this range on another work sheet, it leaves the results of any cells with this formula blank. It is important to me to have this formula in the cell, do you have any suggestions. Sincerely, Nathan Sargeant -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php?action=getinfo&a...

Hi Everyone! I apologize that this was likely asked before but could someone show me how I can copy my 'column headings, column widths and totals formula' to a blank spreadsheet tab? I have set up budget spreadsheet where I input my receipts to keep track of monthly expenditures. Could someone explain how to copy the column headings, the column widths, AND the formulas of each column to a blank spreadsheet tab so each month will contain the 'same headings, same column widths and the same formula for each column'? Thanks so much for any help!!! katy Assuming you alr...

Once again working on the fundraiser spreadsheet... Teacher wants ... of total money raised, 40% is profit...of that profit the first $15 goes to the general account, the difference goes in the student account. The only thing I really need to keep track of is the student account, which I figured would be (cell*.4)-15, but the problem I have is ... If the child raised zero dollars, it's showing a -$15 dollar balance, but I would need it to show zero. Any help is appreciated. Vivian K =MAX(yourexpression,0) -- Gary''s Student "Viviank" wrote: > Once again wo...

Im new at all this and Im not sure if Im explaining this correctly, bu im trying to make a bowling league summary sheet which can tell th players what thier high game is after each week. I tryed to us th what if formula, but it just gives counts. I want a number total. ho do i get started with this. the games for each player has 33 columns. Is this a what IF formula? this was the formula i was using and all i does is count not give me the "number" in which was bowled. =COUNTIF(G10:G42,">175" -- Message posted from http://www.ExcelForum.com Hi have a look at SUMIF...