Add Or Subtract Depending On Text Value In Another Column
May 3, 2007
I have a workbook to record the attendance of students. Sheet attendance9 for September's attendance. Enter 1 for present, 0 for absent, L for late, E for early leave and blank cell for no schoolday. Sheet "remark9" is used to record detail of attendance for September. In sheet "attendance9", I want to display in cell AT2 the number of times a student absent for 7 or 14 consecutive days. How to count from E2:AH2?
View 6 Replies
ADVERTISEMENT
Feb 21, 2014
I have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then
F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)
So, New Column (O) = N - F
and the next New Column (W) = V-N ...
NOTE: Column G can be ignored.
I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?
[Code].....
I'm using Excel 2013.
View 2 Replies
View Related
Mar 11, 2014
I'm trying to subtract the value of a textbox in a userform from a value in a cell, and display this new value in another textbox. An added complexity is this takes place in Visio but communicates to excel to find certain values. I have the following code:
[Code]......
which displays the value of a cell in excel, in a textbox in a visio userform. The excel cell value then changes and I thought that it would work if I were to duplicate this code and run a new sub changing the last line to:
[Code] ....
But I get a type mismatch error on the line.
View 6 Replies
View Related
Nov 18, 2009
I would like to subtract 9:30 AM From 9:40 Am. I have tried the formula listed and I get, (0.00694444444444442 Mins) ...
View 7 Replies
View Related
Oct 15, 2007
We use excel to organize salaries and contracts for my fantasy baseball league. I need an easy way to go through all the teams and subtract one year from each players contract. This is an example of a team.
The contracts are in the columns like this NG-3
I would like to change that to NG-2
View 9 Replies
View Related
Jul 30, 2014
I'm working on a budget and financial calculator
I've already got it set up to calculate my pay and deduct taxes and all my bill and tell me how much I have left at the end of the month, now I am trying to get it set up to tell me how much will be left out of each check I get based on what day my bills are due.
Essentially what i want is I7 is the cell for my first check of the month and K7 is my second check, Column H shows my due dates for each bill, column A is the price on each bill so what i want is something that is similar to.
If the value if column H is between 5 and 19 then deduct the price in column a from I7 (with H and A being the same row) and if the value of column H is 1-4 or 20-31 then deduce the value in column A (with H and A being the same row) from K7.
Another way to put it would be I7 gets deducted the value in column A if column H is between 4-19 otherwise do nothing (with H and A having same row) deduct the value in Column A from K7 if column H is between 1-4 or 20-31 otherwise do nothing.
View 3 Replies
View Related
Mar 15, 2014
I have a column of times that are formatted 12:30:00, 14:02:00.....so on and so on. I want to convert these times to be 4 hours less than what they are. Is there a formula I can put in a cell somewhere that will do this for me?
View 9 Replies
View Related
Oct 4, 2006
I have data on A1 to A100. How can I subtract a same number (for example 10)from each cell of A1:A100?
View 3 Replies
View Related
May 22, 2013
I have an excel with column value as below(sample)
3
4
6
7
empty cell
4
5
3
1
7
empty cell
4
5
empty cell
and more...
Actually i want it to reduce others with lowest number among them.. and after every empty cell, it should find the lowest number again and reduce it from others.. this should repeat until the column data completes. after processing it should find as below
0
1
3
4
empty cell
3
4
2
0
6
empty cell
0
1
empty cell
Any macro or excel formula for processing such a kind of excel. Empty cell works like a delimiter..
View 3 Replies
View Related
Jul 13, 2009
I have is a cell with a begining balance and a cell witha current balance.
I also have a column in which each expenditure will be placed ina cell in that column in sequential order.
What i want to do is take the value in the first cell of the expenditure column and subtract it from the begining balance and the place the result in the current balance.
But after the first cell i will need to subtract each value from its previous value to continue counting down so to speak.
I am at a loss on how to move the last value which will also be the lowest value in the column to the cueent balance cell.
View 13 Replies
View Related
May 17, 2009
I have tried a few other examples in previous threads that use COUNTIF and ISERROR but I just can't get it right.
Basically I want to average the cells in column J (J2:J6,J8:J12,J14:J18, etc).
My aim here is to determine the average value for each day (Averaging 0 values in this instance is OK).
Now, the date in column A (A2:A6,A8:A12,A14:A18, etc) sometimes returns a blank value, depending on the day of the week (see attached example). This is because, in the full version of the workbook, I have Column A date linked to a perpetual calendar and therefore the dates can change cells depending on the year.
For the Date Cells that return a blank value, I do not want the AVERAGE equation to include the 0 value on these days.
For an example I have attached a month of February where there are blank date cells at either end of the column.
View 13 Replies
View Related
Apr 28, 2012
I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
View 6 Replies
View Related
Apr 15, 2009
I am trying to nest a formula with IF & AND to work out a RAG status. I have 2 questions per row (in Columns I:J) which will be answered either Yes or No. For each combination I need to give a RAG status in Column K – e.g. two Yes replies give Green, 2 No replies give no and one No out of 2 will give Amber
Below is hoped for response in Column K:
ColI---Col--- JCol K
Q1----Q2----RAG
Yes---Yes---G
Yes---No----A
No----Yes---A
No----No----R
View 3 Replies
View Related
Jan 8, 2007
I have a very long excel sheet. What I want to do is, depending on the input in one column (text), return a another text in another column, for the whole sheet.
I tried to with Select Case Statement and if-then, but couldn't figure it out since I don't have much VBA experience.
View 6 Replies
View Related
Apr 8, 2007
I have a spreadsheet which is attached. I want text to be entered into column A only if there is text in column B. The text to be entered is taken from a cell in another workbook. I have removed the lin to up it to this fourm.
View 5 Replies
View Related
Mar 1, 2008
I have a cell A5 in a worksheet that can be either E or W. I am looking for a formula in Cell A10 in the same worksheet to return numeric values of either +1 if cell A5 is W or -1 if cell A5 is E
View 3 Replies
View Related
Sep 15, 2014
See attached work book, it is an mot booking system that is all working fine except for one thing that I don't know how to change.
At the moment if you double click a cell in the test type column it enters the required data and turns the cell green highlighting the length of time and values this is all good but I am wanting the ones that start with n-side to turn yellow just so that we can see our own internal tests easily. It is control in the coding not formatting . Is there a way this can be done?
View 2 Replies
View Related
May 30, 2008
I will have a spreadsheet similar to the image provided. Basically, I need the macro to go row by row down column I. If the cell in column I contains the word "Tech" I need the cells from column B to P to be shaded color1; if it contains "Update," color 2; if the cell is blank; leave the cell as is. Once the macro has done this, it will continue to the next row and to the same check.
The number of rows in the spreadsheet will change on a day-to-day basis depending on the data that's pulled, but the columns should remain the same.
So I think I have the shading part. This is what I have so far. I'm not sure if this is the most efficient way of coding it, but it kinda works. I guess another problem would be if in the future the column with this info (column I) changes and shifts.
View 10 Replies
View Related
Sep 3, 2007
When a number is entered in cell S3, it triggers formulas throughout the worksheet to populate the it with information from another sheet. If the length of the text in B6 is greater than 80, the font size for B6 only should be 8; if the length of the text in B6 is less than or equal to 80, the font size for B6 should be 10. Regardless of the length of the text in B6, the font size for the rest of the sheet should not be changed.
I tried the following
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$6" And Target.Count > 1 Then Exit Sub
Dim cnt As Long
cnt = Len(Cells(6, 2).Value)
If cnt > 80 Then Cells(6, 2).Font.Size = 8
ElseIf cnt < 80 Then
Cells(6, 2).Font.Size = 10
End If
End Sub
View 9 Replies
View Related
Oct 29, 2007
I have a list of data in colum A and I want to group them together.
In column D I number the group with the same number.
A1 = Tony D1 = 1
A2 = Tom D2 = 1
A3 = Bill D3 = 1
A4 = Helen D4 = 2
A5 = Marg D5 = 2
A6 = Kathy D6 = 2
A7 = Catherine D7 = 2
And so on..... The list up to 100 names
As you can see group 1 = 3 names, group 2 = 4 names, group 3 = 6 or maybe 7 and so on.
I'm trying to set ranges for them Eg:
set rng1 = A1:A3
set rng2 = A4:A7
but it not fixed to set ranges like that. It's variable and changes every week. Next week maybe rng1 = A1:A5, rng2 = A6:A9.
can I have the VB code to determine these variable ranges.
View 9 Replies
View Related
May 25, 2014
I need command that takes in consideration values from column F only if in column B is value 4.8. And than, I need minimum value of those taken in consideration .d.png here is attached picture regarding my problem.
View 8 Replies
View Related
Jul 6, 2009
i am looking for a formula, if i put it as plainly as i can then i hope someone can work it out for me!
if ad4 is greater or less than 30 then ad4 text is red, if ad4 is equal too 30 then text is green
View 14 Replies
View Related
Aug 28, 2007
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
View 4 Replies
View Related
Jan 7, 2009
I want to add up items in column H, but only if Column J says "yes" in the same row.
I tried a few methods as I always try to do, but I came up short with this one.
View 2 Replies
View Related
May 15, 2007
I want to create a macro that make the somme of 2 other columns when the nummer of the first column ist double(more than one ) and put the somme in a new Rows. like this :
A B
23 5
23 12
23 2
45 4
45 1
46 3 in the news the resultat in the same columns it´s like this
A B
23 19
45 5
46 3
View 10 Replies
View Related
Mar 4, 2009
If i have row 10-20 in column A filled, what vba code would i need to select a range in column C that selects rows 10-20
If that 10-20 rows in column A chnages to say 10-50 the vba code will select 10-50 in column C
View 10 Replies
View Related
Dec 19, 2013
I am creating jet report, however am having to use Excel formula to create totals.
I am trying to sum the amounts in column I depending on the currency, although as it's a jet report it needs to update when the report is run.
My formula: =SUMPRODUCT(SUBTOTAL(9,OFFSET(I4,ROW(I4:I6)-ROW(I4),0)),(F4:F6=Options!B4)+0)
View 2 Replies
View Related
Feb 22, 2007
Let's say I have data validation on column A, which only allows 2 values, "Number" and "Letter". Easy enough. If the value "Number" is selected in A1, when the user moves to B1, I want a validation list of 1,2 and 3, when they move to C1, I want a list of 4, 5 and 6 and when they move to D1, a list of 7, 8 and 9. However, if "Letter" is selected in A1, when the user moves to B1, I want a list of R, S and T, when they move to C1, U, V and W, and when they move to D1, a list of X, Y and Z. I tried using dependant validation, but can only get all three columns (B, C and D) to either be 1, 2 and 3, or R, S and T. The validation lists would be existing named ranges on a separate sheet.
View 2 Replies
View Related
Dec 31, 2013
I have an excel sheet which looks similar to below and with some more columns.
HTML Code:Â
AreaBranchEmpolyeesProductsShelfs
Area1Area1 Branch110Product 110
Area1Area1 Branch25Product 16
Area1Area1 Branch38Product 28
[Code] ......
I want to have data in other sheet With respect to Product details. Where It gives me the count of Product 1 and so on.
I am able to get the count using countif function and it displays like below.
HTML Code:Â
Product Count
Product 16
Product 28
Product 34
Product 49
Product 510
Product 68
Product 78
Product 89
Product 97
Product 108
What I want is when I select "Product 1" It should display some selected columns data from sheet where complete data is available.
HTML Code:Â
Branch Empolyees Other
Area1 Branch1 10 abc
Area1 Branch2 5 xyz
Area1 Branch5 7 pqr
Area2 Branch5 5 wxy
Which function we can use. I tried Vlookup but was not successfull.
View 14 Replies
View Related
Dec 22, 2013
I would like to know the formula to highlight the color of text in particular cell if the value of the particular cell ( value is text ) is so and so.
For Ex: If the J6 is "Vacation" then the J1 text should be in red color.
View 5 Replies
View Related
