I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
example cell A4 the formula is ='week1'!$a$4
when I insert a row on the week 1 tab it changes tab 2 formula for cell A4 to
='week1'!$a$5
What can I do to make it not make that change but still be able to insert
rows?
I have a problem where my conditional formatting is broken when new rows are inserted because Excel is auto updating the cell references. I am not able to use absolute references because I need to be able to Copy the entire information many times on one sheet to handle an ever changing number of projects.
I have attached an example file which shows a simple version of the sheet. There are areas for two projects now, but more would be added to the sheet as needed by just copying the entire section of one project and pasting it at the bottom. For each project, there is a cell which has a data validation list, e.g. B6, from which the user can select the current stage. The list of stages is obtained from column A, e.g. A9:A18. Whatever stage is selected, I need it to be highlighted in some manner. I currently have conditional formatting that checks if the value in B6 matches the information in the current cell and will change the text font color if True.
The problem occurs when new rows are inserted into the project. For example: if the project requires a Beta 1.1 stage, then a row would need to be inserted and labeled for that stage. However, Excel auto changes the cell references so that it no longer looks at B6. I need some way that the current stage can still be highlighted when selected in the Data Validation list.
I know that what I am needing to do may not be the best method to go about this, but I am having to work within the confines of the software available to me and the intended users of the file. Since the number and length of projects can change on a daily basis, the users need to be able to add and remove room for additional stages and projects whenever they want. I have a basic solution available to me using a macro, but the overall solution is clumsy and just leads to more problems.
This may be an obvious one, I have received a spreadsheet with cell references or references to values that I am not familiar with, is anyone able to shed some light on how to interpret these references or at least find out where their source is within the spreadsheet?
View 3 Replies View RelatedI am typing a number into Cell B4 (ex. zza4582) this number is a serial number that will be in many differnt cells in column "A" on a different sheet "Template Paste". On each row with that serial number I need the information from "A-I" to be copied in the main sheet starting on row 10 and continuing on from row to row depending on how many times the serial number is found.
View 10 Replies View RelatedI have a spreadsheet that gets updated from the top. Is there any way for me to mod my formulae so that they always begin with the topmost cell (row 2 in this case, and in many columns) but still extend downward?
More practical example:
I have an Average formula in column X which (at the moment) averages X2:X75. I would like, even when adding new rows at the top (in the row 2 position) the forumla to not need to be manually extended with every new entry. So, if I add three more entrie4s, it will then average X2:X78. I tried all manner of absolutes.
What would be a line of code that could delete rows below a current cell without using absolute row titles? For instance, I pick a random cell and I want it to delete the three below regardless of what cell I pick.
View 2 Replies View RelatedI am attempting to create a template to make time and dollar budgets for various projects easier to create (20 plus projects a year with varying number of employees).
I want to have a template in which you input the number of employees working in a cell (IE. 3 employees), and with that the table expands to have enough rows to have 3 employees data input. However, only insert the number required but not overriding "totals" row below. With the new inserted rows I would like to keep the same format and formula for the row it was based on.
Example :
week 1 hours
week 2 hours
total
[Code].....
I can insert lines fine, but the problem is that now I want the worksheet to be able to delete rows without inputting time stamps. It can insert them with no problems. I have attached the file on as well.
View 3 Replies View RelatedOn sheet "Create Package" in cell "AA14" I have a value (lets say 2).
I want it to go to sheet "Samples" and insert a number of rows equal to the value on sheet "Create Package" cell "AA14" (so 2 rows)
I have a header row in row 1, so I would like it to insert the designated number of rows beneath that.
Then I would like it to copy a designated number of rows (based off of the "AA14" value, so 2) from the "Create Package" sheet starting at row 66 and then paste special values into the new rows that were inserted on sheet "samples".
On a workbook named "Main Book" I have my whole inventory list on this sheet.
totaling over 200,000 items with 4 colums per row
Material # - Description - Family # - Price
(seeing how there is only 65,536 rows I have this list across over 20 colums)
So what I want is to have a VLOOKUP referncing this workbook
example:
material # / description / price
H047071504 / plate 1" mt box / $650.29
H152369500 / burst disk 5000psi / $45.65
manual enter / auto fill / auto fill
In a different Workbook when I type the "Material #" I want the next column to automatically fill in with the information (Description/Price) referenced from "Main Book"
To simplify matters each day is exactly 10 rows (including header).
I already have code in column D that populates each D row IF it is the first unique occurance of column B for each day.
The forumla in column E for rows 2-10 put a daily total by each unique occurance.
The problem:
Since I am using absolute references in E2-E10 to contain the totals for that day if I copy E-2 -> E10 to E12-E20 the totals will be incorrect.
If I use relative references in the formula it changes the range for the day which is also produces incorrect totals.
Question:
How would I copy the formulas in column E to the addtional days so that the ranges follow for the specific 10 row day that they are intended?
build a spreadsheet that reads information off of a Master sheet onto 4 other sheets. The hope is that by making changes ONLY to the Master sheet that the other 4 will update automatically.
And then we ran into the trouble of not being able to insert new lines onto the Master sheet without throwing everything off on the other sheets.
(I've attached an example.)
For instance, if I go to the Master sheet in the Test.xlsx attachment, I've left out Lima from the alphabet. So, I insert a new row onto the Master, switch back to the Formula sheet where it should (theoretically) just update the cells to display the new data.
Not so. The Formula sheet just skips the new A13 and keeps on going.
the primary one seems to be using Offset. Well, the coworker will be adding and deleting many, many rows over the course of the year, as it is a product log and we change our products often.
I have a simple count formula which is suppose to be absolute.
=COUNT($L5:$V5) and works till I add a column via a macro.
Macro adds a new column at column M then inserts data.
My problem is when I check the formula after macro it changes to
=COUNT($M5:$W5) I thought using the $ sign means Don't change?
I have cells (all in one column) containing text separated by commas e.g. (SD-299, SD-200, SD-300)
I am trying to transpose the text in these cells into rows.
VB:
Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, ", ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub
The problem with that is that when transposing it does not shift the cells down (/ insert new rows) so I lose the data already in the cells underneath.
Also, that macro would only apply to one cell I would like to be able to apply it all the data in the specific column on my Sheet.
I want to move the absolute value of sheet 2 $D$42 to absolute cell sheet $F$15. The !sheet2 $d$42 is not working.
View 4 Replies View RelatedI'm not great anyways with VBA Macro.Effectively, here is my issue. I have a spreadsheet which is really badly designed.In one column I have multiple numbers separated by a comma. I need this data separated into new individual rows, but at the same time, copying the data in the other columns in that row to the new row.
Example of what I have:
Test 1 54 email1
Test 2 32, 343, 63, 34 email2
Test 3 4934, 5342 email 3
What I need:
Test 1 54 email1
Test 2 32 email2
Test 2 343 email2
Test 2 63 email2
Test 2 34 email2
Test 3 4934 email3
Test 3 5342 email3
I have have the following code below which paste everything into a new column and into a new row, but the problem I have is that it does not push the other rows data down, nor does it copy the 1st rows data (for that data set) into the new rows created.
Code:
Option Explicit
Sub Macro1()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
Dim inVal As String
Dim outVal As String
[Code] ........
The amount of rows I have, so this manually (text to column, then transcoding etc) it out of the questions (i have 1000 rows of this!)
I have an excel sheet that has the A column populated with many different things.
I'm not sure how possible it is, but I'm trying to group everything up by 7s and putting a space in between the groups.
So...
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
Would turn into
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ABS()>.005"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[code].....
i am currently using the macro below to import text files into a spreadsheet. Currently, it begins the import in cell A1 which is what I recorded it to do. how do I change the code to begin the import on the active cell?
View 2 Replies View RelatedI have a workbook with over 500 rows and those rows are divided in diferent sections, to which I have left five empty rows to add more data, the problem is, how do I add four more rows once I have only one empty row of the five empty ones I had previous? There's also a formula on column H that I'd like to be copied down on to the newly added rows.
I don't know how I'm gonna deal with the rest of the other data that is gonna shift down as more rows are added, I said this because I want to be able to do the same on all the sections I have.
I want to add a formula with absolute row to a cell.
The desired formula in the cell is something like "=SUM(B$5:B11)"
However, when i use the following code I keep getting error 1004 'Application-defined or object-defined error'
Code:
topCell.Cells(CurrentRow, 3)FormulaR1C1 = _
"=SUM(R-" & CurrentRow - 1 & "C[-1]:RC[-1])"
It works when the bracket [] is used for row, but it does not give me absolute row in the formula.
Code:
topCell.Cells(CurrentRow, 3)FormulaR1C1 = _
"=SUM(R[-" & CurrentRow - 1 & "]C[-1]:RC[-1])"
I have entered the current format of the data and the required format as well...
View 3 Replies View RelatedI have an excel sheet, where I want to insert a column or more columns between row C and D. The file is attached below. Usually, I would right click row D and choose "insert column". This does not work here. How would you do it?
View 2 Replies View RelatedHow do I insert a row with A-N Merged and centered? Interior color should be dark blue with white font.
Currently I'm doing this to add a space based on a number I have in a column S.
[Code] .....
In column S I have a series of numbers 1 - 5 and when a unique number is found Insert this row I can't seem to figure out how to create.
[Code] .....
I am new to VBA and am trying to insert 20 blank rows to my spreadsheet. I know how to add one row, but am not sure how to add 20 rows without repeating the same line of code 20 times.
View 6 Replies View Relatedi have a spread sheet that has a number of different addresses in them I need a vb code that will insert 5 rows between each row of data starting from row 42 and has the possiblity of running to the end of the rows 65536. i believe the best way to do it is via column B which will always have data in it. i have tried a number of methods but they all only insert the rows once or they do it the required ammount of times one after the other so i end up with a whole section of blank rows and all my data still together.
View 4 Replies View RelatedI have 2 columns of raw data. With
column one containing the document Names.
and column two containing the document titles
i.e
Column 1
Doc 1
Doc 1b
Doc 2b
Doc 3
Doc 10
Doc 11.5
Column 2
Manual 1
Manual 1
Manual 1
Manual 2
Manual 2
manual 3
Manual 4
I am looking to be able to reform this information on another sheet to look like this :-
Column 1
Manual 1
Doc 1
Doc 1b
Doc 2b
Manual 2
Doc 3
Doc 10
Manual 3
Doc 11.5
Manual 4
Now I have created an addional column on an other sheet which checks for the row number where a new manual appears. and I have made this look like the row I would like to insert.
The idea i have is to use the above Numbers and the row itself and insert it in to the formated report (working from the bottom though). With this doen for each manual name change.
the number of Documents per manual can vary....
how to write the marco for this
I have a store report, which every now and again needs to have a new line added for a new store that has been opened. I need a inputbox to display so that you enter a "store number". Once you enter the store number, the macro adds a new line to add the store number in numberical order.
Looking for assistance in inserting 24 blank rows in between a column of names. And if not too much trouble have the ability to enter the number of blank rows to insert.
Before:
Column A
name1
name2
name3
etc.
After:
name1
blank row
blank row
etc.
name2
blank row
blank row
etc.
name3
blank row
blank row
etc.
I need to insert a new row, in a spreadsheet, after every ninth row that now exists. How is this easiest done?
View 3 Replies View Related