Updating By Adding New Rows
Mar 1, 2008
i have rows of data in an accounting worksheet (payments in/out) which needs constant updating by adding new rows. within the row a formula exists in column D such as =SUM(E11:J11) and the next row is =SUM(E12:J12). if i select row 12, right click and select insert, a new blank row 12 appears but without formula in column D. is it possible to insert row with retained formula? i would prefer to add a row from a blank row below current row and have new row appear with above rows formula but this is not vital.
View 12 Replies
ADVERTISEMENT
Mar 27, 2013
I'm trying to find a way of filling in a "form" on sheet1 and for that information to populate into the next empty row on sheet2. This would then give me a table of information in sheet2 generated from multiple entries into sheet1.
View 5 Replies
View Related
Feb 7, 2014
I have a spread sheet which has a range as shown below.
Batch NumberItem 1Item 2Item 3
P155635
P254
P378
P2568
P454
P389
P385
I need to get the output as shown below which is for Batch number P2 I need to include Item 1 (568) in one row and delete the other P2 row. similarly I need to do for P3. The end result will be as shown below.
Batch NumberItem 1Item 2Item 3
P155635
P256854
P3898578
P454
View 8 Replies
View Related
May 28, 2013
I have the following formula that picks up the cost of the item with a certain number and the description includes the word post, however I now have a scenario whereby under a certain number there are several items that include the word post and the formula is only picking up the first one and not the sum of them all.
Formula:
{=IF(ISERROR(INDEX(Sheet1!$E$3:$E$706,MATCH("*"&$C$1&"*"&A3,Sheet1!$C$3:$C$711&Sheet1!$F$3:$F$707,0))),0,(INDEX(Sheet1!$E$3:$E$706,MATCH("*"&$C$1&"*"&A3,Sheet1!$C$3:$C$711&Sheet1!$F$3:$F$707,0))))}
Data:
Description
ID
NAME
Qty
Unit $ exc
Number
[Code] .....
Result:
Job Nos
Product Description
Post
24741
IN Wave
124.3
Is there anyway to pick up all the information?
View 5 Replies
View Related
Apr 3, 2014
I have a form that has three fields (1. Comments (TEXT), 2. Legacy_Comments (TEXT), 3, Comment date (DATE))
Now my users need to keep adding comments to the comment text box, and when they do it automatically adds the date they entered the comment in the Comment date box. Now my problem is that since they keep adding comments to the comment box, I need to keep track of these comments in the Legacy_Comments (Text box).
For example, the First time a user enters a comment into the (1) comment text box it auto populates the date in the comment date box, and then adds the comment and date to the Legacy_Comment box. the end result is (comment,4/3/2014 now lets say a user needs to add a comment to the comments box tomorrow - I want the legacy_Comment box to then read (comment, 4/3/2014; comment2, 4/4/2014, ...., comment(n),date(n)) OR it can be vice-verse, because I just need to keep track of the comments, I am not worried if the new comments are before or after older (yesterdays / the day before yesterdays comments)
How can I write a VBA code that will always add the new comment to the legacy_comment field, without deleting the comments that were entered previously?
Code:
If isnull(me.comment.value) Then
Exit Sub
ElseIf me.comment.value = true Then
me.comment_date.value = date
me.legacy_comment.value = me.comment.value & "," & me.comment_date.value & ";"
me.legacy_comment.value = me.legacy_comment.value & "," & me.comment_date.value & ";"
It adds the comment only the first time, but it does not concatenate the string from yesterday to the string to today. I do not care which order the comments are, meaning if I added a comment today it can be before OR after the comment from yesterday.
View 1 Replies
View Related
Jan 29, 2009
I need some help fomulating a VSB code which would add two rows after every one row.
View 7 Replies
View Related
Apr 18, 2008
We have workook with several worksheets (tabs). The main page has several headings (category), including "Doors and Ceilings", "Walls" etc. There are currently three rows under each heading. When I go to a category (tab) there is a list of issues in column b. I can go to column A of each issue and if I place an "a" that issue will be copied to the main page under that category. The problem is that i only have 3 rows and I need a row added each time an issue is transferred to the cell before it.
Is there some VBA I can use to detect the precence of data anbd add a row?
View 9 Replies
View Related
Aug 7, 2009
Is there any way to automatically add two new rows after every existing row containing data?
For example
1 A B C
2 D E F
3 G H I
turn into
1 A B C
(empty row)
(empty row)
2 D E F
(empty row)
(empty row)
3 G H I
View 9 Replies
View Related
Mar 14, 2013
I have got a spread sheet (Attached) that works out the rankings of youth football teams so that they can be given fixtures against teams of the same ability. I want to use the same system for another age group which has nearly 100 teams in it but I can't get it to work for over 50 teams.
With my basic Excel knowledge I have worked out how to add extra columns with the correct formula, but I can't work out how to add rows with the correct formula to the table that is in cells A12 -C12 down.
Ideally I would also like this table to show the team with the highest ranking in position 1 & in descending order from there - but I would be more than happy just to add the extra rows for now.
View 5 Replies
View Related
Oct 7, 2008
I've got an excel sheet which has names in column A followed by 5 numerical values in columns B-F. I'd like to get a macro that will compare the values in Column A and if they match, add the B values, the C values, the D values, the E values, and the F values and delete one row - effectively combining the row. What's the best way to go about this? Also the sheet is already in ascending alphabetical order by Column A, so the rows that need adding will be next to each other. So I guess compare each A value with the one below it and if they match add the rows, but how?
View 5 Replies
View Related
Jul 10, 2006
I want to be able to have rows/cells generated with a certain text
based on the answer given by a user.
For example, My question in a cell could be "how many groups have you made?".
Now to this if the user answers "3" then I would want three cells below to
ask "what is the name of your first group", "what is the name of your second
group" and so on.
View 11 Replies
View Related
Jan 3, 2014
I have a spreadsheet where I will input details for "incidents" under different headings on one row per entry (I will have a separate tab for each "incident", and each row will relate to each customer affected by this incident)
For every row that is completed I have a "Summary", which is just a section of the same worksheet, that will list all the basic details of the above entries in a format that will be printed and passed to a different department.
As I will be unable to determine whether a particular incident will have 5 entries or 50, I need a way to add a new row to my "Summary" section if and when a row is completed.
I have a formula to populate the data in the "Summary" section automatically, so all I need now is for it to add new rows as and when they are needed, copying the formulas from the original row, so each new "incident" entry is copied each time.
I have created a dummy spreadsheet but I'm not sure how to attach it to this post.
View 5 Replies
View Related
Feb 15, 2007
Is any way to add automatically comments in VBA
Some combination of "ctrl" with something else.
View 3 Replies
View Related
Apr 25, 2014
I have a quotation that I am working on that I need to add 12.5% onto each seperate line price.
There are 300 individual lines on the quote all with different pricing but they all need 12.5% adding on so I am hoping there is a simple way to do a bulk sum for all of the rows rather than having to go through each row and creating a sum for each. - ie =(K28*1.125)
View 3 Replies
View Related
Dec 16, 2009
I'm creating a spreadsheet to generate a standardized form that I use as a paralegal.
I have 2 things that I don't know how to create....
1 - I want to have y/n questions on the data entry tab. When boxes are clicked Y, it adds a row and language to the form which exists on another tab. I know how to do links and to do if then statements, but I don't know how to make it not take up space when the answer is n. Some of these clauses are pretty long so I don't want to have big blank spaces when the answer is no.
2 - I have certain sections where I need to control the number of rows. I want to have a cell on the data entry tab where I put in the number of rows that I want. The spread sheet would create those rows and I would then enter the data on the data entry tab. On the form tab, the spreadsheet would refer to the cell telling it the number of rows, add those rows, and link them to the data that I entered on the data entry tab.
View 9 Replies
View Related
Jun 13, 2013
I have attached a file for reference. I have another file I use that is similar to the one attached, however it has 500+ rows and is still growing. The issue I am having is that in this file I want to make a "Template" worksheet. Basically I need 50ish sheets that are more or less the same. I have a column that describes a item, a items weight, a row with headers for the columns that I want to appear identical on every worksheet. I want the variables to be the count. These will change from sheet to sheet with formulas to than update the totals.
My issue I am having is that I constantly find myself needing to insert new rows for new items. In my real worksheet I have rows grouped together in a logical way so the rows cannot just always be placed in the next available row at the end of a sheet. How I can have sheet 1 be my "template" and the others take their item lists and weights from sheet 1. In the example file I created sheet 1. I than created sheet 2 and selected a cell entered "=" and selected the cell that I want it to have the same info from on sheet one. I fill handled these items down to generate a list. I than held down control and dragged a couple of new sheets. This process works, and is what I am currently doing, but what do I do when I wish to add a new rob between row 4 & 5?
View 2 Replies
View Related
Jul 25, 2014
I am able to autofilter a sheet to show only rows with a date in Column U but no "x" in Column BC. Now I want to be able to put an "x" in BC in only the filtered rows. The purpose is if someone enters a date in Column U, the macro will show only those rows minus the rows that have already had an "x" added to BC.
For the most part I've gotten this to work using but it will also put an "x" in Column BC, row 3001 on down.
How can I get it to stop at the last row?
Cells.AutoFilter Field:=21, Criteria1:="<>"
Cells.AutoFilter Field:=55, Criteria1:="="
Columns(55).SpecialCells(xlCellTypeVisible).Value = "X"
View 5 Replies
View Related
Nov 3, 2008
I am extending rows on a salary schedule. As you can see below, the rows include: Bargaining Unit (GP) Schedule(2A) Range(5) Step (A) Hourly (S) and the hourly wage(11.52)
GP,2A,5,A,H,11.52
GP,2A,5,B,H,11.82
GP,2A,5,C,H,12.17
GP,2A,5,D,H,12.5
GP,2A,5,E,H,12.88
GP,2A,5,F,H,13.22
GP,2A,5,G,H,13.61
GP,2A,5,J,H,14.18
GP,2A,5,K,H,14.71
GP,2A,5,L,H,15.26
GP,2A,5,M,H,15.83
The Steps currently go from A thru M but I need to add N thru Z and after step M there is a 3.75% increase for each step.
You can see that this is a csv file (comma delimited). All of the other info needs to autofill and only the new steps (N-Z) will change and the wage for each of the new steps. The last row is 2 decimal points.
View 8 Replies
View Related
Mar 20, 2009
This is what I have:
A-------------B
gfdfd------sjfsdnjsnd
gfdfd------sjfsdnjsnd
This is what I need:
A-------------B
gfdfd------sjfsdnjsnd
....
I just need a black row between every row that I have now. I'm sure its simple but I can't seem to figure it out. I have tried a macro but don't really know how to work them, it just made a new line in the same spot.
View 2 Replies
View Related
Sep 24, 2009
My table is a much larger version of this:
A B
X1283.786
Y1684.379
Z1719.013
X10745.013
Y10860.668
i want excel to work out the total for x y and z. however i have about 100 different things x y and z could be and its a long list so an automatic way would be great.
View 3 Replies
View Related
Feb 23, 2010
I´ve made a simple macro in order to make easier to add new rows in a table, by filling the cells with the new data.
The problem is that according to one of the fields, I´d like that macro to add multiple rows, with exactly the same information but numbering all these rows on this specific field from 1to the number I filled on the respective cell.
For example, I have beyond another fields that I´ve already filled, the field "Parcel" and "Total of Parcels". I filled parcel with number 1 and total of parcels with 5. Then I´d like the macro to add 5 rows with the "total of parcels" cells containing 5 on every row and the field parcel filled with number 1, 2, 3, 4 and 5, for each row created.
View 14 Replies
View Related
Nov 28, 2011
I created a Validation list from 2 to 20. I want to have it (the list) add a row every time you select the proper data.
Example: If I want to add 2 row I would select the 2 in the drop down menu. If I need 5 rows I need to select 5 from the drop down menu.
Is this a VBA code? Or can I use a formula?
View 5 Replies
View Related
May 1, 2012
In columm (A) I have a row for each day of the year, that is, 1/1, 1/2.....12/31 which has a corresponding value in column (B). In column (K) I have a row for each week of the year, that is, 1, 2....52.
In column (L), what formula can I use so that each row will sum the values of a seven day period from column (B)?
View 3 Replies
View Related
May 30, 2012
I have a monthly log that I need to keep to track expenses and customers visited. The log is segmented into 12 parts corresponding with each month.
I need to be able to add multiple lines via an input box while maintaining the formulas and formatting of the 2 rows directly above the forms command button (because both lines are filled with a different color) but not the contents?
There will be 12 command buttons in all and the new rows should be added at the bottom (directly above where the button is)
I found this and it works to some extent.
Sub FromFormsCommandBar2()
Dim Btn As Button
with ActiveSheet
Set Btn = .Buttons(application.caller)
btn.topleftcell.entirerow.insert
end with
End Sub
View 2 Replies
View Related
Aug 16, 2012
Is it possible to add a row to the pivot table. All my fields run across the top of my excel sheet but i need a calculated field to show under the row under the total of each group. The values in this row must equal the value total in one of my fields. Is this possible ?
View 1 Replies
View Related
Jun 19, 2013
I have a data set of 300 points and i want to increase my data range by a factor of 10 .
For example if i have Column A with master points A1=10 A2 = 20 A3 = 30 .
I would like to be able to automatically enter in the same about of new rows between each point . Then calculate there difference between the master points and populate the new rows in sequence . For example The difference between A1 and A2 = 10. So the new A2 would be 11
I would like to have A1=10 A2=11 A3= 12 A3=14 Etc as the new cells.
View 1 Replies
View Related
Jul 9, 2013
I have a spreadsheet with multiple similar entries I would like to add up. What would work well for me is to be able to input a column like a and a target column like e.
So in this case it would search throughout column a and wherever it finds a duplicate entry add column e and then delete one of the rows. I could then run it again under different column criteria if needed.
So if I had the below sheet:
I could search by column a and add up column h to read like this:
View 1 Replies
View Related
Mar 31, 2009
Due DateInvDocument numberDebitCreditBalance
17/09/08L01607610714211201825.44
13/10/08L01637010284345841020
13/10/08L01637131713874501020
13/10/08L016373852202473461020
13/10/08L0163748.5222E+111020
15/10/08L01641811103746832316
The above is a part of a w/sheet. Rather than determining the last row in the debit column and credit column (to know which is greater) and use a loop to put a formula in each of the cells in the balance column (adding the debit and credit), I know I can use the with ... statement to input the formula at a go , but cannot figure it out.
Sub sumtotal()
With Sheets(1)
With .Range("G14", Range("G" & Rows.Count).End(xlUp))
.Clear
.Formula = "=sum(E14+F14)"
On Error Resume Next
End With
End With
End Sub
View 9 Replies
View Related
Dec 7, 2006
If I have a sum function that adds up:
= SUM(U41:U45)
but I add a row at 41 I now get sum function
=SUM(S42:S46) when I really want it to incorporate the just add row to look like this:
=SUM(U41:U46)
View 3 Replies
View Related
Jan 29, 2007
I want to restrict the adding the rows and columns and also deletion of rows and columns in excel template, the same template is circulated to all the program owners to capture there project metrics. With that template through macro values are extracting to consolidated report. Some times program owners are adding extra columns/rows with that we are getting errors/incorrect reports.
View 14 Replies
View Related
