Inserting Rows Breaks Cell References But Can't Use Absolute

Jul 23, 2006

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.

View 3 Replies


ADVERTISEMENT

Using Absolute Cell Refernce And Inserting Rows

Dec 12, 2005

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?

View 10 Replies View Related

Relative Or Absolute References? (each Day Is Exactly 10 Rows)

Apr 25, 2007

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?

View 2 Replies View Related

Finding Breaks In Data, Inserting Rows

Jan 20, 2007

I've got data output (~500 rows) that's going into a larger, formatted sheet. The data going into the sheet is about 8 columns of material and the formatted sheet has over 20 columns (the rest to be filled out by the engineers). I'm tacking the new information to the bottom of the sheet (in the corresponding, non-consecutive columns), but the question now is how to format it.

Each location has recommendations, about 1-5 rows. What I want to do is insert a row after each different entry (probably by location address) so it will be more visually accessible (this is the format of the sheet).

View 4 Replies View Related

Excel 2010 :: Absolute References Do Not Apply To Inserted Rows?

Nov 18, 2013

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.

View 3 Replies View Related

Inserting Page Breaks On Relative Rows Based On Data

Oct 29, 2008

Each week I format a report that I receive that lists every product a particular employee closed. Its a list of each employee, the product and date. I'll have about 20-30 lines for each employee. I can easily sort by the employee name to have a nice list.

From here what I do is use a forumla to mark where each employee's name changes, then filter by that changed line and insert page breaks for each of these lines. Is there a way to macro this to save me time, having 270 employees takes alot of time still, because thats 270 manually inserted page breaks.

View 4 Replies View Related

Undo Absolute Cell References

Jul 26, 2009

I'm using a lot of "activeCell.offset(row,col).address" type of entries to form formulas for me. All of the references come across as Absolute References - $G$24, for example. Is there a different way for activeCell to give me a "normal" address ('G24' vice '$g$24') or a function that will convert for me?

View 2 Replies View Related

Absolute Cell References With Macros

Jul 31, 2006

can't seem to understand how to keep cells absolute when creating a macro

View 8 Replies View Related

Automatically Put Absolute $ In Cell References

Aug 27, 2006

When specifying a cell, what do I press to make Excel automatically insert the $ signs like $C$5 ? C

View 9 Replies View Related

Converting Absolute References To Relative References In Formulas?

Feb 10, 2014

I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.

All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.

=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")

View 1 Replies View Related

Absolute Cell References For Large Amount Of Cells?

Aug 13, 2014

I want to link a number of cells on one worksheet back to another worksheet within the same workbook. Say everything from Cells A1 to M90.

I find the fastest way to do this link A1 on second worksheet to A1 on first worksheet (by using = and click on worksheet 1 Cell A1).
I then click on cell A1 on worksheet 2, grab small square in bottom right hand side of cell, and drag accross to M1.
While A1 TO M1 is still highlighted, I then grab small square on bottom of cell M1 and drag down to M90.

This makes all cells within this range reference back to worksheet1. In advanced settings I have unchecked for all blank cells to show a zero in woksheet 2.

Therefore I now have what I currently want, although I would like to make all reference absolute?

I tried doing this from the beginning again making A1 absolute at the start, but dragging the cells across and down does not provide the info I am looking for in all cells?

View 1 Replies View Related

Inserting Page Breaks Based On Cell Text?

Feb 13, 2014

I'm working with a large report that includes ~6000 rows of data. Within the report the data is separated into a couple hundred town names - "Town of XXX" - and I need a page break at each town. Is there a way to insert a page break based on text in a cell? Should be an option in conditional formatting but... Need to avoid VBA.

View 1 Replies View Related

Copy Non Absolute Cell References In Conditional Formatting Formula?

Dec 3, 2012

I have this fairly simple formula which decides whether to shade a cell or not

=AND($X$1<>"TBD",R3<>"None",AC3="Y")

This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.

View 12 Replies View Related

Conditionally Format Cell Using Absolute Values And Relative Cell References?

Mar 25, 2013

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].....

View 1 Replies View Related

Not Inserting Cell References When Navigating Forms

Apr 11, 2013

Imagine I have a cell with validation and I need to add to my list. My list is Cat, Dog, Frog, Parrot and I need to add Elephant. So I tab down to Source, I want to right arrow to after Dog and type Elephant, but as soon as I hit the right arrow key it replaces the full list with an absolute reference to the selected cell. Is there any way I can get this to not happen so I can instead use the arrow keys to edit the text in the same manner as when composing this text.

View 2 Replies View Related

Inserting Page Breaks With VBA

Apr 23, 2013

I am having serious issues inserting page breaks using VBA. I have even taken code from the Microsoft website and it still crashes.

Code:
Sub InsertPageBreaks()

With Worksheets(1)
.HPageBreaks.Add.Range("F25")
.VPageBreaks.Add .Range("F25")

[Code] ......

View 2 Replies View Related

Absolute References: The Sub Worksheets To Update Automatically

Aug 4, 2009

I have a workbook with a main worksheet where all the data entry and calculations are made and then I have "sub" worksheets which mirror subsets (specific columns) of the information from the main worksheet. When information in the main worksheet changes, or rows/columns are added, I want the sub worksheets to update automatically.

The only way I can figure out to do this is to use absolute references in the sub worksheets. Does anyone know how to change the references to be absolute (using the F4 function) without having to do them one by one? Or can anyone suggest how I can achieve this in another way.

View 3 Replies View Related

Relative, Absolute & Mixed References In Many Cells

Jun 17, 2006

I've read several threads about switching between relative, absolute, and
mixed references across several cells however these solutions seem to result
in formula with all relative or all absolute or all mixed.

I need to change the formula in lots of cells with a mix of types of
reference. e.g. I need to change "$E$4*AD$2" to "$E4*$AD$2" & would prefer
not to have to go though each of the cells with F4!

View 11 Replies View Related

Absolute External References When Defining Names

Nov 29, 2006

I would like a defined name (1) in my excel document (A) to refer to another defined location (2) in an external document (B), which is a master document that will not move or be renamed. The main document (A) is one which will be copied to numerous locations within our company's network.

Therefore, I need the defined name (1) to have an absolute reference to the external document (B). At the moment I'm only able to get a relative reference, since Excel 'simplifies' the reference when both files are opened. Here is what I'm currently using: ='J:Invoice TemplateOffice list01 231106 List of offices.xls'!SiteNames

View 3 Replies View Related

Inserting Breaks In Two Sorted Columns

Jul 13, 2009

I have been struggling to figure this out for several days now and i don't really know where to go from here.

I have 2 columns and i need to break them up at certain points.

The attached excel file should hopefully be clear enough to show what i mean.

The first tab is how the data starts the second tab is how i want it to look.

To clarify i need to break it after each change in the first column. ie from 1 to 2.

Furthermore, i need to break the "sub groups" the second column up after 15, and 30. I will never have a number larger than 32 in the second column.

I think the excel file will clear up what i mean.

View 13 Replies View Related

Change From Absolute To Relative References With Excel Macro.

Sep 6, 2009

How to change from absolute references to relative references.

Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365

View 12 Replies View Related

Inserting Page Breaks For Certain Sheets Using Macros

Aug 29, 2009

For each of the sheets below, I need to extend the bottom of the page down ten rows and i need a page break after 60 rows.

Does anybody have any ideas? Here are the tabs that I need this macro on:

(MySheets = Array("FY09 Installation Support", "FY09 Install", "FY09 Purchase", "FY09 CF Discretionary Grants", "FY09 CF LOI", "FY08 Purchase", "FY08 Installation Support", "FY08 CF Discretionary Grants", "FY07 Sup Install Support", "FY07 CF Install Non-LOI", "FY07 Sup Purchase", "FY05 CF Carryover Install", "FY04 Recovery Funds", "FY05 Recovery Funds", "FY08 Safety Carryover", "FY09 Safety", "FY09 Transport Canada")

View 9 Replies View Related

Disable Automatic Absolute References When Linking To External Sheets?

May 30, 2014

Would be a massive time saver but can't find anything here or on Google on how to do this.

View 1 Replies View Related

Change Multiple Formula Range References Between Absolute/Relative

Mar 16, 2008

Is there a way to absolute reference multiple cells at one time?

View 5 Replies View Related

Absolute Reference To The Cell Even When Rows Are Inserted

Feb 17, 2009

I 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.

View 9 Replies View Related

Deleting Rows Below Current Cell Without Using Absolute Row Titles

Mar 21, 2012

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 Related

Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets

Dec 15, 2008

I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.

View 9 Replies View Related

Open Workbook 2 From Hyperlink In Workbook 1 Breaks References?

Sep 17, 2012

I have set up a workbook (wb2) that has external references to another workbook (wb1). All is good and works fine when you open the 2 files from windows explorer. I have then put a hyperlink to wb2 from wb1. When you click on this link the file opens but throws up errors saying some of the named ranges cannot be found?

View 3 Replies View Related

Inserting Rows With Formulas And Formats Based On Cell Value

Jan 11, 2014

I 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].....

View 2 Replies View Related

Inserting/deleting Rows Without Registering Cell Change

Oct 30, 2009

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 Related







Copyrights 2005-15 www.BigResource.com, All rights reserved