If I have a worksheet and it has 100 rows, I would like for column D to auto-populate with a predetermined formula all the way down to row 100, not row 101.
View 3 RepliesWhat I would like to do is on a sheet when I insert a new row that it will "FILL" the formulas that are the row above it. For example I have cells A1-F1. On cell A1 there is 1, B1 there is 2...etc. When I then insert a new row I would like the row below A1-F1 to read. A2 = 2, B2=3 so it had a linear growth. I want to do this with my formulas so whenever someone adds a new line it knows to copy the formula as well but only in certain cells if possible.
View 9 Replies View RelatedI have a formula in column G of a worksheet, however when a new row is inserted within the formula range, the formula does not continue in the new row.
Is it possible to make excel continue the formula without having to drag the formula down again?
When I insert a row in my table (below the headers, which are between rows 1 and 4), I want the formulas from the above row (or below row) to be copied down to my newly inserted row. I say "below row" as well in case I want to insert a new first row and want the formulas in the row underneath to be copied up.
e.g., formulas currently in the first row are:
Column A: =SUBTOTAL(3, $B$5:B5)
Column H: =IF((F5="N*"),(1),"")
Column I: =IF(((SUM(H$4:$H5))=(SUM(H3:$H$4))),"",(SUM(H$4:$H5)))
Column J: =I5
Any other values in the other columns should not be copied to the new row.
I also do not want my table to be scrambled if I should delete any particular row.
I've a spreadsheet with each row representing a record of some equipment installation escept for Row #1, which contains the column headers. For each record, Columns A through G contain the installation information,Columns H onwards are for monthly billing purposes(March 07, April 07, etc.). Each cell in Column H onwards contains a complicated formula for billing. Under the row that represents the last installation, there is a Totals Row. Since we are adding installations very frequently at the moment, my goal is to: Using a Worksheet_Change Event,
When a row is inserted into the worksheet, the code tests for
1) If the event is an "Insertion"-if not, Exit the code.
2) If the Target is a row- if not, Exit the code.
3) The row number-if row #2, copy the formulas from the row below.
If the row # is less than 2, Exit the code.
4) If the Target Range's row number is > 2, copy the formulas from row above.
5) If the Target Range's row number is => than that of the "Totals", Exit the code.
I've got 2,3 and 4-
How can I test for #1, and for #5, every time a row is inserted, the row # increases. How can I code #5?
I want the 2 graphs in "Graph" worksheet to change automatically when a row is inserted in "Data" worksheet . Every time i have to change the graph manually to contain the latest 20 days value. I want some offset or something which can be put in the range provided below to do the work.
Chart Data Range =Data!$A$1:$A$22,Data!$F$1:$F$2,Data!$L$1:$L$20
Legend entries (Series) - Series Name =Data!$F$1 ( this will remain constant everyday as this is a header field)
Series Values =Data!$F$2:$F$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then it should automatically change to =Data!$F$3:$F$23)
Legend entries (Series) - Series Name =Data!$L$1 ( this will remain constant everyday as this is a header field)
Series Values =Data!$L$2:$L$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then it should automatically change to =Data!$L$3:$L$23)
Horizontal (Category) axis series -Axis Label Range =Data!$A$2:$A$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then it should automatically change to =Data!$A$3:$A$23)
Sheet attached : Devicess.xlsx
Need automate a process that I would like to implement into my spreadsheet,
It involves inserting comments,
I want to be able to select a particular cell anywhere in my spreadsheet, and then click on a command button and have a comment automatically inserted into the active cell, I tried to record the macro by hand but I do not know how to state that it is the active cell the comment should be added to
Can periodical fixed deductions/payments be inserted automatically to the individual MONTH sheets of the workbook I uploaded ? To reduce the size of the file, I have left only ONE of the twelve month-sheets - the one for January.
Rephrased :
How to automate the entry of fixed amounts at regular intervals?
My guess is that from time to time, a macro would have to be run
- one of the macro's inputs would be the computer date and the other two inputs would be the value of the entry to be made and the value of the intervening period.
In practice there would be more than one regular entry and the values would be both positive ones and negative ones.
I guess that means one macro for each financial pattern.
Feasible or feesible (a multi-million-dollar-fee task)?
i have a question with regards to the shading of a table in excel 2010.The grey/white shading of the sheet should adjust automatically when lines are inserted / deleted...what would be the most efficient way?
View 6 Replies View Relatedi am trying to create a spreadsheet that will automatically plan a days work in manufacturing based on the items the factory is making, the different items obviously have different hourly production targets and id lke to create a spread sheet that knows how many we want to create what the targets are and how long it will take. i also want it to be able to know when it has reached the needed total and automatically plot the targets for the next product, so what i need is a way to change part of the formula when the column reaches a set culmative total
View 3 Replies View RelatedRange("B25").Name = "EndMull"
Its fine but if i insert a new row or column then it mucks the whole thing up. Is there away of naming them but if any cells, row or columns are inserted the range will automatically adjust to suit1
i have a list of serial numbers in a column, say column a, for example, and i have corresponding data in columns to the right of this, say just column B for example- I also have a header on column B which counts the data in column b. (just a COUNTA, counting occurences of 'x' )
my issue is this: i need to limit the range of the COUNTA dynamically- by the number of serial numbers in column A. for example: if i have 10 serial numbers, i want my COUNTA range to be B1:B10 (excluding header) and... i need this to be a built-in formula, NOT a VBA macro. i need it to update automatically, as soon as another serial number is entered. i've tried using references to named ranges and all sorts of language tricks, and i cant seem to get it to work. (such as:
' =counta(b1:namedrange1)
i'm going to have to do the same thing with COUNTIF's, so if that is also easily explainable,
I would like to do is in cell B8, copy each row of text to a newly inserted line below. The highlighted cells in yellow have been done manually as an example. I have about 50 groupings of these to do manually. I'm making an effort to learn VBA.
View 3 Replies View RelatedI want whenever i select any dept in the B column say ID or Design... the value into the D column should change automatically...
say if i select in B5 as ID then D5 value should show me India
******** ******************** ************************************************************************>Microsoft Excel - resource_sheet_11_Dec_07_V2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDE1Project(Client - Name- Length)Dept.Resource 1-Oct2 3Template 4 5New Course Sample Template ID 6New Course Sample Template ID 7New Course Sample Template DesignDesign Pool 8New Course Sample Template BuildBuild Pool Project Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
How to automatically list down associates if workflow is mentioned in the column? Like in attached sheet, in column B workflow is listed down and in column C name of associates should get populated in sequential manner from column F, G and H. For example A, B, C and D is aligned to workflow X (Treating this as primary source), whenever in column B, workflow X is populated (manually) it should list down list of associates in order - A then B, then C, then D; once cycle completes, it should repeat again A, B, C, and D.
View 1 Replies View RelatedI have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".
V
W
X
Y
Z
AA
AB
AC
...
FB
[code]....
List of Dates Absent
x
x
x
WR
x
x
x
1/23, 1/24
[code]....
I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.
Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.
I've applied a basic multiplication formula =D12*E12 in a table Column F, also I've filled it throughout but now if new rows are inserted in between or at the end of the table, new cells in this columns doesn't include this formula.
View 1 Replies View RelatedI'm trying to create a dynamic chart title by inserting a text box in the chart title that displays the value of the last populated cell in column A. The number of rows increases over time, so I'm trying to come up with a dynamic cell reference. When I hover over the text box a tool tip appears with this text "TextBox 2" so I assume that is the name of the text box. I'm definitely open to other methods that do not use vba. It seems that none of this code can activate the text box:
Code:
Sub textbox()
Worksheets("Figure3-5").TextBoxes("TextBox 2").Range("A" & Rows.Count).End(xlUp).Value
End Sub
[Code]....
Our spreadsheet pulls values from a column when a month is inserted in a certain cell:
OCT is typed in B2 and in the column below it, from B10 down to B286, data (numbers) are pulled from the October column K10 through K286 using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed in B2 then the same happens except data is pulled from the November column, L. The monthly columns from K through V are tied to and updated
from other sheets. As you can see the problem is that nested functions allow only 7 and I need 12, one for each month. I have looked at the VLOOPUP and the HLOOKUP but our data is not set up that way (tables) since they need to be exact numbers pulled from the monthly columns that are tied to other sheets.
OCT (B2)
OCT NOV DEC
Complaint 3 (B10) 3
6
Inspection 10 (B11)
10 2
NOV 11 (etc)
11 5
Door Notice 1 and
so on
Recheck 32
Citation 2
Work Order0
Demolition 0
I am using Excel 2010 and have the problem as shown in the attached file.
Input Sheet shows the Data I have at present
Output Sheet is the desired result.
I need a macro which should create an "Output" sheet by doing the following on the Input Sheet
1)Insert a Blank Column before Column A
2)Unmerge the Region Heading and insert the respective Region Name in the newly inserted Column. Region Heading will be in Bold Font.
3)Repeat Step 2 for all Regions
4)Delete the Rows which was merged.
Please note that the number of Data Rows will vary for each Region.
I have shown two Regions for explanation purpose only. There will be several Regions in reality.
The result is shown on the Output sheet
Merge Problem - Forum.xlsx‎
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedI have a dynamic range that is 19 columns wide with the height defined by the last filled cell in Column B.
Columns A, O, P, Q, and R contain formulas that need to be autofilled every time a new record is added to the list (i.e. a new row is added to the range) - this seems like it should be so simple, but I can't seem to find out how to do it?
Some extra details in case they are needed -
- Column B will always contain data for every row that I need A, O, P, Q and R to have their respective formulas copied into
- I'd like the formulas to appear as soon as the data goes into Column B - regardless of what else is entered into the row
- Multiple identical records will sometimes be entered at the same time (i.e. by highlighting multiple rows and using Ctrl-Enter) - I need the formulas to autofill all of the affected rows.
Right now I have a spreadsheet where I have a person enter in 4 differant values in 4 adjacent cells of a row, then in another 4 adjacent cells of the same row calculations are done with respect to the 4 rows of entered information then displayed. Is there a more effective way in placing and copying these formulas in the last 4 adjacent cells rather then just copying the formula into the first 3000 or so rows of cells?
I say 3000 because each project is differant and will require a differant amount of rows, but by copying the formula, even little IF statements, slows down the program and makes printing a hassle.
When I enter a formula into a cell, for instance; (d27*d28/144)*d23, and then enter a number into cell d23, the formula gets replaced by the value of the result automatically.
I've searched the help files and found no setting that does this. It reminds me of an old question I've had lingering since I've used excel and that is why sometimes when you enter a math calculation, for intance; =25/5, the number 5 is entered into the cell instead of the formula.
After I posted the above, the problem magically went away until.....
This is boggling my mind. The problem is back.
The wierd thing is I copy a known good formula to one of these cells, for a split second you can see the formula in the formula window and then it gets changed automatically to the result of the formula. Formula is gone!
Then I tried this:
I set security to high (I had it on Low) and of course my macros would not run, but this did not clear up the problem. However when I set security back to low, it now seems to be working good. I am scepticle (sp.?) though because the problem is very random.
Could I have a virus?
I have 2 WorkBook, Division.xls and Department.xls. The Sheet1 in the Department is linked & getting the values from Sheet1 in the Division & works fine. But if there is a change in Division workbook, its not affecting here immediately. When i close the Department.xls and open again, i can find the latest values. I thought the links to the other sheets or workbooks will be updated when i press the save button. So I used the SendKeys "^S" in the VBA. But it fails. Is there any way to mention through VBA code to update the recent values to the Department workbook from Division.
View 7 Replies View RelatedI'm using a heavy excel file and I have linked some cells in one sheet to other worksheets in the same file. My problem is that when I change the value in some cells they do not change in the other cells linked to the ones I have modified.
View 7 Replies View RelatedUsing Microsoft Excel 2007 and all of a sudden, my spreadsheets are not automatically calculating the formulas. It does not matter if I have other workbooks open or not. I still get the problem. It does not happen 100% of the time to make it even more complicated.
- Calulation set to auto in Excel Options.
- No VBA functions being used. I can the worksheet summing 1 + 1 and get the error periodically. It does not have to do with the spreadsheet being too complicated.
- Even if I can hit Ctrl-Alt-F9 to force the formulas to calculate, it won't work.
I am copy/pasting a new column of data (F). I have three formulas MIN,AVE,MAX watching each row in the columns "=MIN(B2:E2)" or example. When I insert the new column F, the formulas give me the "Formula Omits Adjacent Cell" warning. I don't want to turn off the warning; I want to know if there is a way for the formulas to automatically include the new data? Do I need a Worksheet Event for this?
View 3 Replies View RelatedThis may be an easy answer, but I haven't been able to figure it out. I have a cumulative page of formulas that reads "SHEET1!B8+SHEET!B9". I need the next row formula to reference "SHEET1!B47+SHEET1!B48", and then "SHEET1!B86+SHEET1!B87". Each row the formula moves 39 rows on sheet 1. Is there a way to get excel to automatically do this, so I don't have to go line by line?
View 9 Replies View Related