I have a spreadsheet with a lot of financial information going down a column (about 500 rows down). Most of the cells are just typed in, but certain rows have sum formulas in them. Is it possible to copy values from another workbook, paste the values in, but somehow keep the formulas that are running through?
View 1 RepliesI have a series of formulas in Row 1 across the columns in worksheet A. I would like to copy these formulas to a single column and down rows in worksheet B while retaining the references to worksheet A. I know that I can manually enter the references in worksheet B but that would take a long time to do.
View 1 Replies View RelatedHow to express what I am trying to do in a sentence but basically I have this formula
[Code] ..........
I need B# to change automatally like it does when you copy the formula but i need the N1 and N2 to stay N1 and N2, how do i do this?
I have a spread sheet that I have locked with the expection of certain cells. I also have left the ability to insert and delete rows. The cells that are locked have functions in them run in sequance.
Is there a way that when the new row is inserted its copies the formula from the row above automatically but also adjust for the new row and adjusts the rows below it automatically?
For example if I insert a new row between rows 2 and 3 below.
(orginal layout)
a1 (unlocked) b1 (locked)(function is =sum(a1:b1)
a2 (unlocked) b2 (locked)(function is =sum(a2:b2)
a3 (unlocked) b3 (locked)(function is =sum(a3:b3)
a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
(layout after inserting row)
a1 (unlocked) b1 (locked)(function is =sum(a1:b1)
a2 (unlocked) b2 (locked)(function is =sum(a2:b2)
a3 (unlocked) b3 (unlocked) *inserted row*
a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
a5 (unlocked) b5 (locked)(function is =sum(a5:b5)
this is what i want the end product to be.
(layout if formulas are copied and adjusted after inserting row)
a1 (unlocked) b1 (locked)(function is =sum(a1:b1)
a2 (unlocked) b2 (locked)(function is =sum(a2:b2)
a3 (unlocked) b3 (locked)(function is =sum(a3:b3) *inserted row*
a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
a5 (unlocked) b5 (locked)(function is =sum(a5:b5)
I'm working on a spreadsheet that tracks project accepted and done over a course of time. I am expecting the data to eventually reach the thousands in a year's time. I have several formulas in specific columns that I need to replicate as the user enters new data into the next row.
While it is easy to copy paste the formulas from the cells above, we are looking to save time by having the sheet do this automatically which also prevents the user from accidentally deleting/modifying the said formulas. I am also looking to save file size which is why I am considering doing this in VB. An example of a formula that I need to move down into the next column is:
=IF(ISERROR(VLOOKUP(C2,Tables!A:B,2,FALSE)),"",VLOOKUP(C2,Tables!A:B,2,FALSE))
I am new to VB but would like to gain some understanding on how this works.
I was tired of making my list smaller to upload it here, so I uploaded the complete thing on megaupload :P Here is the link:
http://www.megaupload.com/?d=1W6PBADE
On the bottom of the list is a button; "Add New Anime". When you click on that you get a UserForm where you can enter; Title, Type, Total Episodes, D/L Eps and Watched Episodes. When you press ''Add Anime" the entered values are entered at the bottom of the list, underneath the corresponding list names on the top.
Now there are 4 list names left (Left, Status, Status 2, and Progress (%)) I still have to make a option to enter Status 2 as well in the UserForm.
Now the question:
In columns F (Left), H (Status) and I (Progress (%)) are formulas. How can I copy those formulas automatically when I add something new with the UserForm? And can it automatically insert a new row, since I have to move the buttons down with every new title.
If I have formulas in A1, A5, A19, and A36, highlight them with goto, how do I move them into B1, B5, B19, and B36? Copying and pasting puts them in B1, B2, B3, and B4.
View 9 Replies View RelatedI have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.
I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.
I've managed to be able to create a macro to make a copy of a worksheet within excel.
However, when that copy is create all the macros are lost. If it is not possible to copy all the macros that are within the "Original Hours" sheet, I at least need a macro that will take people back to the original hours sheet (we have disabled sheet tabs, so need the macros to navigate through the worksheet).
Code used is below
Private Sub Hours_Click()
Sheets("Original hours").Copy After:=Sheets("Original hours")
ActiveSheet.Name = "Update hours"
ActiveSheet.Unprotect "PASSWORD"
With ActiveSheet.UsedRange
.Value = .Value
Sheets("Update hours").Select
End With
End Sub
I have a piece of code that runs through various excel files and takes the data (minus the header) and pastes that into a separate workbook. The piece of code that does the actual copying i think is this (i have used code from the msdn website)
Code:
With sourceRange
Set destrange = destrange. _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
I have tried replacing the sourceRange.Value with sourceRange.Text however when i do that it will copy nothing any more.
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...
I need to copy a bunch of cells that contain formulas without altering the cell references. I know I could change each formula to contain absolute cell references, then copy and paste special with formulas, but this is alot of work, and following that I'd need to change the references back again from absolute to relative in both locations. So, is there a way to quickly copy and paste formulas in multiple cells without altering the cell references?
View 2 Replies View RelatedSo i have sheet 1 with some cells locked and protected (but open to copying) as a template and sheet two will be where people paste one or more of the template formula on an ongoing basis. How do i mantain the locked and protected cells after they have been pasted? I have unlocked sheet two pasted the first template and had to choose protect cells after pasting, and further pasting of locked cells are not protected.
View 1 Replies View Relatedi have data in worksheet 2, a table with formulas using worksheet 2 in worksheet 1, i need to move the data in worksheet 2 to another worksheet, but if i use cut or copy and paste the formulas do not track its movement, so how do i move the data to another worksheet so the formulas know where it went? i thought if you could select the data and drag the data straight into another worksheet but how?
View 9 Replies View RelatedI have a series of worksheets that are formatted for data imported from various scripts.
What I want to do is parse through each worksheet and cell, when the cell has numeric data, I want to clear the cell.
I do not want to clear the cells on the following conditions:
Cell has Text
Cell has Formula
Cell has date, month or time
I'm working on a project requiring the matching of data from multiple worksheets and got some useful advice from someone on here a few months ago to get it up and running.
At the moment I'm using SUMPRODUCT with an IF argument to check rows on one sheet for matches on 5 others and return a "Yes" or "No" value, which is working fine. The next bit of analysis however requires copying the value from another column on rows that match, and pasting it into the first sheet.
I have a sheet with a lot of different data. In column M in workbook "Maximo report" there are 5 different values. I want to copy the rows that contain CDC_HVAC and that have the value "INPRG" in column G to the sheet HVAC_INPRG. I want to do this for all 5 values. Once the rows are in their respective sheets I want to filter them based on a formula and then sort from oldest to newest. I dont want the file to be too big and I think that VBA is the way to do it. I am new to VBA
View 6 Replies View RelatedHow would you copy a row's formatting and formulas but not value. for example: A1 1 B1 =a1+2
I would like the copied row to be: A2 (blank) B2 =A2+2
I need to copy specific rows that have a specific value in colum B and Colum M to new worksheets.
So from below attached screenshot , say for an example macro shud filter rows that have "BigPond" in coloum B ,"RG2" in column M & "INT" in column W and i dont want all cells in these rows , i only want copy cells under column C,AK,AL,AM (in this order) to my other excel sheet that is named "BigPond" and it should paste it starting from Row5.
I want to avoid recording a macro as it selects a range and i am expecting more new rows every week so if a record a macro the cell range for selection changes and i get incorrect result.
i have created a table. when i m editing new row then formulas is copying bu value is not copying ?
View 3 Replies View RelatedI have a game where people are scored on the spreadsheet, but they can join anytime, so everyday, I get a combined list of points. The list consists of people who joined previously, and the new players. How should I combine the points?
[URL] ..........
I have dumped main frame data into excel. It is customer info that is in three rows of data each. I need to know if its possible to group or somehow keep three rows of data together for multiple sorts. The first row is the customer name, the second row is their first line of address, the third row is the city, state and zip. I need to add columns to this dumped data, but I will need to do a number of sorts for different views of the data. But as I've stated every 3 rows go together. I've tried different ways to group (for lack of a better word) the three rows, but nothing I've tried works. I've also done multiple searches, but either I'm not searching on the right word or this can't be done.
View 9 Replies View RelatedI have a spreadsheet that I want to keep the blank rows that seperate the data for asthetic reasons. Can I sort just the rows with data in them and still keep the blank rows where they are?
View 9 Replies View RelatedI'm trying to sort by City first, then by Report #, but keeping the highlighted rows together. If I use the custom sort it will through my D2's at the bottom and not keep them with their city, report, and D1. How do I get them to stay together? I have attached my document example.
View 4 Replies View RelatedI have an excel spreadsheet with approximately 10000 rows. There are approximately 10 columns...
Column A - person's ID number
Column B - name
Column C - Street Address
Column D - City
Column E - State
etc...
It being such a large list there are many many duplicates (it was pulled from a bunch of different excel spreadsheets and combined into one via copy and pasting one document into the other).
I figured out a way to have excel highlight the duplicates in a different color... but scrolling through 10k people and deleting the duplicates is obviously very tedious (approx 40% of the spreadsheet is duplicates).
Is there a way to sort out the duplicates (via their ID number, column A) so that there are no repeats? I want to get rid of the 2nd/3rd/4th (etc) occurance of the person's information but I was to obviously keep the first occurance.
I tried using Advanced Filter but I must have done something wrong because it shrunk the list too far down.
I have a spreadsheet with only 80 rows in it and I need data from another spreadsheet with over 200 rows. One of these rows in each spreadsheet contains a matching field (Job #). I only want the 80 matching rows data from the over 200 row sheet.
Is there an easy way to combine these two. Even adding the 80 to the 200+ will be fine as I can just remove the blanks. I can't figure out a way to combine them and match them up.
I have an excel file with over 20 worksheets and each of them have around 1200 rows. The first column in each worksheet contains the variable names and then the data associated to it is present horizontally.
I only want to keep around 80 rows from those 1200 rows. They are not in sequence (means they are not in continuous order) so I manually selected those rows by deleting the non required rows step by step.
I did it manually on 2 worksheets but I don't want to do that manually over 20 worksheets. Is there any method that can speedup the whole process.
I am attaching the snapshots of the worksheets..
This one is before I deleted the unwanted rows.
This one is after the deletion of unwanted rows.
I would like to know if there is an efficient way to save an excel workbook, with multiple worksheets as an .xls. I want the file to keep the formatting from the original file, while only saving the values of the original file. All the numbers in the original file are hyperion driven but the file is going to be emailed to people without hyperion. Is this possible?
View 6 Replies View RelatedI need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3----------------------------------------------0
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2---------------------------------------------0
Alice Alpha-----------------------------------------------1
Carl Carlisle--------------------------------------1--------------1---------------1
Ernie Elephant-------------------------------------------1---------------1
This is what I'm hoping it can look like:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2
In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.
Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.
I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns
I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.
I need the macro for deleting duplicate rows only it should keep one lowest value row.
for e.g.
Column A Column B
TS1234 100
TS1234 50
TS1234 200
Macro will compair dublicates in column A & it will keep lowest value row (i.e. 50 value in Column B), other dublicate rows will be deleted.