In cell D16, I would like the sum of D35 (only if D29 contains data) to D54 (only if D48 contains data)...there is more than just adding these two - there's actually 25 that follows the pattern.
View 9 RepliesAll I want to do is the following;
I have a workbook with 18 columns and 9000rows for each column,
All I want to be able to do is ad a"," (Comma) in each cell at the end of what ever is in each cell.
Tried looking in the help of excel, but if you can't write in properly what you want, it doesn't give you the answer of how to do this in the help list
I need to add drop boxes (from data already listed on another worksheet) to every cell in a single column. Drag down, easy! My problem is that I already have the required list (names in this case) pasted into the column from another sheet.
Is this possible?
I have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.
I am looking for a formula in D7
In col E I have a range of numbers
In Col AA I have yes or no
I would like to add all of the col E amounts from line 18-72 where the answer in AA of the same line is no or blank
E AA
18 2.00 Yes
19 3.00 Yes
20 5.00 No
21 7.00
D7 = 12.00
I have an excel 2008 sheet and want to add cells in an odd way. Here is the best way I can describe it.
I want a formula to add cells based off a number value in another cell.
So it would be something like
IF M3= 1 add C5 to SUM ; If not then go to next step
IF M3= 2 add F5 to SUM ; If not then go to next step
IF M3= 3 add I5 to SUM ; If not then go to next step
IF N3= 1 add C5 to SUM ; If not then go to next step
IF N3= 2 add F5 to SUM ; If not then go to next step
IF N3= 3 add I5 to SUM ; If not then go to next step....................
i have an excel file which contains data, I want to be able to search this file and to fill the cell and its next 3 cells to the right with a certain colour. Is this possible as I have wrote some vba to do this but, Im only able to search and not fill the cells with the correct colour.
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
I'm attempting to create a macro (1st time) that will copy data from one worksheet and paste it into another. The problem I have is that I need to run this multiple times so the pasting event needs to occur on a new row each time. As it is, I can only paste new data over existing data, so I need code to find the next empty row. Here's what I have so far, which obviously doesn't work:
View 9 Replies View RelatedI've a worksheet with 36 columns to keep track of a football confidence pool,I've a CF in which a W is entered in every other column from B1:AG1 and it highlites the whole column yellow,in column A is all players names,from column B2:AG2 I enter the 32 NFL teams,in row B3:AG3 I enter points from 1-16,in column AI it adds all numbers in yellow per row which gives me a weekly score,in column AJ I've the players name and there year to date points and finally in column AL it ranks the players by highest point total.
I will use this on a weekly bases for 16 weeks,what I need is for column AJ to keep adding the players points once I delete there respective numbers from rows B3:AG3 and add new ones for the following week.
Formula in cell AI3 =sumif($B$1:$AG$1,"W",$B3:AG3) copied down
Formula in cell AJ3 =A3 & " " & AI3 copied down
Formula in cell AL3 =rank(AI3,$AI$3:$AI$8,0) copied down
I also have the following macro an my sheet which allows me to use the formula in column AI3.
Function ConditionalColor(rg As Range, FormatType As String) As Long
'Returns the color index (either font or interior) of the first cell in range rg. If no _
conditional format conditions apply, Then returns the regular color of the cell. _
FormatType Is either "Font" Or "Interior"
Dim cel As Range
Dim tmp As Variant
Dim boo As Boolean
Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
Dim i As Long
I have a userform that I'm using to add data to a worksheet, with the following
Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")
OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
......
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.
IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?
I've made a file with 4 active sheets.
Form - to submit the data of items incl 2 buttons to add the data in 2 sheets and 1 email button wich is active and working.
I would like when the data is been added in the form cells that when by example when i push the button for new assets these data in correct order be placed in the new asset sheet horizontal and in the right column. Not with a pivot table i need to keep the layout of the top line in new asset sheet as it is by procedure.
Same for the asset disposal button and sheet.
I have a number of time data elements of different recordings that i would like to be able to use one formula in one cell and have it give me to total time. For example:
00:03
00:55
00:47
00:05
All in one cell (i.e., not having each data point in a cell), I would like to have the formula tell me that these four recording times equal 00:01:48 (i.e., 1min 48 sec).
I've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more.
Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc.
Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
I have some text I wish to add some content to the front and back of it. i.e. the text in the cell is "214.212.134.62" and I want to add "http://" to the front of it and ":9999" to the back to it, so it looks like "http://214.212.134.62:9999" in a different cell.
View 3 Replies View RelatedI want to add across columns, which is not so difficult but I have to "ignore" the blank cells because of the way my formula works.
I'm not sure of the correct mathematical term for the addition I have to do but here is my example:
in this row are the values
1 2 3 4 5
In the row below I want to add the numbers so the result is
1 3 6 10 15
basically, a cell is always adding itself to the result of the sum from the previous cells. This formula I can handle, (=A2+B1 then autofill the results by dragging the formula across the empty cells) but when there are blank cells between values I get the error message! value. How can I write a formula which will ignore the empty cells?
i have a problem adding cells every 5th cell for example i have
=SUMPRODUCT((C10:C67)*(MOD(ROW(C10:C67),5)=0))
this adds every 5th row in this range and it does it right as long as there is no text in the cells in between
it works fine when it looks like this
1
1
1
1
1
1
1
but it does not work when it looks like this
1
1
text
1
1
1
when this happens it appears as #VALUE, meaning an error but i don't understand why if i'm just telling it to look at every 5th row and those specific rows have no text in them
i have a spreadsheet with text in between the cells that need to be added so i need a formula that only adds up the cells with numbers on it, to be specific the numbers are either ones or zeroes.....
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies View RelatedI m adding cell with alphabets. I attach a pic of what i want it to look like. Is there a command to do it for you instead of me outting one by one myself? Ex. 'A' must first followed by 'B' and so on, futhermore, if there a CApital 'A' and a lower case 'a' the capital 'A' goes first.
View 14 Replies View RelatedUsing =A7&","&B7 to add data from two columns into one, separated by a comma. What about multiple columns, still separated by a comma? See Example in attachment.
View 2 Replies View RelatedLooking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
every month i download a sales list of which there are about 200 transactions.
each day there is a different amount of sales ie
feb 1st 9.99
feb 1st 14.99
feb 1st 7.99 (3 sales)
feb 2nd 8.99
feb 2nd 16.99
feb 2nd 14.99
feb 2nd 8.99 (4 sales)
"" "" "" ""
etc throughout the month
at the moment i am using the SUM to maunually add the sales per day
feb 1st 32.97
feb 2nd 49.96
as i need a daily record of income, however this is very time consuming
is there any way i can request the worksheet to add say column b for the 1st feb and show total
column b for 2nd feb and show total, thus saving myself hours of work ??
way to add up the total for each day
Here is what I have In cell H4 I put the following code =B5:G5
But when I add number in thous cells nothing happens. I want cell H4 to keep a running total of cells B5 through G5 so as I add numbers to the cells I would like the total in H4 to change.
While cells(r,40) or cells(r,41) are empty error 13 occures
Dim r
r = ActiveCell.Row
Cells(r, 43) = Me.TextBox3.Value
Cells(r, 41) = Cells(r, 41) + Cells(r, 43)
Cells(r, 42) = Cells(r, 40) - Cells(r, 41)
Why doesn't excel "see" empty cells as null?
I have a column of cells formatted with different colors to represent a range of values. I want to know if that a formula or VBA I can use to I sum up these range of cells with the same color.
View 10 Replies View RelatedI have three columns.
For instance, Column A are names. Column B are totals points and Column C is year.
I want to a cell too look up all names of "John" for the year 2007, then with those two conditions add up all total points for John in 2007.
I am trying to add a ROW of times. Each cell of times is using the 00":"00 format so I don't have to type a colon between the hours:minutes.
I'm not sure of what the formula should be for the Total Hours box.
For example, if I were to type 13:45 in the start time & 14:00 in the end time, I am getting a result of 0:55 which is incorrect
Another example if I leave the first 2 start/end boxes empty(blank) and then type 15:30 Start & 17:00 in the second set of Start/end boxes, I get a Total Hours of 1:70 which is also incorrect.
The formula I have in the Total Hours box is =SUM(B12-A12)+(D12-C12) which is know is incorrect.
What is the correct formula which will give me a Total Hours which adds a row of times correctly?
StartEndStartEndTotal Hours
I have a worksheet that has 5 rows and 5 columns. The five rows are Highschool subjects. Math, English, French, Phys. Ed, History. The Five columns are Monday, Tuesday, Wednesday, Thursday, Friday. I want to device a worksheet that say for Monday it will have YES for math, no for english, yes for French, yes for phys. ed, no for history. Tuesday would be say no for math, yes for english, no for french, etc, etc. I then want to use an if statement to convert the yes & no entries to a numeric value. I.E I have this right now. =If(B6="YES",1,0)
This simply puts a 1 for a yes and 0 for a no into cell M6. What I want tho is each subject to add the amount of yes's for the week and put it into cell M6. So if math is taken 3 times a week it would put a 3 into cell m6. if History is taken 4 times a week it would put 4 into cell m7, etc, etc.
1) I am trying to find a way to add together the total number of hours worked from shift start time to the end of the shift start time throughout the week.
2) Add together the total number of hours travelled. again from the start and end times throughout the week...(presumably, if I solve the first problem, it in itself will provide the second solution.!! (Grin)
Where shift start and end times are concerned, the cells are formatted as time, but I want the sheet to produce the results of the total number of hours worked, so that I may cost the hours and make payment. Ie: 62 hours worked = £ at such and such a rate, and 25hrs travel paid at £ different rate.