Place A Number In A Cell Based On Cell With A Value
I need a macro that will place the number "1" in a range under the cell with a value of any kind. If the cell does not have a value then the cells below will be left blank.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Display Cell X Of Range Based On Another Cell Number
I am trying to create a table, based on what people predict in sports games. I have most of it in place, but I have hit a brick wall where one part of it is concerned. For example, cells M43:M56 have the scores for each week of what one person predicted., so it could be 2,5,7,2 and so on. What I then need the table to do is determine which round or week it is and display the relevant score. So based on the above let's say it's round 3, and his score would be 7 for the week, and the cell to the right of this shows the week's score plus his total score so far.
View Replies!
View Related
Cell Colour Based On Number In Cell
I have a cell (A2) which has a large formula in it but eventually returns a number between 1 - 5. I want to add colour to the cells based on the number. So, in column B2 i want to have a formula that says =If (A2 = 1, blue, If(A2 = 2, Red, etc..)
View Replies!
View Related
Format Cell Number Based On Value
I need to evaluate some offers and for the management I need to follow some strict rules like: 1. € 1.000.000 instead of €1000000. But that's not the problem 2. to use maximum 3 digits... Example: 10,2 or 6.12 I tried with an if function and then round, but I think that's not right way. If number < 10, then round with 2 decimal places else, round with 1 decimal place
View Replies!
View Related
How To See Where The 55th Place In A Cell Is
I use Excel 2002 - I have a column with Text in its cells. The cells usually range from 30 to 130 characters including spaces. I need to use word wrap. I would like to be able to see where the 55th character is ( Including spaces ) - but I also need to see the rest of the contents of the text in the cell. I have thought about coloring the first 55 character/spaces ? or inserting a character at space 56 ? Example below XXXX XXX XXX XX XXXXX XXX XX XXXX XXXX XXXX XXX XXX XXX XX~XXX X XXXX XX
View Replies!
View Related
Change Number Format Based On Another Cell?
I have used this forum periodically and it has saved my bacon on numerous occasions so I know someone can help with this! I created a workbook for staff planning and it's becoming a behemoth! I'm wanting a range of cells within a row to change the number format to "[hh]:mm" based on a value in the 2nd column of that row. I have played about with a few things and can't seem to get it to work. What I have so far is this:
View Replies!
View Related
Change Cell Color Based On Number
Can I change the color of a cell based on my deviation from the number based above it? I am making a golf spreadsheet to base my performance off of, and I was wondering if I could make the cell change color based on my performance on the hole (different color for how many strokes above or under par). If anyone has any idea on this issue, please let me know.
View Replies!
View Related
Cell Number Format Based On Another Cells Value
If Cell A1 in my spreadsheet is 1, then cell A2 should appear formatted as a percentage. So if a user enters 9000 into cell A2, it should appear as 9000%. If Cell A1 in my spreadsheet is 2, then cell A2 should appear as a comma-formatted whole number. So if a user enters 9000 into cell A2, it should appear as 9,000. Conditional formatting doesn't appear to let me do this.
View Replies!
View Related
Finding Row Number Of A Cell Based On A Particular Value
I need to find the row number of a cell based on a particular value. I am populating a row within a spreadsheet with a value (the columns have unique identifiers). After that is done, I need to go to a different spreadsheet, grab different values, and place them in a different column in the above referenced spreadsheet. So, what I want to do is find the row number for the unique identifier, then place the value in the column.
View Replies!
View Related
Return Number Based On Cell Content
I have a questioner in a excel spreadsheet. Column C have yes or no answers. If answer yes is implied then I want the number 2 to appear in Column F of no is implied then the number 3 will appear. I am having trouble writing the language for this.
View Replies!
View Related
Return Number Based On Other Cell Values
I have the following formula: =IF(C319<=300,9,IF(AND(C319>300,C319<500,6.75),IF(C319>500,6))) It returns 9 if C319<=300 but returns FALSE for all other numbers. I want the funtion to return 9 If <=300 and 6.75 if C319 is greater than 300 but <=500 and if it greater than 500 it should return 6.
View Replies!
View Related
VB Code To Place Formula In Cell
Upon user making selection from data validation list in Active Row Selection & Column A : i would like to do the following (is possible) ,,,,,,,,,,,,,,, Place formula in Active Cell Row & Column S =MT4|BID! (followed by cell A1 but replace the "/" with "") followed by "m" I have tried several combinations but with no success. Also - where would i place this code so that it automatically updates the formula in Column S as soon as the user changes selection in Cell A?
View Replies!
View Related
Place Date In Cell H6 Automatically
I have the following Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A2:A100")) Is Nothing Then With Target(1, 2) .Value = Date .EntireColumn.AutoFit End With End If End Sub I just want the date to appear in cell H6 autmatically and the time to appear in H7.
View Replies!
View Related
Conditional Number Format Based On The Value Of A Specific Cell
I have a large spreadsheet which includes many rows whose number format needs to change depending on the value of a specific cell. So for example: If cell B20 has value "A", cells Q20:AG20 should display as 1,234 If cell B20 has value "B", cells Q20:AG20 should display as 5.6% If cell B20 has value "C", cells Q20:AG20 should display as 7.89 Etc... As conditional formatting does not seem to include a number format option I've worked out that I need some sort of macro to change these settings for me, either automatically whenever the value of B20 changes or, more likely, when I manually choose to run the macro.
View Replies!
View Related
Hide/unhide Number Of Columns Based On Cell Value
If I have a value in A1 and run a macro [button], I want to have it hide a number of rows. Each number is one column. eg if A1 = 2 then B:C are visible, D:IV Hidden if A1 = 3 then B:D are visible, E:IV Hidden if A1 = 4 then B:E are visible, F:IV Hidden up to 200 columns. I tried it as a select case, but it is limited to just 22 cases which obviously not enough.
View Replies!
View Related
Insert Number Of Rows Based On Cell Values?
Column B contains geographical Areas. Column C contains a list of business departments. North Env North Env North Ops North Sales North Sales North Sales South Env South Maint South Ops South Sales South Sales South Sales Etc. The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
View Replies!
View Related
Offset From Cell Based On Inputed Week Number
I want to have a variable range. ie. This value M4 needs to change based on the week. Range("M4").Select If it's week 1, then it can be say M4, but week two will need data to go to N4. I have found out how to get the week number from the user. ie. ' Select Week Number Dim NumSheets As Integer Dim Prompt As String Dim Caption As String Dim DefValue As Integer Prompt = "What week do you want to get data for?" Caption = "Week Number" DefValue = 1 NumSheets = Val(InputBox(Prompt, Caption, DefValue)) If NumSheets > 52 Then MsgBox "Week Number too high" If NumSheets < 1 Then MsgBox "Week Number too low" I tried then linking this value saved as NumSheets by: Dim Rng As Range If NumSheets = 1 Then Rng = Range("D3") If NumSheets = 2 Then Rng = Range("E3")
View Replies!
View Related
Calculate X Number Of Rows In Range Based On Cell Value
I can't seem to make SumIF or Vlookup do what I want here. I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column. If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3) Period Target Actual %Target 1 74 68 91.9% 2 81 71 87.7% 3 76 87 114.5% 4 76 68 89.5% 5 71 89 125.4% 6 69 81 117.4%
View Replies!
View Related
Place Month Of Date Cell On Another Worksheet
I have two workbooks, one a database that accumulates data over time, and the other a monthly report that needs to be populated based on information from the database. When moving data between the two, the following works fine (snipped a lot for ease of reading): Workbooks.Open (database) ', Password:= / Workbooks(reportbook). Sheets("Sheet1"). Range("A1").Value = month(Cells(282, 5)) As you can see, it simply puts the month from a cell in the database into the report workbook. However, if I change Range("A1") to Cells(1,1) I get a runtime error: Workbooks.Open (database) ', Password:= / Workbooks(reportbook).Sheets("Sheet1").Cells(1, 1).Value = month(Cells(282, 5)) I can generally work around this issue by changing the active workbook, but doing so is a big pain. Is there any way to use the second bit of code without popping an error message?
View Replies!
View Related
Place Values In One Cell On CheckBox Clicks
I have a user form that has 12 check boxes corresponding to the months on the year. All or none of the check boxes can be ticked at the same time. How can I get the value of the check boxs to one cell on the worksheet. ie. Check boxes 'Jan', 'Feb', 'June' and 'Oct' are ticked. On the worksheet, cell A1 would say 'Months chosen: Jan, Feb, June, Oct'
View Replies!
View Related
Identify Row Number Based On Value In A Cell And Use That Row Number In A Macro
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use. Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE? ---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE) I have attached a scaled down version of the Workbook. Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
View Replies!
View Related
How Do I Fill In A Cell Based On The Relationship Of The Number Within A Range Of Numbers
This is a spreadsheet for a real estate office. The boss gave me a spreadsheet that he created to give a seller an idea how much money he will walk away with at closing. The spreadsheet includes all of the pertinate numbers but there is one item that he would like to have filled in automatically. When selling a house, you use a title service that charges a fee based on the price of the house. So, for example, If the house sells for $0 to $50,000, then let's say the fee is $112. If the house sells for $50,000 to $60,000, then the fee is $118. If the house sells for $60,000 to $70,000, then the fee is $125, and so on up to 1.5 million. The boss' spreadsheet includes 5 different selling price scenarios, which I guess doesn't matter since you do the first one and the rest follow the same pattern. So how do I automatically fill in a cell based on how one number falls within a range of other numbers? The fee schedule will be within the same spreadsheet, if that matters. Ideally, I think they should be separate files, but this will be used in various locations as different realtors take it home or use it on their laptops.
View Replies!
View Related
Conditionally Change Number Format Based On Another Cell's Value
Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General' Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function. Column AL's value is based on an IF formula which goes like this: =IF($C4="F/T",SUM($AJ4)+($AK4/2),SUM($D4:$AH4)) Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format). I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.
View Replies!
View Related
Place In Sequence Increasing The Numbers Of One I Creak In A Cell
I would like to place in sequence increasing the numbers of one I creak in a cell. In the formula I determine it I creak and the corresponding frame number to the placed ones. The problem is that he is accumulated only the greater and not sequencia it. 1803456781101112 1134567810111280 but,80-80 Function ordenar2(Myrange As Range, num As Integer) As String Dim Myorder As Double Dim X2 As String Dim n As Integer n = 1 Do While n
View Replies!
View Related
Custom Cell Format In Which Space Follows Thousands Place
I am trying to write a custom cell format that would allow me to replace the comma with a space after the thousand's place. Therefore, 1 million would look like: 1 000 000 instead of 1,000,000. I do NOT want to change my regional settings to accomplish this. When I write ### ### ### it accomplishes this, however, there is one small glitch. When you apply an "underline" you get the following result: _1 _10 _100 _1 000 _10 000 _100 000 1 000 000 10 000 000 100 000 000 The underline does not lineup evenly with the "ones" digit until we reach 1 million. I want to write a universal format in which the underline lines up evenly regardless of the number.
View Replies!
View Related
IF And OR Statement (populate A Cell Based On Which Number Meets The Criteria I Define)
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0. =IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
View Replies!
View Related
Fill Conditional Cell Values Based On Unknown Number Of Rows
I have a set of data in column R, with an unknown number of rows that looks like this Days Late -28 150 3 16 41 . . . and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:
View Replies!
View Related
|
TRACKER
