Unchanging Sheet Reference When Rows Are Added
Oct 28, 2009
I have a sheet titled PartNumbers....which contains in numerical order just 1 column of random 1, 2, or 3 character numerical part numbers. nothing special. and not necessarily in 1 by 1 order. like 45,46,47,48,49, it will skip number more like 45, 49, 50, 55. I have another sheet titled FinalReport.....that takes the raw data i have from the PartNumbers sheet and outputs the data onto a nice pretty looking designed sheet.
on 1 column of FinalReport I have =PartNumber!C2, C3, C4 and so on for the total number of parts i have to fill in the FinalReport sheet for each cell. The Question is, on PartNumbers when I want to add a part, say my sheet goes number 45 then 49 and i want 46... What I do is right click row number and select Insert, that adds a new row for my new part number. Now that move doesn't correspond to FinalReport now, it just removes the row i inserted. How do I get everything to flow to FinalReport?
View 5 Replies
ADVERTISEMENT
Sep 23, 2007
I have a userform which creates a new sheet using TextBox2.value as the sheet name. Here is the code I am using and it works fine. Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = TextBox2.Value
Here is the problem - After additional code is run on the newly created sheet I need to take the value (Which will be text) from cell AM6 of the newly created sheet and place it on an activecell on Sheet3. Seems simple enough but I do not know how to address this new sheet as I do not know what the name will be. Here is the code I have tried (and various renditions of it)
ActiveCell.FormulaR1C1 = "=TextBox2.Text!R[-9]C[32]"
I do not want to specifically name the new sheet as many sheets could be created, unless you could tell me a way to do that.
View 5 Replies
View Related
Apr 4, 2008
I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:
=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998
becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998
View 3 Replies
View Related
Jun 27, 2013
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
View 8 Replies
View Related
Feb 27, 2009
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
View 8 Replies
View Related
Jun 11, 2008
I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C
As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.
I also need E (which has also shifted over one) to sum A,B,C, and C2.
Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
View 9 Replies
View Related
Mar 21, 2008
I have a formula that I want to insert using a macro, so how do I iterate the * values in this line?
ActiveCell.FormulaR1C1 = "='Sheet1'!R[*]C[*]"
View 9 Replies
View Related
Sep 26, 2007
I am using excel 2007. I am working on an account register. I am using my spreadsheet to track when deposits and withdraws are made, when bills are paid, and when check have cleared my bank. I am using condintional formating to black out rows when bills have been paid, checks have cleare, and when withdraws have been made.
I only have one issue. I am tracking my current balance at the bottom of my spreadsheet I want to be able to go back and delete all of blacked out rows once a week without losing the value that was contained within them thus leaving the current balance unchanged.
i.e
On Monday the 17 check number 2207 clears my bank so in comumn “C” enter the word “cleared” in row 210 the row then turns black and everything is fine. My current balance now is $1,678.25, at the end of the month I want to be able to go back and delete all of the blacked out rows. So I go to row 210 and remove the row but now my balance changes back to what it was before that value was entered.
I want to know is there is a way to leave the balance unchanged even after I have deleted that row?
View 9 Replies
View Related
May 24, 2007
I have tried several macros and different code to accomplish the following:
1. scan a 3x9 barcode via hand-held scanner to populate a cell in column A, lets say A2.
2. the scanner does this and puts the number, say "330030" there (via the forms function)
3. in column B I would like it to say the date and time of the scan
4. the macros I have used will 'sometimes' populate the date / time but when I reopen the sheet, everything changes to 'NOW'. I would like the data to not change the date.
This is for a simple inventory spreadsheet that I am building to track movement of stock.
View 9 Replies
View Related
Jun 26, 2006
i'm trying to get data added in one sheet of a workbook to automatically be entered into another sheet. such as a monthly, Quarterly and Annual balance sheet.
View 3 Replies
View Related
Oct 23, 2006
How do I apply a formula to every cell in a given column?
I'd like my workbook to store this formula:
=c<current_row> + d<current_row>
in every cell of column P as a default.
This means that even if I do "insert row", I wouldn't have to copy this formula into the new cell.
View 6 Replies
View Related
Jan 4, 2007
I've created a simple budget sheet outlining various group expenses eg: Fixed, Flexible, Variable, and their totals sums.
How can I add all totals while still allowing for a new row to be inserted in any group?
eg:..............Row E
1........Fixed Expense group
2..................$20.00
3..................$40.00
4..................$40.00
5....Total sum $100.00
6.......Flexible Expenses group
7...................$10.00
8...................$20.00
9...................$20.00
10..................$10.00
11..................$50.00
12...Total sum $110.00
13......Variable expenses group
14..................$10.00
15..................$10.00
16..................$10.00
17..................$20.00
18..................$10.00
19....Total sum $60.00
20--------------------
21..Grand Total $270.00
View 4 Replies
View Related
Jun 26, 2013
I am needing to create a form that exports data (a quote) to an Excel Db (table) and is then able to recall the data back into the form. (the default form in excel does this and I want to copy that.)
Once the data is called back in, I can then export it to another Table to show that the quote has been approved and will be used.
I am having trouble with the VBA coding that copies the inputted quote in Cell C2 (the reference for the quote number) of the "Form" sheet and looks it up in the "Database" sheet. I have tried several variations of code, but nothing works so far.
-SS
Sub RecallQuote()
'
' RecallQuote Macro
'
Sheets("Form").Select
Range("C2").Select 'this is the cell that holds the quote number to look up from the table
[Code] ......
View 2 Replies
View Related
Jul 24, 2009
I have a worksheet on which users will enter data one row at a time, in columns A-K. When the user starts to enter data in a new row #, that is, when they make any column in the next, unused row have non-blank value, I'd like the sheet to update the borders of the row for columns A-K. I want the Range from A1 to K# to have full borders, so that the whole data set is outlined and easier to read.
For a bonus, I'd like the formulas from the previous H, I, and J cells copied down into H#, I#, and J#, adjusted appropriately for their reference changes.
Normally, I would work around the edges of such a VB problem by recording the actions manually and then modifying that code as I learned more. But I'm not sure if what I've done is the right way to start that process. I think that if I use the CurrentRegion property, and the Worksheet_Change event, I'll be well on my way.
View 2 Replies
View Related
May 18, 2012
I have a workbook where on one sheet various peoples information is listed and based on their organization the information is then linked to one of a few different worksheets for costs. On the cost worksheets, when a new person is added to the original worksheet, their information populates the next row. At the end of the cost columns I have a the total cost of each person added together. What I need to do is have these totals continue to move down the worksheet as new people are added. Is there a way to do this in excel using VBA or another function?
View 1 Replies
View Related
Dec 10, 2009
I am working on a spreadhseet with multiple tables present on the sheet. Each table has various formatting, merged cells, formulas, and defined lists. I need to have the table add a new row below the last including cell formatting, formulas, defined lists, so I can just keep going through and do data entry. The code adds a row at the end so I don't run into the table below the present one, by monitoring how far away it is from the "Type" cell tag in column A. The code below is a compilation from viewing other threads, but it just does a copy and paste, including the data that was input so I'm looking at a duplicate row. How can I get ito to clear values in the new row? I can't seem to get it to paste xlPasteFormat and xlPasteFormulas
Is the new row in fact being added below the last? I think it's up one after the macro had run through.
Private Sub Worksheet_Change(ByVal Target As Range)
r = Target.Row
c = Target.Column
If c <> 1 Then Exit Sub
Application.EnableEvents = False
NextLineValue = Cells(r + 3, c)
If NextLineValue = "Type" Then
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).EntireRow.Copy
ActiveCell.Offset(0, 0).EntireRow.PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Application.EnableEvents = True
End Sub
View 5 Replies
View Related
Jan 5, 2007
I have a sheet (Sheet 3) that is pre-populated from another sheet (Sheet 1), and the user is allowed to update the numbers (for forecasting).
I have a column ( Total Hours) that I use to total the new numbers in the row.
If the user inserts a row, the Total Hours formula does not follow. This is the Total Hours formula that I am using SUM Formula
(=IF(SUM($G30:$AP30)=0,"",SUM($G30:$AP30))
How do I (Can I) get the formula to cascade into the new row.
View 8 Replies
View Related
Mar 24, 2008
I want to create a code that adds a check box for each sheet that's added and takes the name of the sheet as the Caption.
View 10 Replies
View Related
Feb 12, 2014
So I'm trying to create a macro that will simplify dealing with a file we receive at work pretty often. I've enclosed a spreadsheet that shows a very basic example of the files we receive. What I need to happen is this: On the rows that have only the Company # in column A and Total $ in column D (ie. rows 5 and 6), I need the Total $ amount moved up one row and then for the row it was previously in to be deleted (at that point that row should only have the Company $ and no other data in it so it's basically useless). The problem I'm having is that if any rows are added to the file (for instance if an row was added between rows four and five) it would throw off my macro.
View 3 Replies
View Related
Jun 30, 2014
I'm using a workbook that has one sheet that pulls data from all the others, displaying a series of rows that summarizes data from each worksheet, each of which is full of data. Its a worksheet that a lot of people use so its kind of tedious to use as of now, because whenever you add a new worksheet of data you have to manually create a row and then manually change all the worksheet references. Is there a way to make rows automatically add and update when a new worksheet is added? I know it would probably have involve some sort of Macro or VBA.
View 1 Replies
View Related
Apr 16, 2008
I'm looking for: a cell (in column C) that sums Column C from C6 to the row above said cell. (A Total) So if new rows were added, these values would be included in the sum.
What I'm currently working with is a simple Sum formula, but this sum does not include rows added after the predefined sum range.
View 9 Replies
View Related
Apr 2, 2008
I have code in the NewSheet event of ThisWorkbook which tracks new sheets being added. But when a worksheet is added by copying an existing worksheet this event doesn't seem to be triggered. Buy logically a new sheet has been added to the workbook so the event should be triggered. Is this a design flaw or am I missing something?
View 7 Replies
View Related
Mar 5, 2013
I have a worksheet used for a car game. Each row is designated to show one particular car that can be used in the game, so let's say that there are 40 cars taking up 40 rows.
Column A shows the car type and model; column B shows the engine that the cars has represented by a number (the higher the number the better the engine is); and so it continues, Column C shows the transmission; column D the brakes etc.
Underneath the 40 rows (lets say from row 45 to 65) I then have a table with spare-parts that can be used to replace the cars existing parts. This Parts-table is also arranged with engines in column B, transmissions in column C, brakes in column D etc...
This point of this layout is that it should be easy to compare each type of part in the spare-parts table with the corresponding type of part already used in any of the cars as everything is perfectly lined up by the columns.
About the spare-parts table: because the inventory in the spare-parts table often are added to or subtracted from, as parts are won in races or being put on the cars, I have created a macro to sort the parts in each column so as to put the best parts at the top of the table and the bad ones towards the bottom - so far so good.
The problem is this - as I add new cars, the table of spare-parts is pushed down worksheet and that means that the macro no longer will reference the right rows. So the question is this, how do I automatically update the macro to adjust for rows being added above it? I am not really interested in redesigning the worksheet.
View 2 Replies
View Related
Nov 1, 2009
If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?
As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:
View 2 Replies
View Related
Jul 14, 2009
I have a spreadsheet that shows a large number of folks we had working in a particular division from Jan. 1st until now, 1800 +. Some of the folks are on the sheet twice due to having more than one role. The sheet lists last name, first name, skill description, pay rate, company they worked at...etc. Most of the folks on the list are not currently working but some are. I have another spread sheet that lists the folks that ARE currently working. I'm trying to see if there is a way to compare the two sheets, via a formula, that will be able to identify when the first name, last name, company name, and skill description are the same and then have those identified deleted from the first sheet.
View 14 Replies
View Related
Feb 17, 2014
To get the content of cells in other sheets of the workbook you refer to the 'Name' of the sheet and write e.g.:
[Code] ....
However, I need to refer to the '(Name)' of the sheet rather than the 'Name', i.e. 'Sheet1' rather than 'Fruit', and I would like to do it in a formula not using VBA.
View 5 Replies
View Related
Dec 7, 2008
I paste a weekly information sheet into a workbook. I pull information from cells within the sheet to form a weekly report. The sheet I paste is identical in columns but not always identical in rows; sometimes it may not have any information on one of the accounts. That account will be deleted and the sheet is 10 rows shorter. The sheet is over 1000 rows so it is too time consuming to add and delete the rows each week to match. My guess is I need to use VLOOKUP & OFFSET but I cannot get any of the formulas I tried to work. The account number appears on each 10 lines for that account. I have made up an example workbook to give an idea of what I need.
View 2 Replies
View Related
Jul 20, 2013
I would like to atribute a property (like colour) to any new row inserted in the spreadsheet. I'm struggling to find the correct way to reference a all inserted rows.
View 2 Replies
View Related
Jul 24, 2013
I have a spreadsheet, that I have been using for years to estimate jobs. I started learning excel, by building this sheet. It is still a work in progress, as am I. I am always trying to figure out ways to make my life simpler by making the sheet more intelligent. I would like to be able to go from one sheet, to another and click on a part name, which would return a value associated with that part name, to an additive formula on the first sheet, to build and assembly. And, eventualy build a parts list from it as well. For ordering parts.
View 7 Replies
View Related
Aug 6, 2013
Sheet 2 contains my checking ledger and sheet 1 is my summary page that I would like to have show current balance from the ledger. Is there a way to reference a value from sheet 2 on sheet 1?
View 3 Replies
View Related
