Autosum And Text In Same Cell?
Dec 18, 2013
I have made up a sheet to show approximate CO2 consumption of fluorescent and LED light fittings, and on my sheet I have used an autosum so that all you do is type in the quantity of light fittings you will have on a job and the sum will show you the yearly CO2 consumption automatically.
However, I was wondering if in the same cell as the auto sum could you choose to have some text displayed in the same cell after your sum?
Something along the lines of:
=SUM(A13*B13) (DISPLAY: Tonnes Of CO2 Used)
Its just so I don't have to have a separate cell adjacent to the autosum with my text displayed.
View 5 Replies
ADVERTISEMENT
Feb 22, 2007
I have a macro that takes a large block of info, sorts it by certain criteria, then adds blank rows, and puts in Autosum for the various blocks of info. Problem occurs when a particular "block" of info is only 1 row. Any dirty little tricks to keep the macro running past this roadblock? The section of code looks like
Range("O1").Select
ActiveCell.Offset(2, 0).Select
Do While ActiveCell <> "end"
If ActiveCell.Offset(-1, 0) = "TOTAL" Then
ActiveCell.Offset(-1, 1).Select
ActiveCell.Font.Bold = True
SendKeys ("%="), False
SendKeys ("~"), True
ActiveCell.Offset(1, -1).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
View 2 Replies
View Related
Jan 14, 2014
the autosum is not working on one cell which has a formula. All the other cells with formulas are summing correctly. The forumla i used is: =IF(C21="No","-1","") If i enter "No" in C21 the value -1 is return.
However the autosum does not seem to pick up this -1 and only returns 0.
View 3 Replies
View Related
Apr 3, 2007
I have a user that is having problems with AutoSum updating after a cell is deleted. He has the following...
100
200
100
400
If he deletes one of the cells, (lets say 200), the autosum number stays at 400. I reinstalled his MS Office and it worked for a few days, but now he is having the same problem.
So now he has...
100
100
400
View 9 Replies
View Related
Jul 3, 2009
I have a formula that is in ColF Row4, and it is copied down to ColF Row 13. But trouble is that when I autosum this column I get 6, but the answer should be 8.
The data in Rows A-E are extracted from an external data source. I've attached the spreadsheet. =IF((AND(A4=7,D4="")),1,(IF((OR(A4=7,D4>=2)),D4,0)))
View 4 Replies
View Related
Sep 10, 2013
with vba code that autosums column O two cells below the last row of data.
I am currently using the following code:
Range("O2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
Application.CommandBars("Standard").Controls("Autosum").Controls("Sum").Execute
SendKeys "{ENTER}", True
The problem is it switches over to editing after the autosum command and doesn't execute the sendkey correctly.
View 4 Replies
View Related
Dec 18, 2006
Let me preface this by saying I am very new to macros in Excel....I was wondering of there is a way or an existing macro that will automatically calculate the sum of a column at each inserted line break?
I am working with a spreadsheet with 3 basic columns...the first column has a dollar amount, the second a part name, and the third column a quantity. The spreadsheet is quite large, so I am hoping to find a way to have a macro autocalculate the sum of the dollar amount and quantity columns whenever a line break has been inserted in between parts.
View 9 Replies
View Related
Jun 10, 2006
Here is what I am looking for:
I have several groups of data separated by a couple of blank lines. For each group of data I would like to sum the rows in column F. I do not want to use the subtotal feature but rather the autosum feature for each section. It is important that the formula is actually entered so if I add additional rows of data the formula will keep calculating. So at the end of each data the formula is as follows =sum()
View 5 Replies
View Related
Feb 26, 2007
where I can input and keep track of my receipts for tax season. So there would be a date column, price, etc and for the price column I'd like to be able to not have to add all the totals manually.
I've looked around for something like this on the Microsoft templates site but haven't found anything so I think I may just have to make this myself but I don't know how to.
View 9 Replies
View Related
Jul 6, 2012
I have this autosum macro it does its job very well. Problem is that it returns autosum with absolute cell references ($ signs around cell numbers). This way I cannot copy paste formula around same row but different columns. I would rather have to copy paste code for each column and change declarations.
How to edit this code so that variable cell references are returned in the result.
Code:
Dim cel1 As String, cel2 As String
cel1 = ActiveCell.Offset(-2, 0).End(xlUp).Address
cel2 = ActiveCell.Offset(-1).Address
ActiveCell.Value = "=sum(" & (cel1) & ":" & (cel2) & ")"
View 3 Replies
View Related
Dec 2, 2006
The Autosum in all my Excel files is greyed out. I am able to use the icon button to perform autosums but am unable to turn off "autosum" from the tool bar. I have turned the EnableAutosum to "true" in the Editor with no luck. What am I missing?
View 2 Replies
View Related
Aug 21, 2014
I have a set where there are multiple rows on same date ( ascending order). The data set looks like following:
Date Variable 1 Variable 2 Variable 3
1/01/2014 (M-D-Y)
1/01/2014
1/01/2014
Insert a new row over here and calculate auto sum from 4 Column (D) onwards for all the data for 1/01/2014
1/02/2014
1/02/2014
1/02/2014
Insert a new row over here and calculate auto sum from 4 Column (D) onwards for all the data for 1/02/2014
As i have 365 days in a year and i have to insert a blank row for every date then calculate the auto sum for all the dates.
View 7 Replies
View Related
Apr 14, 2014
I have a cashflow spread sheet on which there are three properties that are currently being renovated and are being rented out. I would like to see ongoing costs for each individual property on the main spread sheet. I can filter and sort by the properties and autosum to get the individual costings of properties, but I would like to be able to see the costings of each property on the main spread sheet once I have stopped filtering.
View 1 Replies
View Related
Feb 25, 2009
I have a spreadsheet that runs a macro resulting in a different number of rows everytime the macro is run. Columns H, I & J are the only columns with currency in them. I would like a macro to find the bottom-most row of currency data in these columns and insert the Auto-sum formula in the row directly below each of these 3 columns. Then bold this row and increase the font by 2 points (or 12).
View 13 Replies
View Related
Jul 5, 2012
Trying to create a button that, once clicked performs the following task:
Checks whether the text in a cell in the column X2:X40 matches the text in a cell in the Range A2:U14.
If it does, the formatting of the cell in X2:X40 (eg background) must change to match that of the corresponding cell in A2:U14.
Only 1 cell in the range will match 1 cell in the column
For example:
Before click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background
During click:
matches these cells
changes X4 so that it also has a yellow background
After click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background
View 3 Replies
View Related
Jan 28, 2014
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
View 9 Replies
View Related
Jan 24, 2013
I have four cells that contain text. All have connected check boxes with TRUE FALSE.
I need to be able to select anyone one of these cells with a check box, and have it's text appear in one separate cell eg: A1.
I have no issue connecting check boxes etc. I have no issue reproducing the text from any of these cells into multiple cells with a check box. But they have to be selectable and reproducing in one cell only (eg"A1").
View 1 Replies
View Related
Mar 25, 2014
As per title, I am trying to compare a column of text cells which contain "Yes" or are empty and a columns of numbers. If they are "Yes" and "1" on the same row, I want to output an "OK" message. Excel seems happy with the following code but it does not work and returns an empty cell if the two conditions are true.
[Code] .....
View 6 Replies
View Related
Jul 31, 2014
Here's an interesting one:
A1 contains a text string which is both bold and unbold (
B1 is blank
I need a macro which scans the cells with text and copies the BOLD portions of the text string into the adjacent cell.
Example:
A1
The sky is blue
Macro is run
B1
sky blue
View 11 Replies
View Related
May 22, 2014
I have a list of text quotes in column A. I then have column B which will have a Y entered if the quote is used in a presentation. I currently have around 100 quotes.
On a separate sheet, I want to be able to effectively say in one cell - If column B has text in it, copy the quote from column A. But the difficult part is how do I make this happen so that I can have multiple quotes being pulled into one cell?
View 11 Replies
View Related
May 9, 2008
is there a formula that will find a word in a sentence written in a cell and if present then enter specif text in another cell? for example, if the word "Hotel" appears in the sentence in cell A5 then put in the text "Hotel and subsistence" in cell B5...
View 2 Replies
View Related
Apr 10, 2013
I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.
For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol
So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.
I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")
I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.
View 6 Replies
View Related
Aug 8, 2012
I am not sure if this is possible, but worth a shot. I am using a countif function to count a cells and I want it to count only if it has text, followed by a space then more text. I've been able to count the text only followed by a space, but cant figure in the more text part.
View 2 Replies
View Related
Jul 8, 2013
I am trying to figure E.g. when A1 is empty i want it to equal a text.
A1 has a text "Quote No" in it permanently until other text is put in
Purpose so that they people know where to enter the applicable text.
View 9 Replies
View Related
Jan 28, 2013
I have in Column A (quantity varies A1....An) a list of strings (i.e. file names NameX.txt and in column C I have a long string that contains many file names separated by ; that I extracted from the file name in Column A. what I want to do is to search column C individual text (file names) against the file names in column A. If the file exist in column A, then color only that text (file name) in column C. and so on.
See attached of an example of expected results.
Diery_exc_test.txt
ada_long_xyz_0a1;never_check_hello_2a1;testing_test_te_0a1;name_whatever_done_2a1
tesing1_abc_testy_1a2.txt
filename_xyz_test_1b2;diery_exc_test;Diery_hello_testy_1a2;abc_def_gah_2A1
[Code] ............
View 2 Replies
View Related
May 16, 2013
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
I have over 600 rows that needs this done.
1,2,3,4
B
[Code]....
View 3 Replies
View Related
Jul 19, 2014
I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.
Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.
It is non-numeric text, and i've pasted --> values to remove formula issues.
View 1 Replies
View Related
Mar 28, 2014
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
See attachment : Example for forum.xlsx
View 3 Replies
View Related
Feb 28, 2013
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies
View Related
Sep 2, 2013
I am trying to auto-populate text in cells in area A, based on data I enter into other cells in area B. I want the area A cells to be for display only, as all editing will be done in area B. The problem is: how do I do this such that the text I write does not get cut off if is longer than the column width? See the attached document for a clear example and description of what I am trying to do...
Excel Q.xlsx
View 7 Replies
View Related
