Add Red X In Cell Based On IF Function
I have a numerical value of in cell B3. A user will type a value into cell C3. If the user enters a value other than the value in B3, I would like a space and a red X to appear just after the value they enter, in red. (The value is black; the X is red.)
Example: 100 X
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)
I am trying to return a TRUE or FALSE based on a date in a cell. for example: if cell A1 = 07/11/2009 I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date. I have tried stuff like: =IF(A1=TODAY()14,"True","False") but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View Replies!
View Related
VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1. Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr09 column.
View Replies!
View Related
Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place. Is someone aware of a function which will target a relative location instead of an exact cell?
View Replies!
View Related
Force Function To Use Row Relative To Cell Housing Function
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3). My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime. My Script: Function Position(rCell As Range, Optional RightPosition As Boolean) Dim vResult Select Case rCell.Text Case "QB" vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2) Case Else vResult = "Invalid Position" End Select If RightPosition = True Then Position = vResult Else Position = "Position not valid" End If End Function So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas). My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
View Replies!
View Related
How To Use Mid Function Based On The 2nd ( X ) Character
I have random descriptions that have the case dimentions within the text The descriptions are NOT standard length. the only thing I can think of is the dimensions in ALL descriptions have this in common: Length ( x ) Width ( x ) Height I am looking for the Height value How can I use the =mid function based on the 2nd ( x ) character?
View Replies!
View Related
IF Function Based Upon Text
The following is my existing formula (I admit it is probably very far from what it should be) but I'm looking a way to base the conditional part of a function based on the text within a cell. =IF(E4=OR("WordA","WordB"),(I4),(1*I4)) There are four words that I use to classify the column "E" for sake of simplicity I will name them as "WordA" "WordB" "WordC" and "WordD". Those words serve as the identifier that I am trying to test. I.E. if "WordA" or "WordB" are present in column E, I want the end result (listed in column I) to be a positive number. If "WordC" or "WordD" are present in column E, I want the end result listed in column I to be a negative number. Currently I have a formula in column I, which is: =((H3G3)/G3). Both Column H and G have numerical values. Essentially the whole goal of my process is that if WordA or WordB show up in E, then I want my formula in I to remain "asis". If however, WordC or WordD are present, I want the end result or formula in column I to be negative. I am not tied down or restricted to using a particular formula, but just don't know how to set it up either way. Additionally, I plan to copy the formula down in terms of numbers... i.e. I5,I6,I7, etc. so when submitting any advice or if supplying a formula to paste.
View Replies!
View Related
If Function To Return No. Based On 2 Cells?
I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12). What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400. Is there such a formula or do I have to enter manually the amounts into an If function?
View Replies!
View Related
Average Function Based On Criteria
I'm trying to find the best Macro or Formula to get this done. Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".
View Replies!
View Related
Sum If Function Based On Months Chosen
I have 20 sheets in a work book (120) with similar row details(80 rows)...but the columns have amounts for 12 months. I would like to do a sum if function based on months chosen. For eg: If I select "sheet 1" and choose month as june...I want the sum of amounts from JanJune.
View Replies!
View Related
SUMIF Function Based On Row Selected
I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in. background: I have a very large input template for 1218 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 1215 descriptive columns) that makes the freeze pane option unusable.
View Replies!
View Related
Run Macro Based On SUM Function Result
i have the formula "= sum(A1:A10)" in cell A4 & would like to call macro when the value changes. The code i'm using below work's if I manually type in a value, but isn't working with the formula. Private Sub Worksheet_Change(ByVal Target As Range) ' Checks for cell value change If Intersect(Target, Range("A4")) Is Nothing Then Exit Sub ' If cell value change, calls msgbox based on criteria Else If Range("E4").Value = "C" And Range("A4").Value > "30" Then Call MsgBox1 End If If Range("E4").Value = "F" And Range("A4").Value > "38" Then Call MsgBox2 End If End If End Sub
View Replies!
View Related
Function That Will Generate A Price Based On Three Fields
I'm a graphic designer and i'm trying to build a spreadsheet that will help me in doing my accounts. Ok, so i'm trying to set up a function that will generate a price based on three fields: Lamination:YES or NO Duplex: YES or NO Stock: Around 14 different stock options. I had it working using a nested IF but I ran out of the 7. I then tried using CONCATENATE using "&" to seperate but excel said the formula was too long.
View Replies!
View Related
Lookup Function Based On Multiple Cells
in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results? i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array. the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned .... kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left) here is the formula: {=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))} "data to retrieve" is an array of one column containing the data you want to retrieve "cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted) "table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value
View Replies!
View Related
Function To Return Value Based On OptionButton Choice
Ihave 2 functions which are called in my main program which should return a value due to what the user selects on a userform using option buttons and check boxes. Function getnum() As Integer If userform1.OptionButton1.Value = True Then getnum = 1 End If If userform1.OptionButton1.Value = True Then getnum = 3 End If If userform1.OptionButton1.Value = True Then getnum = 5 End If End Function Function getlevel() As Double If userform1.CheckBox1.Value = True Then getlevel = 1.2 End If.......................................
View Replies!
View Related
Countif Function For Counting Values Based On More Then 1 Variable
I'm trying to make a spreadsheet that will count the number of times a certain incident occurs, for a particular person, for a particular month. The attached spreadsheet is an example of what I need done. For the attached spreadsheet, I need to find out how many times x employee has been late for x month, and how many times they've been late overall. You can see one of the many tries I've attempted in the second sheet, but it doesn't seem to want to work. I have to be able to do this without VBA, because of signature issues.
View Replies!
View Related
User Defined Function Based Upon Page Of Calculations
Indicate that a userdefined function can only be based upon the calculations that can be placed in a single cell. If you have too many calculations to put them into a single cell, e.g., an entire page of calculations based upon a few starting parameters that eventually yield a single value, then how do you reuse this entire page of calculations? Is there another Excel mechanism that allows an entire page of calculations to used as a stored procedure?
View Replies!
View Related
Define A Range For SumProduct Function Based On An If Statement?
I want to calculate a weighted average but I need it to only calculate on the months to date. I have a data validation drop down on a title page that is toggled to the current month each time a report is needed to define what months have actuals. I bring in data for all the months but only the current months have actuals. I need to calculate the weighted average on ONLY the ACTUALS. How can I set the ranges for sum product based on the data validation list on the title page?
View Replies!
View Related
Locate Date Function/Formula Based On Criteria
I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference
View Replies!
View Related
Find Matching Document Value And Max Date Using Function Based On Certain Parameter
I have the following info in a table: name  doc date  doc value a  2009/01/01  IN111 b  2009/02/04  IN222 c  2009/02/05  IN333 a  2009/01/05  CN111 d  2009/03/01  IN444 b  2009/03/01  CN222 a  2009/04/01  IN555 Firstly, I need to find the LAST DOCUMENT DATE for "a" where #doc_value starts with IN*** (invoice). Manually, I can see that it would be "2009/04/01", but my spreadsheet runs 6000+ entries. I need a function to do this. Secondly, I need to find the corresponding #doc_value for that date (in this case, IN555).
View Replies!
View Related
IF Function (few Words Shown Based On Figures In Specific Cells)
I want to have a few words shown based on figures in specific cells. I not quite sure how to explain, but i will try my best: A1 = 5 A2 = 0 A3 = 7 The function i am using looks something like this: =IF(A1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?") However, by using this function, if the cell is empty it still shows the first wording when i need it to show lets say between 1 & 6 shows the first wording and more than 6 shows the second wording! If you know what i mean... I know what i want to do but cant really explain it very well. Maybe it should look something like this but it does not work the way i am doing it. =IF(E156>1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
View Replies!
View Related
Formula/Function To Return Multiple Values Based On Criteria
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
View Replies!
View Related
Using Lists Function Calculate The Outstanding Work Estimation Based On Available Information
I am trying to create a worksheet which can calculate the Outstanding Work Estimation based on available information. The information available with me is presented in Rows 3, 4, 5, and 6. For these types of work orders, I have information regarding their Previous Outstanding Status, New Orders, Completed Orders, and at presented Unworked Orders (or Outstanding orders at the end of Jan 2008). Second set of information is available in rows 9 to 23. This information provides for which type of orders were completed by Staff 1 and Staff 2. Based on these two sets of information, I am trying to get to the point where I can calculate the following: a) Applying the weight of orders (namely 1A, 1B, 1C and Blend) by using the drop down list function in column B, I should be able to get the results in Column C (C25 onwards till C40), Column D (D25 onwards till D40), and Column E (E25 onwards till E40). Simlarly for all other months...for February, March, and so on...till December 08.
View Replies!
View Related
Passing Cell References From A Cell's Text To LINEST Function And A Graph
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following: I250 contains: 10, shows: 10 I251 contains: ="I"&I250, shows I10 But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja  Jb and x values in cells Ia  Ib (where a and b are integers specified in cells I250 and J250 respectively):
View Replies!
View Related
Index Function  Data Identified Based On A List Of Account Numbers And Return This Data To A New Sheet
I have a multiple column spreadsheet (Call it "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet. In "Money" I have: IE; columns B, C respectively have cust #A100 & 20.00 columns E, F respectively have cust #B100 & 40.00 columns G,H respectively have cust #C100 & 60.00 Etc. (above for illustrationthere are 100 lines of data in these columns with varied account numbers and respective dollars) So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes. I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month Based On Workdays
I would like to create a monthly inventory, based on workdays (Monday  Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them. =IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
View Replies!
View Related
Workaround For Public Variable Bug: Creates Different Cell Colors Based Upon The Cell Contents
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug. Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View Replies!
View Related
Find Last Cell In Range, And Changing Another Cell Based On Offset Text
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F" and also t = time()
View Replies!
View Related
Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells. For example: Cell 'A1' starts out blank. Cell 'B1' has a drop down to select specific items. Cell 'C1' is the date of the order and is a locked cell. Cell 'D1' is the date of completion and is a locked cell. The worksheet is protected to prevent direct changes to cells C1 and D1. If a user enters any information in A1, then C1 displays the current date. If a user selects 'Complete' from the dropdown list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
View Replies!
View Related
Get Last Cell Function
I am trying to make add in function that will show the last value in a single colum selected range. But it returns data from colum out side the selected range . Function LASTINCOLUMN(rngInput As Range) Dim WorkRange As Range Dim i As Integer, CellCount As Integer Application.Volatile Set WorkRange = rngInput.Columns(1).EntireColumn Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange) CellCount = WorkRange.Count For i = CellCount To 1 Step 1 If Not IsEmpty(WorkRange(i)) Then LASTINCOLUMN = WorkRange(i).Value Exit Function End If Next i End Function
View Replies!
View Related
Macro: Changing Cell Color Based On Date In Cell
I did a search on this site and found some code I was looking for (see link: http://www.excelforum.com/excelprog...eincell.html  Leith Ross's response code). The code works perfectly, however, if I save the workbook with a different spreadsheet on top than the spreadsheet referred to in this code, I get an error message: Method 'Range' of object '_Worksheet' failed. I should state that I did change "Private Sub Workbook_Open()" to "Private Sub Auto_Open()". I assume I need to modify the code but am unsure as to how.
View Replies!
View Related
Change Result Cell Format Based On Input Cell Value
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View Replies!
View Related
Automatic Time Stamp Cell Based On Adjacent Cell
I am looking to add a function to a current spreadsheet that writes the current date to a cell when another is updated. The sheets function is to have a user record when a particular action has been completed and then remind them after a given amount of time. The user is faced with an option to input a "1" to essentially "start the timer" and then the date that is automatically input by the code will be used to compare with the current date. I have tried the following on the Workbook_SheetChange sub If ActiveCell.Column = 9 And ActiveCell.Value <> 1 Then ActiveCell.Offset(1, 2).Value = Date End If But cannot figure out how to eliminate user error. Let me explain. If a user enters a value (will only ever be a 1 to indicate "yes") into column 9 then the date appears in the correct place (two cells to the left) If however a user deletes a value then the date will update one cell too high!? I also thought of using a cell based if statement (if j7 = 1 then today() else "" but this only updates with todays value each time you open where I want the date to stay as the day the cell was updated. The date is going to be used as a way of working out when to change the original value of the cell in column 9 to "".
View Replies!
View Related
Clear Contents Of Cell Based On Comparsion Of Cell Values
I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col if eg h60 = h59 then I60 clearContents, Select h59 Else select H59 Do until H3 is selected Psuedocode Select table (A2:I Xldown) Sort Table by CAcode (H Col) Set Range as H3:Xldown Select Last cell with CAcode (Xldown) in H col For every cell in Range (H3:Xldown) Use If/thenIf Last cell = 2nd Last cell (H Col) then Clear contents of I col (last row) select 2nd Last cell (H Col) Else 2nd Last cell (H Col)Next Cell I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the subtotals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work. Sub sort_And_delete_Sumif_amounts() Dim r As Range 'Select range to sort Range("A2:I98").Select 'Sort CAcode in H col ascendingly Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'select range for comparisomn of CAcode Set r = Range("H3", Selection.End(xlDown))................
View Replies!
View Related
Function To Sum A Column's Entries Based On Another Column
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View Replies!
View Related
