When User Inserts New Row Copy Formulas / Formatting From Row Above
Jan 29, 2014
If the user inserts a row, I want all formatting/formulas/data validation from the row above to be inherited in the newly inserted row. I want this to occur on sheet1.
When you insert a new row, although the entire new row is selected, the active cell is the cell in column A. So I figured I could base a routine off of the active cell. Here is what I tried:
[Code] .....
I am trying to reference a range. The first cell in the range is above the active cell. The second cell in the Range is above, the active cell, then to the right of the range, then down one. By referring to this Range, I am then trying to use the FillDown method. This code isn't doing anything.
Also, I need Excel to automatically run this code when the user inserts a new cell on sheet1. Would I need an event handler or just put this code inside of the Sheet1 code window?
I have cobbled together (borrowing from examples I have found online because I don't know VBA) a form which inserts a date into a spreadsheet.
The trouble I have is that this date field is in USA date format and I need UK.
If I enter 03/01/2009 into the form, it appears in the spreadsheet as 01/03/2009. I have formatted these cells to display the month only and because of the way it was entered, the next column displays MAR instead of JAN.
(1) My main tab I have a macro button that inserts a new row and also inserts a new sheet with the rows name that was inputted. But on the main tab in cell A1 that is named, I can not get it to hyperlink to the new tab.
(2) When the button is hit to insert a new row and sheet I copy over previous tabs information and then I want to change two fields A1 and B1, A1 works fine but my B1 I can not get the formula to work correctly. My cell in B1 I need it to say 'DESCRIPTION: ' and then take the value of main tab 'Test Case Summaries' cell D?, it would be the new line and copy it here. See below... rname is the new tab name and lrow is the new row that was added when the macro button is hit.
t = Sheets.Count Sheets(t).Copy after:=Sheets(t) ActiveSheet.Name = rname
I am looking for a macro that will copy the row above it, to include formatting and formulas, however not the actual contents. I need the row that's being copied to be hidden from view, which is where the problem stems from. The macro is also copying the 'Hide' formatting and I want to exclude that. This is the current macro I am using:
Lets say I have a block of cells (lets say A1:E5) that all reference the value in the upper left hand cell (A1). Could be a formula by itself or one embedded within a conditional format. I reproduced the formula and formats in the A1:E5 block by using the absolute cell reference $A$1.
I now want to reproduce that entire block (including the referenced cell A1) multiple times on the sheet, but the catch is that each new block must reference the cell in IT'S OWN upper left hand corner. (e.g. a block located at F10:J15 must use as it's reference F10, not A1).
How can I quickly reproduce those blocks? I have done a search and replace to eliminate the $ symbols within the formulas to make them relative, but that doesn't seem to work within conditional formats.
The issue right now is I am trying to apply conditional formats to a sheet I already constructed. I don't want to overight the formulas in the UL corner, so I am trying to copy and paste the conditional formatting only. Can't seem to figure out how.
How do I get some columns in sheet 2 to be a 'Live' copy of some colums in sheet 1? - 'Live' as in they include any changes to sheet 1, i.e. insertion/deletion of rows?...
I have set up a very basic shared workbook for me & four colleagues so we can all see what each others work loads are like and establish who has capacity for new work. We each have a sheet and record the work data in rows which details Name of case, Case ref no., age in days,etc... This then feeds a summary sheet displaying the totals of the relevant info and has pretty charts etc which we can print off for our manager.
As our cases are concluded we simply delete the rows. When we get new cases they are either added as new rows or inserted between existing rows (when similar work/related work). What I would like to do is give each of us a second sheet where we could record more specific detail regards each case. I would like sheet 2 columns A&B (detailing name name & Ref No.) to be the same as Columns A&B Sheet 1 - I have done this with a simple =Sheet1!A1...etc, which is great until any deletions/insertion are made to sheet 1 which then throws out sheet 2. How do I make Columns A&B for sheet 2 'live' copies of Colimns A&B Sheet 1?
I have the attached file that I need in a user form format. Basically the vlookups and calculations (on tab form) should be calculated once users enter A and B into form.
I am trying to get different people to add dates onto a spreadsheet but need a way to differentiate whos dates are whos. Is there a way to color the cells according to which user put the dates in automatically?
I need to enter a range of dates and have any date that falls within that period (regardless of year) highlight. I currently have it referncing two different years, but I need more range than that and I can't get it to highlight dates in July if they were dates that happened 1, 2, 3, etc. years ago.
I am trying to adjust a simple formula that I am using:
=IF(D3<>"",1,"") **Note this is clunky, but ISBLANK doesn't seem to work due to D3 containing a formula**
to also change the fill color of A3 to "Red" if data exists in D3. Everywhere I look online for the formula reference tries to point me to Conditional Formatting, Which doesnt seem to apply to seperate cells so I can't use this.
I'm trying to get some formatting/formulas to work, but apparently I'm not doing it right (otherwise, I wouldn't be here ):
1. Column A has dates. I want Column A_ to be red if the date is a week old or more from today's date, and white if it is not.
2. Column A has dates, and column B has dollar values. I want three four summations: -Total dollar value from dates a week old from today's date -Total dollar value from dates 2 weeks old from today's dates -Total dollar value a month old from today's date -Total dollar value year to date
Following is an example of an HLOOKUP formula I'm using:
=HLOOKUP(A1,$B$2:$E$5,4,FALSE)
The problem I'm having is that if a user inserts a row within the range B2:E5, then it throws my lookup formula out, because the formula should now be looking at row 5 and not row 4. How do I get the number 4 in the formula to be a variable that always selects the last row of the specified (variable) range?
i'm using a macro in excel 2007 that will open a Microsoft Project file, and go to the filtering menu. This is working fine however i have come to a deadend as i need the answer from a Data validated menu to be inserted into a box that project creates. Basically i need to copy the text from my excel (worksheet 2, cell c2), into my vba formula so that it automatically inserts into the MS project box. I hope this all makes sense and would be massively gratefull if you could point me in the right direction.
I have a cell which team members enter their team name in to. For example FR1 and RA5. I set up data validation to only allow four characters so they can enter RA10 FR15 etc. However I have now found some are entering FR 1 with a space between the team name and number. This then means my formulas further on are not picking up the cell correctly. How can I have the space between the FR and 1 removed automatically upon user input?
I need to apply conditional formatting to cells in a column that contain formulas, or more accurately cells that do not. I have a spreadsheet with default values in a column based on a lookup function. I need to know if the user overwrites the default and cannot use UDF's or VBA. I don't think it's possible so my alternative is to include a hidden column that does the same lookup and do the conditional format off of a comparison.
I am working on a macro to insert a row below if a cell with a validation list has an item from the list selected. The new row needs to maintain the same format and formulas as the original row. To explain this better I have attached a very generic spreadsheet Called Custom Order. In this example cell A3 has a validation list. If the user selects one of the items in the list... I need the macro to insert a row below and maintain the format and formulas found in row A3.
Also this is posted on another forum. http://www.excelforum.com/showthread...=1#post1893257.........
i am trying to do something along the lines of. a For Next Loop that selects the cell N6 and inserts the formula.
$K$7*F6+$L$7 then fill down to the cell N754 then selects O6 and inserts the formula $K$8*F6+$L$8 then fill down to cell O754. repeating this until there are no more values in either the K or L column. in the attatched spreadsheet i have deleted the values from 50 something down to 754 but the idea is still the same.
so that each formula inserted keeps the cell from column K and L constant when filled down but F changes, then when the next column is used(ie. from column N to O), the next row in the K and L is used but F starts from F6 and changes as it is filled down again.
In my workbook I have several column with dates, these are benchmarks that I need to follow and have my patient's follow. They concern the dates of treatments and of lab work. My last column is Contact Needed and does not contain any formulas or code. Is there a way to change the color of the row to adjust for how close we are to the treatment date or the lab draw date.
I was thinking if I were +/- 7 days from each blood draw the row would be yellow, so I could contact the patient and remind them to get the labs done. If it was over 7 days past scheduled blood draw, the row would be light red, as the urgency to get labs and continue treatment has increased as they are past due. In like manner, if treatment are within 14 days, the row would be yellow so i could call them and set the appointment. If past the treatment date, the row goes red and I have 14 days to get them in or we have to start treatment s all over again.
Can you have IF and AND statements in Conditional formatting formulas?
I put this and I get an error: =IF(AND($G5=""($H5=<>"")) I was testing the water for adding and OR statment also. I really want if cell G5 is blank and cell H5 or I5 has text, then G5 should be red.
In the attached spreadsheet you can see I have a chart with three columns. In the third column, I've inserted a conditional formatting formula so that any value between 90% and 94% will be highlighted in orange. What I'd like to do, based on the results in the third column which I've already formatted, is have the other two columns in the same rows highlighted in orange as well. For instance, you see that the 92.30% is highlighted in the third column because it is a value between 90% and 94% per my formula. When that is true, I would also like the 56.02% and the $4,100 (the two cells to its left) highlighted in the same color. I'm not sure how to accomplish this.
is it possible, after inserting new sheet rows, to automate the copying of formulas and cell formatting into the newly created space, instead of manually copying the formulas and formatting down into each column?
I'm currently trying to set up a spreadsheet on excel, when certain documents need updating. Basically I have a column with the dates in that each document was last updated, I want the cells to turn red (fill colour) if the document has not been updated within the last 6 months and orange (fill colour) if the document has not been updated in the last 4 months. How do I do this?
I am currently using Excel 2002 and am on a Windows XP computer.
I tried searching for code for each part of the task separately and trying to piece together multiple macros, that did something similar, but I’m not have a lot of luck, so I’ll break it down into two treads, I hope this will follow the rule of one question per post, but if I should have separated this request even farther as two treads, please let me know and I’ll be happy to comply.
I was given a spread sheet with a macro that loads data from a SQL Data base (not mine), it returns anywhere from one row to 100’s of rows data. Since it’s not my spread sheet and macro, I copied this sheet to my destination workbook. The Data loads in 7 columns starting in B4:H4, with the column headings titles: Acct#, Id#, Name, Qtr1 Totals, Qtr2 Totals, Qtr3 Totals, and Qtr4 Totals. Data cells in spreadsheet not formatted (general)
What I’m trying to do: part 1: I need a macro to copy the data only for columns Id# thru Qtr4 Totals (C5:H5 down thru the last row with data) to another sheet. The destination sheet range starts with rows B13:G13, with formulas below and to the right in columns H13, and J13:N13 (column I is blank).
The formulas below the range are a subtotal of the copied columns (D:G). They could be remove and totaled after they’re copied, so there's nothing below the copied range? Since I need the column totals as a subtotals, my thought was to have the row heading and formulas already on the destinations sheet and just insert a blank rows between as each row was copied?
part 2: As each row of data is copied from the source sheet, I need the macro to copy the formulas to the right of the copied range (H13, and J13:N13) on the destination sheet down for each new row of data copied from the source sheet
#17. Create a macro named "AddSale" that perform the following tasks: -Switches to the Sales History worksheet, and then inserts a blank record in the third row of the Sales History worksheet, shift the rest of the records down.
i did create the Addsale to the macro i use Tools-Macro-record a new macro (is that right?) and i switch to the sales history worksheet insert a blank row. (am i right?) it state inserts a black record, i can't find record anywhere so i assume its blank row.
I am trying to make a tally sheet to keep track of my inventory of inserts. I am trying to make it as user friendly as possible as my operators do not have much experience working with computers. I will attach what I have made so far. The only math functions so far are: C4=B15-C15.
Right now the operator has to look at C4 to see current total, type that number into B15, then in C15 type the number of inserts thrown out to show a new current inventory total in C4. Is there a way I can set this up so that all an operator has to do is type in only the number of inserts thrown out in C15 to give current total in C4. Is there a way to make B15 know what is in C4 without the operator having to type it in. If so is there also a way to make C15 the only cell that can be edited.
