Formula Doesn't Provide Amount As Expected
My sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Recorded Macro Doesn't Seem To Provide Expected Result
On column A, I simply do an A-Z sort that gives the expected result. So I recorded it as a macro. In column G is a code that corresponds with column A. When the marcro is executed Column A is sorted but the corresponding value in G stays where it is. Clicking the A-Z button works. But when that action is recorded as a macro I get the above quirck. Since VBA is not my beef, I don't really know what wrong.
View Replies!
View Related
Cell That Doesn't Reach The Desired Amount
function that add to any cell that doesn't reach the disired amount. If the required number is 14 and a calculated cell comes up with 13 or less; I need a funtion to take that number and add a penalty number to it. For example: For arguements sake the penalty is $150.00. Suppose cell 1A has 5 and cell 1B has 5; cell 1C adds them for a total of 10. I need cell 1D to realize that the number in 1C did not equate to atleast 14 and therfore 1D should show $150.00 and for every cell selected that falls short of 14 should add an additional $150.00 to 1D.
View Replies!
View Related
Some Times If I Do A =if( ) Formula It Doesn't Work
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives? Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
View Replies!
View Related
Highlight A Cell Formula Result If It Doesn't Match
I've been asked to assist with modifications of an excel spreadsheet. One of the features is a column that has a formula of what the total order should be. For example, 1 base order of 25.00, plus 1 bonus order of 18.00, plus 2 bonus orders of 15.00 would be $73.00 due. Then data entry persons enter the actual amount collected. Is there a way to highlight either the cell or the result if the amount collected does not equal the amount due? I know I can accomplish this with another column, but they want to keep the columns to a minimum. This is for a non-profit agency and the orders are now at 1000 that must be entered within a day or two period, so they want to keep the fields to a minimum so as to make the entry easier for those doing the input. And they would like the data entry person to see an immediate flag if what he or she enters as collected does not equal what is due.
View Replies!
View Related
Formula Requires Parameter In Quotes, Indirect Doesn't Work
I've been given a formula to use (embedded in an add-in that is password-protected). One of the parameters REQUIRES quotes. The formula goes like this: =MYFORMULA(1,2,3,"ABC") If I put ABC in a cell (say C5) and use the formula =MYFORMULA(1,2,3,INDIRECT(C5)), the formula doesn't work. If I put "ABC" in cell C5, the indirect function still work make the function work. Even if I have ABC in cell C5 and use =MYFORMULA(1,2,3,""""&C5&"""") or =MYFORMULA(1,2,3,""""&INDIRECT(C5)&""""), these won't work. I wish I could just change the UDF behind this, but that isn't possible.
View Replies!
View Related
VBA Code Doesn't Work When Formula In Sheet Is Removed
I had to remove a lot of pictures in the xls file in order to attach it, (since the original is about 5.3mb) but it worked out. When you move your arrow over the titles in column A a picture shows up thanks to a code. When you change the status in column G, the color changes together with the title in column A, as it should.
View Replies!
View Related
Doesn't Work If The Worksheet Hasn't Been Saved Yet
I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000. 2 reasons I'm doing this: 1) It doesn't work if the worksheet hasn't been saved yet. 2) It's ugly =DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0) The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it) Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.
View Replies!
View Related
Provide Both Formula And Vba Solution.
I have an excel sheet which has been exported from an 'Employee Attendance Software', http://www.flickr.com/photos/34247990@N06/4299288997/ In the above screenshot, The employee name highlighted in Yellow should feauture in column A in the cells highlighted in Yellow. Similarly, there are some other employees in the list for whom I have to populate the data in a similar fashion http://www.flickr.com/photos/34247990@N06/4299288959/
View Replies!
View Related
Formula That Will Provide A Weighted Average Rate
I would like to a formula that will provide a weighted average rate (yellow cells) for a provided volume level, given the tiers on the left. I have already performed this the "dumb" way with lots of IF statements to figure the volume by tier and then taking a weighted average of the products with the rates by tier, but there's got to be a more efficient way. My file is huge! ....
View Replies!
View Related
Formula To Enable A Letter To Provide A Word As Answer
I have the following letters in cells D2= A D3=G D4 =L These represent Accomodation,Groceries, and Liquor. Starting at E12 I want a formula , to drag down the column, which will show either Accomodation, Groceries or Liquor depending on the letter I put in cell D12, Eventually I will possibly need help to summarise these costs on another sheet with other data such as fuel costs and fuel analysis but this will get me started.
View Replies!
View Related
Hyperlink Doesn't Work
I have a hyperlink to [url] that doesn't work. When I click it I get an error saying "Unable to locate the server or proxy server". But when I cut & paste the address from the hyperlink into my browser (no chance of mistyping), it works fine.
View Replies!
View Related
Validation Drop Down Doesn't Appear
I named a range State, it contains a list of all the US state abbreviations. I do a data validation that refers to this range as a List. If I type in something that's not in the named range I get the error message I created for the Validation. But - I don't get a drop down button. I tried to do the same validation on another cell and get the same thing. Other drop downs for validation appear.
View Replies!
View Related
Right Click Doesn't Work
As said, the right click doesn't work anymore, last week it was working but it doesn't anymore. When I right-click wherever in the spreadsheet nothing happens but I know it's working because it works outside excel.
View Replies!
View Related
Listbox Doesn't Redraw
I have a worksheet with a listbox from the controls toolbox. I have to use the controls toolbox activex version because the box is formatted and I don't want a vertical scrollbar. I don't want to use a userform either because that would be major overkill. When the view is set to full screen the listbox partially disappears. Paging up and paging down will redraw the screen and the listbox. I've tried application.screenupdating with different listbox events but nothing works.
View Replies!
View Related
Why Doesn't The Sound Play When The Value Changes Indirectly?
This is a complete mystery to me. I have some code (reproduced below) that plays a .wav file when any of the values in a1:a9 changes from 0 to greater than 0. This code works when I manually change one of the values to a trigger number in any of the cells. The problem is that the values in a1:a9 are really pointers to other cells. For example, a1 is "=sum(D9:D20)" So, when I d9:d20 chnages values such that a1 changes from 0 to 5, the sound doesn't play. To put it simply, if I type "5" in a1 the sound plays. If a1 = D1 and I type "5" in D1 the value in A1 changes to 5 but the sound doesn't play. How do I modify this code such that any change in the value of A1:A9, no matter the source of that data change, triggers the playing of the sound? ......
View Replies!
View Related
NAME Doesn't Stay Attached To The Cell
I have a cell (H27) which has a (sum) total in and have attached a NAME FRED. This is fine as long as the client doesn't add a row above it. If they do when I re-open the sheet the NAME doesn't stay attached to the cell (now H28) Is there a way to keep the NAME attached to the cell regardless where it is? Private Sub Workbook_SheetActivate(ByVal Sh As Object) Range("$H$27").Name = "Fred" End Sub
View Replies!
View Related
If Statement Doesn't Recognize Non-zero Values
I have the following formula with multiple if statements: IF(N2>0,N2,IF(O2>0,O2,IF(P2>0,P2,IF(Q2>0,Q2,IF(R2>0,R2,IF(S2>0,S2,IF(T2>0,T2))))))) This formula refers to cells in columns N,O,P,Q,R,S and T. Every cell in columns N,O,P,Q,R,S and T also contains formulas (VLOOKUPs). My if statement works fine but when it checks cells in column P, for some reason, it doesn’t recognize cells with non-zero values. I can’t figure out why this is happening and how to fix this problem.
View Replies!
View Related
Isolate Data That Doesn't Match
I have a long lists of values (product numbers) from our inventory records. Now, we've been given a new list of product numbers, some are the same and some are not. I want to isolate (spit out) the values from column A, than don't match column B. Column B is the list we SHOULD be using, column A is what we're currently using. See example below. Old Products New Products 34544 34544 34545 34546 34546 34547 34547 34548 34548 34550 34549 34551
View Replies!
View Related
Full Screen Doesn't Work.
This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.
View Replies!
View Related
VBA Doesn't Recognise Selection
I have a report that has been running in it's current state for several months with no issue. However, today it has decided that it doesn't understand the word 'selection'. eg: selection.copy gives compile error. Now... obviously NOT USING selection is preferred. However, let's put that aside for now because there are LOTS of these in this report and it's never had an issue until today. Is there some setting that could have gotten changed or something? It's happening on multiple computers and we're running Excel 2003 (standard) with SP3.
View Replies!
View Related
Counting Even If Doesn't Meet Criteria
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26. Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field. Here is the formula/function I'm using: =(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")
View Replies!
View Related
Private Sub Workbook_Open() Doesn't Execute
why the code below might not execute ? It just seems like it skips over the code and doesn't run at all. Private Sub Workbook_Open() 'AssignRequestNumber() MsgBox ("Whoa") Run "AssignTrackNumber" End Sub I have a macro assigning a unique number to the file in a fixed cell whenever the .xlt file is opened from File-New.
View Replies!
View Related
Code Doesn't Loop Properly **
I have some code (probably a little inefficient, but still) that should delete any rows that contain nothing in column V. My problem is that it only deletes 1 row at a time: Dim c As Long Dim Limit As Long Limit = Cells(Rows.Count, 11).End(xlUp).Row For c = 2 To Limit If Cells(c, 22).Value = "" Then Cells(c, 22).EntireRow.Delete xlUp End If Next c
View Replies!
View Related
Programme Doesn't Work When Sent To A Different Computer
I have a programme which works fine on my computer at home but when I send it via email to work the 'sort' function messes up (there is a chance it could be the 'vlookup' function, though) and all the cells in the sort range lose their formula reference - which is of course a tad annoying. The part of the programme I'm having difficulty with is this: Range("SsNo1").Resize(d, f).Offset(a, 0).Select Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
View Replies!
View Related
Array Doesn't Seem To Write Out Results
I have 4000 rows of data with numbers in it (only one column...column C). These numbers are consecutive but these have breaks in between. So I am trying to find out where those breaks are and output those points in a list. So here is a macro that I came up with...but these array doesn't seem to write out results.... can anybody help please? This macro tries to compare the two adjacent cells and if the difference between the values is greater than 1 then it copies that value in an array and writes that value in a cell in the same sheet. Sub Macro1() Dim temp(1, 1000) As Integer i = 0 previouscell = 0 For Each cell In Range("C:C") currentcell = cell.Value abc = previouscell + 1 If currentcell <> abc Then temp(1, i) = currentcell i = i + 1 Range("D" & i).Value = temp(1, i) End If previouscell = cell.Value Next cell End Sub
View Replies!
View Related
Why Doesn't My Variant Array Work
The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time, The code I have is: Sub test() Dim vSheetColours As Variant Dim iCounter As Integer vSheetColours = Range("Colours").Interior.ColorIndex For iCounter = 1 To UBound(vSheetColours, 1) MsgBox vSheetColours(iCounter, 1) Next iCounter End Sub (Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!) Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours. If I run this code, I get a runtime error 13 type mismatch, and it highlights For iCounter = 1 To UBound(vSheetColours, 1) But if I replace vSheetColours = Range("Colours").Interior.ColorIndex with vSheetColours = Range("Colours").Value it works fine.
View Replies!
View Related
Delete Row If Doesn't Contain Certain Numbers
Working at a company where we every month extract our customers into a excel document. This is the CRM system , and some of the clients can't (shouldn't) be approached. In Cell C is their customer classifications. In total there is about 60 different classifications. 10 of them is clients who we can send information to. For example customers with class 11111 or 41414 or 51515 or 61616 (among with 45 others) can't be approached. I'd like a macro/script who can delete all the nonrelevant clients so that afterwards I have a list of clients who can be approached.
View Replies!
View Related
Right-click Menu Doesn't Exist
I have few questions, 1. why the "auto sum" icon grey out, and doesn't work. 2. when I right click the sheet, the menu doesn't show up. 3. Insert columns/rows, When I highlight 2 columns/rows, right-click the mouse & the menu doesn't show up for me to choose insert col/row.
View Replies!
View Related
Workbook_Open Doesn't Run On Second Open
I have noticed this on more than one workbook with an Workbook_Open macro. When you open Excel, open Workbook1, do some work , save or don't save and close, then reOpen Workbook1 without having closed the Excel application, the Workbook_Open macro in Workbook1 doesn't run. Closing Excel and reopening Workbook1 initiates the Workbook_Open macro. It's as if Excel remembers having previously opened Workbook1 and so it doesn't rerun the Workbook_Open macro the next time you open it.
View Replies!
View Related
Scroll Down/Across Worksheet Doesn't Move
I have two basic questions regarding the Excel program/UI I'm currently using Excel 2000, One annoying thing I find with this version is the way the screen doesn't follow along with my scrolling. ie: when I use the vertical scroll bar on the main window, it only updates once I release... not like a typical window which follows the movements of the scroll curser. Is this typical for later versions? I would update this program just for this feature alone! Also, a final question.... Does anyone know if there are any plans to update the handheld (pocket pc) version of Excel? The factory installed program is incredibly limited.
View Replies!
View Related
Create Worksheet If It Doesn't Exist
I need to create a worksheet and then populate it with header row containing columns names, and with values starting at row2. Before I do all this I wanted to first create a spreadsheet, I was successful in getting this done by getting a piece of code from this website. But, what if spreadsheet already exists from a previous run, then, in that case I want to clear the contents. Before I get too far ahead, I am unable to escape runtime error 9, array subscript out of range whenever I use any sort of code to check if the sheet exists. Also, my attempts to circumvent this error by putting in errorhandling is ignored, i.e. I get the standard run-time error message box, but, not what I want the code to do is such an error occurs. Function wsExists(wksName As String) As Boolean On Error Resume Next wsExists = CBool(Len(Worksheets(wksName).Name) > 0)End Function Private Sub Cmbsummary_Click() On Error Goto ErrHandler: 'Worksheets("MySheet").Activate Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MySheet" Exit Sub
View Replies!
View Related
Pivot Table Field List Doesn't Appear
The field list does not appear when I create a pivot table in Excel 2007. It works properly if I start Excel in safe mode. I have toggled the field list button in the PivotTable Tools show/hide ribbon and I tried repairing Office 2007 from the control panel.
View Replies!
View Related
Find Method Doesn't Work With Some Text
I have a spreadsheet with data similar to the following: 12111000 MILK, COW'S, FLUID, LOW PERCENT 27313010 BEEF, NOODLES & VEG (W/ CARROTS/DK GREEN), NO SAUCE 11100000 MILK, NFS 11111000 MILK, COW'S, FLUID, WHOLE If I use the following code to pass in a string variable and then attempt to find the string it never sees the text in line 2; or any line that contains (). The code is simply a test to try and figure out what i'm doing wrong. The actual goal is to search the entire sheet for text similar to that entered in by the user and then copy every row that contains similar text into another worksheet. Sub CopyStuff() strVariable = "car" 'I have tried "*car*" also With Worksheets(1). Range("b1:b500") Set c = .Find(strVariable, LookIn:=xlValues) If Not c Is Nothing Then firstaddress = c.Address Do Set c = .FindNext(c) MsgBox (test) Loop While Not c Is Nothing And c.Address <> firstaddress test = c.Address End If End With End Sub
View Replies!
View Related
VBA - Ignore Sheet If It Doesn't Exist
I have several workbooks, and each has a different number of sheets (i.e. Pool1, Pool2, Pool3, etc...). One workbook may have the sheets named Pool1, Pool2, and Pool 4, but no Pool 3. Can I write code to ignore what is supposed to happen to the sheet "Pool 3" if the sheet doesn't exist.
View Replies!
View Related
SUMIF Doesn't Work From Other Workbook When Closed
I have a sumif formula in one workbook that relates to information off of another workbook...when the other workbook is open the information populates no problem when you close the other workbook I get the #VALUE error. Is this typical of SUMIF when referring to another workbook? I have a vlookup function that refers to the same workbook and it works fine when the other workbook is closed?
View Replies!
View Related
|
TRACKER
