Deleting Cell Values But Not Formulas?
Aug 24, 2013
I work for a bank and we use an excel spreadsheet that has an in depth payment calculator used for creating monthly payment arrangements or calculating settlements on bank loans. I would like to add the following feature and I'm not quite sure exactly how to do it.
For example, imagine cell A1 will contain the full payoff of a loan. I want to be able to put a settlement offer in B1 and have C1 populate the percentage of the payoff that is. At the same time, I want to be able to delete the settlement offer in B1 and put a percentage in C1 and have it calculate that settlement offer in B1. The calculations/formulas to do these problems is simple, but what I need is to not lose the formulas in B1 and C1 if I were to go back and forth entering settlement offers vs settlement percentages.
View 2 Replies
ADVERTISEMENT
Nov 28, 2013
I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag
Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.
And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.
View 1 Replies
View Related
Sep 4, 2006
I have a macro that imports a text file. I then have summary sheets that reference to the sheets with the imported text file details. This is a task I complete every month.
The next month I delete the sheets with the imported text file details before importing current month's details. But when I do that it screws up my references.
Here's an example of a reference with previous months' imported text file details:
= SUM('X'!$I$1:$I$3000)/2+SUM('Y'!$I$1:$I$3000)/2
When I delete the sheets with the imported text file details and import current month's details, my reference suddenly looks like this:
=SUM(#REF!$I$1:$I$3000)/2+SUM(#REF!$I$1:$I$3000)/2
What can I do to prevent the summary sheet from messing up my original references?
View 3 Replies
View Related
Dec 17, 2006
how do you store formulas so that they stay in the cells when all info is deleted? so i can use the same spreadsheet every week but all i have to do is select all and delete. if i do it now i lose al my formuals.
View 3 Replies
View Related
Apr 9, 2009
I have a large amount of data on a sheet which is being looked at by the following formula
{=AVERAGE(IF(LEFT(RAW_DATA!$C$4:$C$10000,LEN(Dashboard!$I$10))=Dashboard!$I$10,IF(RAW_DATA!$A$4:$A$10000=$A2,RAW_DATA!$J $4:$J$10000)))}
However, i don't need to keep the oldest data so I want to write a macro that deletes some of the top rows. Doing this in the normal way causes the formula above to change. I.e deleting one row cause the range to become $J$4:$J$9999 and so on.
Is there any way of deleting a row without affecting the formulas that are looking at it?
View 9 Replies
View Related
Sep 6, 2007
With a macro i want to delete some lines in sheet1. In sheet2 however i have formulas that point on sheet1 .. like this
=sheet1!A1
=sheet1!A2
=sheet1!A3
Now when i delete line 2, the result would look like this
=sheet1!A1
#NV!
=sheet1!A2
Is there a way to make these formulas pointing at the same cells after line two is deleted? And i do not mean a work around, with different formulas. I just want the Formulas in sheet2 to remain unchanged, regardless of what happens in sheet1...
View 9 Replies
View Related
Mar 26, 2014
I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.
I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:
1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table
The code I'm using for my "Add" button is:
[Code].....
The code I'm using for my "Delete" button is:
[Code] .....
The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above).
Attached File : Productivity Report (HH).xlsm
View 8 Replies
View Related
Oct 25, 2006
I currently maintain a program that creates an Excel report. The report is full of formulas (mostly vlookups).
After the report is built, I'd like to get rid of the formulas. The only way I know to do this is to copy the report and do a paste special values.
View 3 Replies
View Related
Jan 25, 2013
I have inherited a spreadsheet with over 800 rows (and daily expanding) and 14 columns of data. I've attached a sheet that looks similar, with only 200 rows for reference. Here are my questions:
1. How can I delete all rows that contain no values?
2. I want to fill an entire row of data yellow if the values in the final two cells (L&M) in the row are equal, and red if they aren't. How can I do that?
3. Column A contains only dates, from oldest to newest. I'd like an obvious visual clue for when the months change. Currently it's a long, merged, blue-filled cell that says "March 2011", for instance. It can't be color because all cells need to be filled based on certain criteria (see #2), and borders aren't obvious enough.
4. I want to click on the row number on the very right, but have it select only columns A-M, is that possible?
I'm also having trouble with autofill. It seems to only work on parts of the spreadsheet. I don't know what settings may have been changed, but I do have autocomplete turned on, and no clue what to do despite hours of googling.
Test Tracking Sheet.xlsx
View 4 Replies
View Related
Aug 20, 2014
I have below code which pin out numbers from IBAN in Column P. Its formulas run by macro. It works fine. But what i really need is that instead of putting the formula in the cell, is that it compare the value from the formula, with the excisting value , in each cell . And it mark the cells where there are a difference with yellow colour I have been thinking how to do this but cant really work it out. If some have a better solution its ok but i need a macro for it.
have a look at below code, which change the cell and correct the error, but which i want just to compare the values.
I have attached a sheet to test with.
[Code] .....
Attached File : Testforcompare.xls
View 12 Replies
View Related
May 13, 2008
I have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
View 4 Replies
View Related
Dec 12, 2013
Whenever you insert / delete row or cut cells out in Mon/Tue sheets it's causing issues in the table in the Weekly view tab.
Is there any way to prevent it? I don't mind redoing whole spreadsheet, it was done ages ago in a very fast manner.
I gave you an idea by deleting a row range in Mon tab which causes #REF error.
View 9 Replies
View Related
Feb 21, 2014
I've attached a sample document of my data layout. On the Compare tab, I'd like to build a sumif formula that takes the data in column B and uses it to reference the other tabs without having to use a large nested IF statement. For example, on the Compare tab, in cell C2, the formula will know to look at the "JAN MONTH" tab because B2 says "JAN", then the rest of the sumif would be looking at the data in column A and matching it with the data in column A in the correct tab.
View 1 Replies
View Related
Nov 3, 2009
I am trying to make a button that will re-initialize the workbook: clear (delete) all unlocked cell's values. I have the following
View 5 Replies
View Related
Dec 3, 2013
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
View 4 Replies
View Related
Apr 8, 2009
I'm running into an issue when I try to generate a report for the largest five values in a report. Some of the cells have a zero divisor in their formula so they come up with the #DIV/0 error. I've put in an If statement to make those equal 0, but the LARGE formula still returns #NAME?. Any ideas on how to get the LARGE formula to work.
I would like to be able to delete the rows with zero in the reference column, so their data is not even in the report. Does anyone know how to do this in VBA?
View 9 Replies
View Related
Aug 22, 2006
My excel looks like this. The rows have duplicate names which I want to delete:
TimeNamesNamesNamesNamesNamesNames
5 hoursBobTimTonyBobTom
7 HoursLisaPamPamHeatherKimKim
6 HoursLisaTimBobTimTim
I want the excel to look like this:
TimeNamesNamesNamesNames
5 hoursBobTimTonyTom
7 HoursLisaPamHeatherKim
6 HoursLisaTimBob
View 6 Replies
View Related
Jun 29, 2007
I know this is an easy question....what syntax would you use if you want a macro to search Column A for every cell with specific text, and then clear that cell? I can't seem to get the syntax right to get it to do what I want.
View 2 Replies
View Related
Sep 11, 2009
The following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
View 2 Replies
View Related
Jun 9, 2009
What I would like to do is to delete the rows in the attached Excel file highlighted in yellow (whole numbers) and to keep the rows with percentage values. Ideally I would like to have only the rows with the string "Group:" remaining along with the rows that have percentage values in them.
What I'm going to be doing is then transferring these groupings of data to another excel sheet in order to create a time series of performance by "team leads". If anybody would be willing to help me out with the first part of this I would really appreciate it. I have some idea of how to go about it using conditional logic, but I'm just not comfortable enough in VBA to do this efficiently. It would probably take me hours just to get a simple version working....
View 6 Replies
View Related
Nov 13, 2012
I've tried to write a VBA code that copies a value down and deletes empty rows. The macro needs to copy down the value in column A from the above row if the current column is empty but delete a row if column B is empty. The following code will copy down the value but will trigger a 400 error and won't delete any rows. The desired effect is to have column A completely filled and the empty row (Row 5) and the row containing "Page 1" (Row 6) deleted.
Code:
Public Sub fillType()
Dim LastRow As Integer
Dim Count As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For Count = 0 To LastRow - 1
'If A column is empty, copy value from column above
[code].....
View 2 Replies
View Related
Nov 13, 2006
I am trying to write a macro which will delete all rows where cells in column B are blank. I am new to macros and since I have only been able to record them, I am having a tough time hand-writing this one.
View 9 Replies
View Related
Jun 6, 2009
The below code is supposed to delete data on the last sheet of the workbook HeadsetOutTimeMay.xls however for some reason it is deleting the data on HeadsetOutTime.xls What am I doing wrong?
View 8 Replies
View Related
Aug 6, 2012
I have a sheet whereby in column A i have company names and column B how much is invested in this company so:
A B
AAPL 10
GOOG 5
MSFT 15
APPL 5
IBM 20
GOOG 10
What i would like to do via macro is take all the duplicates sum them so that i see AAPL as 15 but only be left with one row. So we would be left with only 4 rows each with their summed weight.
View 9 Replies
View Related
Mar 31, 2008
I have a spreadsheeet with values in columns F:K from row 10 onwards
I need VBA code that will delete the rows where there are values opposite and equal in columns F:K from row 10 owards for eg if F10 is 10000 and K10 is -10000, F 25 is 150000 and G25 is -10000, G29 is -175000 and H29 is 175000, then rowa 10,25 & 29 must be deleted
View 9 Replies
View Related
Jun 26, 2009
In Column "C" there is a list of numbers ( these number realte to incidentsm and are called incident IDs) and I want to delete the rows that have duplicate incident IDs.
the row of numbers looks like this ....
View 9 Replies
View Related
May 11, 2006
how to find a certain criteria and delete the entire row that the criteria is in. I am more interested in finding certain criteria with vba, in my case any text/non numerical values and clear contents from that cell for the entire worksheet while retaining the rest of the cells that have numerical values in them. My data has --- in cells that represents missing data which would be easier if it was completely blank.
View 2 Replies
View Related
Oct 24, 2006
How can I delete rows in a worksheet using values in a certain column as criteria?
Example: I want to delete all rows that show a value of zero in column k. How is this done?
View 6 Replies
View Related
Oct 24, 2007
I am working with an Address Worksheet where the house#, street name and street type are 3 seperate columns. I need to combine that data into one "Address1" column. I can use a Concatenate() formula to combine the data, but I need to be able to have that data output to a new column, independent of the formula.
The problem that I am having is if I try to "copy" the concatenated data to either a new column or new worksheet it only wants to copy the formulas. I don't need the formulas anymore, I need the data.
View 3 Replies
View Related
Dec 13, 2013
Every so often I get a spreadsheet form some one else, and when I start to edit it formulas turn immediately into values.
For example, I enter a =10/2 ,when pressing enter, the cell displays correctly the result, 5. But when I click into the cell, instead of having the formula
=10/2, the only value inside is 5.
How do I keep this happening ??? I want the cells to keep the formulas.
View 2 Replies
View Related
