Stopping A Cell From Summing And The Remainder Go To A Different Cell 

Stopping A Cell From Summing And The Remainder Go To A Different Cell  Excel 
View Answers 
Hello. im wondering if anyone can help me with this excel problem... i want a cell to give me the sum of a range...i want it to stop adding at 40 and the remainder to go to a different cell.. i also want to know if two cells can do this and one cell catch the remainder of both
Free Excel Courses
Similar Excel Tutorials
The MOD function is very simple but it can be used to do wonderful things in Excel. It returns the remainder after ...
Add values from every x number of rows in Excel. For instance, add together every other value in a list, or every 5 ...
How to prevent a range of cells from adding up to a negative value, or preventing the same cells from adding to a p ...
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...
Subscribe for Weekly Tutorials
Helpful tutorials delivered to your email!
Helpful Excel Macros
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
 Remove all data validation from a cell in Excel with this free Excel macro. This is a great macro to use when you need
 This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear when
 This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
Similar Topics
Hi was wondering if anyone can help.
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
Any help would be very much appreciated
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
Any help would be very much appreciated
Hello all,
Until now I have been able to find all my answers through searches. As a VBA novice, it has been very helpful. I am stumped on this one, however. I am trying to autofill from the selected cell in Column C down. I would like it to stop at the last cell with data in Column B. This is the code I have so far:
Range("C2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
This does the autofill, but doesn't stop at the last cell with data in Column B.
In the past I have use this code to acheive similar results:
Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & endRow)
The problem with this code is that I will not always be starting in "C2". I need code that uses whatever the selected cell is.
All help is appreciated. Thanks!
Until now I have been able to find all my answers through searches. As a VBA novice, it has been very helpful. I am stumped on this one, however. I am trying to autofill from the selected cell in Column C down. I would like it to stop at the last cell with data in Column B. This is the code I have so far:
Range("C2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
This does the autofill, but doesn't stop at the last cell with data in Column B.
In the past I have use this code to acheive similar results:
Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & endRow)
The problem with this code is that I will not always be starting in "C2". I need code that uses whatever the selected cell is.
All help is appreciated. Thanks!
Hello everyone, I am new to this forum and have been using excel for about 3 years. I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say "3 i.e. 3 tires or cans of coke" and later that day i come back and need to add 2 more ......so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance.
James
James
Ran into a problem I've never seen before. When dragging a cell across that contains a sum equation the result was the following cells with the same value. Inside the cell it showed what would be the correct new equation, but the value was still from the original cell. Any ideas on what's causing this?
If I double click the cell and then hit enter it will retotal the area and then give me the correct value, but I'd obviously rather is just work right the first time.
If I double click the cell and then hit enter it will retotal the area and then give me the correct value, but I'd obviously rather is just work right the first time.
I am looking for assistance in having one cell in a text format equals another cell that contains a time value in hh:mm format.
For example: Cell A1 has a time format (hh:mm) value of 04:00; which is the Start Time. I would like cell D1 to have a text format value of "04:00" (result is dependant upon what is entered in A1). I would duplicate the same formulas to reflect Stop Times in other cells.
My final result is to have another cell (F1) use the Concatenate formula to have the Start and Stop time shown in one cell as "04:00  12:30". The times would change based on the Time formated values entered into the Start and Stop time cells.
I have researched this in the board and found many excellent ways to do the opposite, but not convert Time format to Text format. Any assistance is greatly appreciated.
Shane
For example: Cell A1 has a time format (hh:mm) value of 04:00; which is the Start Time. I would like cell D1 to have a text format value of "04:00" (result is dependant upon what is entered in A1). I would duplicate the same formulas to reflect Stop Times in other cells.
My final result is to have another cell (F1) use the Concatenate formula to have the Start and Stop time shown in one cell as "04:00  12:30". The times would change based on the Time formated values entered into the Start and Stop time cells.
I have researched this in the board and found many excellent ways to do the opposite, but not convert Time format to Text format. Any assistance is greatly appreciated.
Shane
Hi everyone, this is my first post here. I consider myself a more advanced Excel user and can navigate my way around fairly well. I've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
Can anyone help with this, because I can't figure it out....
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
Can anyone help with this, because I can't figure it out....
I've been using Excel for years and had very few issues. However, I recently went into a spreadsheet to update it and was unable to select and enter data into an individual cell. When I click on a cell and try to enter #s nothing happens (my num lock is on). Then when I try to click into another cell it just highlights that cell, along with any other that I move my cursor over. Once I click on one cell I can't stop the highlighting from happening. I can't even click on anything in the toolbar. I am extremely confused, can anyone help?
I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.
The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!
Code:
The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!
Code:
Dim myRange As Range Dim cell As Range Set myRange = Range("V6:V50000") For Each cell In myRange If cell.Value < 2 Then cell.Font.ColorIndex = 5 If cell.Value < 1 Then cell.Font.ColorIndex = 3 Next
Hello,
I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:
I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.
I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?
If somebody could help me out that would be great.
Thanks,
Randy
I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:
I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.
I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?
If somebody could help me out that would be great.
Thanks,
Randy
I was wondering if anyone could help me out please?!?!
I need to have a cell on Sheet2 display the TEXT from a cell on Sheet1, is his possible??
Example:
Sheet1, Cell A1 contains a clients name and I would like to have Sheet2, Sheet3, Sheet4 etc... Display that clients name in a cell of my choosing (could be a different cell on each Sheet) automatically after entering it once on Sheet1, cell A1.
I thought this was possible but I can't seem to figure it out. I haven't done any real Excel work in quite a long time but I thought I had done this before a long time ago!! Any help would be GREATLY appreciated!! Thanks in advance...
P.S. If I use autosum to do this it just displays the number 0, obviously, since it is trying to add numbers...
I uploaded a copy of the spreadsheet, What I would like to happen is have the text from Sheet1,D4 automatically be placed in Sheet2,A1 and Sheet3, B2. Hope this helps
Aric
I need to have a cell on Sheet2 display the TEXT from a cell on Sheet1, is his possible??
Example:
Sheet1, Cell A1 contains a clients name and I would like to have Sheet2, Sheet3, Sheet4 etc... Display that clients name in a cell of my choosing (could be a different cell on each Sheet) automatically after entering it once on Sheet1, cell A1.
I thought this was possible but I can't seem to figure it out. I haven't done any real Excel work in quite a long time but I thought I had done this before a long time ago!! Any help would be GREATLY appreciated!! Thanks in advance...
P.S. If I use autosum to do this it just displays the number 0, obviously, since it is trying to add numbers...
I uploaded a copy of the spreadsheet, What I would like to happen is have the text from Sheet1,D4 automatically be placed in Sheet2,A1 and Sheet3, B2. Hope this helps
Aric
Corporate edict.
I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".
If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).
What I am looking for is this. If the user selects that cell again, they will get the usual popup message, "The cell or chart that you are trying to change is protected..."
I think I am close, but I am getting an "End If without block If" error on the If Clause.
Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".
If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).
What I am looking for is this. If the user selects that cell again, they will get the usual popup message, "The cell or chart that you are trying to change is protected..."
I think I am close, but I am getting an "End If without block If" error on the If Clause.
Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
Hi all 
Im very familiar with how to write a macro to automatically autofilter a list based upon a Cell Value (i.e. Range =(A1)...etc.).... but what I cannot figure out how to correctly write is a way to have Excel automatically autofilter a list for any row that CONTAINS the Cell Value, instead of just the exact value.
Cell Value = "birds"
Example  I need to filter every row so that I see every row that CONTAINS "birds" in the characterstring, not just the row that = "birds".
a) is this possible?
If not, I'd like to know also so I can stop attempting to guess (;
Thanks!!!
Matt
Im very familiar with how to write a macro to automatically autofilter a list based upon a Cell Value (i.e. Range =(A1)...etc.).... but what I cannot figure out how to correctly write is a way to have Excel automatically autofilter a list for any row that CONTAINS the Cell Value, instead of just the exact value.
Cell Value = "birds"
Example  I need to filter every row so that I see every row that CONTAINS "birds" in the characterstring, not just the row that = "birds".
a) is this possible?
If not, I'd like to know also so I can stop attempting to guess (;
Thanks!!!
Matt
Entering time values in custom format [h]:mm:ss
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell  then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell  then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
Greetings all,
How do I link cells so that I can make data entries into either cell? I believe this is called 2 way cell linking.
In short, I want to have a cell on one tab linked to a cell on another tab and be able to change the value of both cells by entering data either tab.
How do I link cells so that I can make data entries into either cell? I believe this is called 2 way cell linking.
In short, I want to have a cell on one tab linked to a cell on another tab and be able to change the value of both cells by entering data either tab.
I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" > 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" > 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
Is it possible to have numbers added to the same cell and have excel continue
to calculate the addition for me in that same cell......ex: I have the number
8 in cell d2 and I want to add the number 8 to that cell and have excel add
the 8 to the previous 8 for a total of 16 in the same cell.....the next time
I would add 5, and the total would be 21? Can this be done in a single cell?
to calculate the addition for me in that same cell......ex: I have the number
8 in cell d2 and I want to add the number 8 to that cell and have excel add
the 8 to the previous 8 for a total of 16 in the same cell.....the next time
I would add 5, and the total would be 21? Can this be done in a single cell?
Hello All, I have a column where cells need to be 50 characters long, it contains addresses, however, since all the addresses do not meat the criteria, I need to add (blank spaces) to reach the 50 characters, any idea on how to approach this?
Example
Fire Street #50 this cell contains 17 characters therefore I need to add the rest by adding blank spaces to reach 50 characters, otherwise the system I am exporting the data will not understand this cell
Example
Fire Street #50 this cell contains 17 characters therefore I need to add the rest by adding blank spaces to reach 50 characters, otherwise the system I am exporting the data will not understand this cell
Hi folks,
My problem is Tab no longer moves your active cell selection one to the right. It now moves to the last cell in your spreadsheet to the right (similar to CTRL+Arrow Key Right).
I seem to remember this being a simple setting you can change, but was unable to find it myself, or search the web for the way out. So my last result is asking the experts .
Do any of you know how to make it so Tab goes back to just moving one cell to the right again?
Thanks in advanced.
My problem is Tab no longer moves your active cell selection one to the right. It now moves to the last cell in your spreadsheet to the right (similar to CTRL+Arrow Key Right).
I seem to remember this being a simple setting you can change, but was unable to find it myself, or search the web for the way out. So my last result is asking the experts .
Do any of you know how to make it so Tab goes back to just moving one cell to the right again?
Thanks in advanced.
Hi guys,
would really appreciate anyones help with this.
I have a column full of textformatted fractions...
4/5
6/4
3/1
2/5
4/5
etc, etc.....the column is very long!!
I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.
If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!
Any suggestions on how I could speed this up?
thanks
would really appreciate anyones help with this.
I have a column full of textformatted fractions...
4/5
6/4
3/1
2/5
4/5
etc, etc.....the column is very long!!
I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.
If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!
Any suggestions on how I could speed this up?
thanks
I would like the contents of a selected active cell to be displayed in a certain other cell, say R4. When another cell is selected and active, that cell's contents should be displayed.
I have an array of 9 columns and 20 rows with equity symbols and conditionally formatted to show the severity of price movement in either direction.
Instead of typing in the value of whatever cell a trader is looking at I would like for them to just be able to click on the cell and have that symbol copied into R4 so some detailed information can be displayed for them.
Can't find a thing about this on any forum or msdn.
Hope I'm explaining this at least somewhat clearly.
Thanks.
I have an array of 9 columns and 20 rows with equity symbols and conditionally formatted to show the severity of price movement in either direction.
Instead of typing in the value of whatever cell a trader is looking at I would like for them to just be able to click on the cell and have that symbol copied into R4 so some detailed information can be displayed for them.
Can't find a thing about this on any forum or msdn.
Hope I'm explaining this at least somewhat clearly.
Thanks.
I am trying to automatically capture and record the date of a cell's last change in value (date stamp). I have an item price list and if a particular cell gets updated I want to automatically record the date of change of that cell. I realize that after I change cell A1 I could tab to cell B1 and enter Ctrl+; but if I have a hundred new prices to enter I don't want to do that (plus me or the data entry person might forget).
For instance,
A B
1 $32.17 8/17/08
2 $43.20 9/12/09
If I go in and update cell A1 to $33.98 today I would like cell B1 to read 2/16/10. But I don't want cell B2 to change.
Thanks
For instance,
A B
1 $32.17 8/17/08
2 $43.20 9/12/09
If I go in and update cell A1 to $33.98 today I would like cell B1 to read 2/16/10. But I don't want cell B2 to change.
Thanks
Today I ran into an odd problem. I typed in values for column A rows 1 through 10 then values for column B rows 1 through 10. then in column C, I made the formula C1=A1/B1. The math was correct it showed 542 in the C1 cell. So I dragged that formula down and it showed 542 in all column C cells which is not correct. And when I went to check to see if the formula was correctly dragged it was. For instance, the formula in C2 is =A2/B2 however the value of that cell showed 542 which was not the correct math/value.
But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.
This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.
Thanks for any tips on this.
But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.
This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.
Thanks for any tips on this.
i have a cell i have to check if it contains six characters. I have a list of data that i need to narrow down to six characters. I have successfully done that, but some of the cell has 5, 6, or 7 characters. The list contains about 600 cells, but i don't have time to format them individually. I want to create a formula that returns true or false if the cell contains 6 characters and false if it is above or below 6.
Can somebody help me, i'm new to excel!
Can somebody help me, i'm new to excel!
Hi everyone,
I'm going nuts trying to figure out how to autofill text from one cell
to another. For example, everytime I type text into cell A20, I want
the exact same thing to cell BL20. The same for B20, BL20, etc.
How do I go about that? I already know how to copy formulas from one
cell to another by dragging the skinny black line of the cell. I guess
I just need the formula for a simple IDENTICAL copying of text.
Thanks in advance,
Mike
I'm going nuts trying to figure out how to autofill text from one cell
to another. For example, everytime I type text into cell A20, I want
the exact same thing to cell BL20. The same for B20, BL20, etc.
How do I go about that? I already know how to copy formulas from one
cell to another by dragging the skinny black line of the cell. I guess
I just need the formula for a simple IDENTICAL copying of text.
Thanks in advance,
Mike
I often use if statements to return empty cells, for example:
=IF(a1=0,"ERROR","")
The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrowdown because it seems to think that these empty cells have contents.
Is there a way to designate, in such a formula as above, to return a TRULY empty cell?
Thanks
=IF(a1=0,"ERROR","")
The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrowdown because it seems to think that these empty cells have contents.
Is there a way to designate, in such a formula as above, to return a TRULY empty cell?
Thanks