Stop Cell Reference Changing When Insert Column

Jun 4, 2009

I have referenced data in two colums on a sheet A and B. Column A contains the latest data, each month i insert new column (moving column A to column B). However all of my references continue to follow the original data (eg will change from column A to column B). this happens despite using Absolute references. (=$A$1). Is there a way to lock these cell references to only ever display column A etc?


Stop Formula Column Reference Changing On Insert But Not Row Reference

Mar 6, 2008

A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.

When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.

View 2 Replies View Related

Stop Formula Cell Reference Changing When Copying

Nov 1, 2006

I am trying to cut/paste a row of formulas in my spreadsheet. The problem is that I want some of the referenced cells in the formula to stay CONSTANT and not increment on each new row. In this example, I want the references to row 17 (F17,E17) to increment (E18,E19,...), but I want the "G7" reference to remain hard coded (as G7) on each row I paste (because that is where my master value is). (The formula is working fine) =IF((F17>0),IF((F17<=G7),E17,0),0)

View 3 Replies View Related

Stop Formula Changing Cell Reference But Change Sheet Number When Dragged?

Feb 8, 2010

need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?

View 4 Replies View Related

Reference Changing Column On Specified Cell?

Dec 9, 2013

I have 2 Sheets in Excel: Sheet 1 & Sheet 2

Sheet 1 has some basic data (numbers) in a column, say AB1, AB2, AB4, AB15 (specific cells)

Sheet 2 basically references the data in a nicer template, with the basic forumala: ="Sheet 1"!AB1, etc..

Now...Sheet 1 has a new column inserted and new data added weekly, so AC1, AC2, AC4. AC15

How do I ensure that Sheet 2 recognises the new Column Insert (incremental letter) and references it automatically?

View 6 Replies View Related

Insert Formula Based Off Changing Reference Cells

Mar 6, 2014

i need a macro which puts the formulas into the cells as per attached.

the number of rows between the "beam" can be variable
the number next to the beam will be variable.
the number of "beam" rows is vaiable

my thoughts where to do a find"beam" and refernce the cells address the create formulas off those points but dont have the skill to code this

beam macro.xlsx

View 2 Replies View Related

How To Stop Linked Cell Destination From Changing

Oct 30, 2013

This is the current layout I have:

Sheet 1: A1:B20, is linked to Sheet 2: A1:B20.

I need to copy and paste data from the internet into Sheet 2, but it's not in the correct order, so I must swap column A and column B in Sheet 2 only. The problem with this is that when I swap them, the linked cells swap as well, which destroys my data on Sheet 1 columns C-Z.

Is there a way that I can keep the cells on Sheet 1 completely static, so that it only reads the data I put into the corresponding cell on Sheet 2?

Or maybe, is there a way I can just swap the cell information in Sheet 2 without messing around with the formatting? Just a complete swap of A1>B1 and B1>A1?

View 4 Replies View Related

Stop Procedure Window Changing When Changing Sheets

Sep 9, 2007

Moving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?

View 4 Replies View Related

How To Autofill 9,000 Rows With Changing Reference In Same Column

Aug 9, 2012

I have over 9,000 rows of data. In column A, I have different values that I need to populate down to associate with values in other columns. I can't simply autofill all 9,000 cells in column A at once, because the values that need to be filled down change at irregular intervals.

My end goal is to be able to filter out values in column B to show their association with the value in column A, but I need column A fully populated.

So I need a way to fill A2:A7 with value from A1 (I don't care about B7 being empty, I can still have Martha in A7 with no adverse affects). But I need the fill to continue through 9,000+ rows where the number of rows to fill is inconsistent between value changes in column A (Martha-5, Sarah-3, Beth-4, Donna 3), and there are over 400 unique values in column A.

This is definitely more involved than I am familiar with, but any simple way for me to identify and list which of the 400 bakers made scones..



View 5 Replies View Related

Insert Columns And Automatically Changing Total Column?

Sep 25, 2013

I have a worksheet with data in columns F,G,H and I. There is a total in column J. (=SUM(F1:I1)

I've written some code that allows a user to insert additional columns. However, I'm finding it difficult to find a way of changing the calculation in the total column automatically when a new column is inserted.

View 1 Replies View Related

Stop/Prevent #VALUE In Formula Reference Error Cell

Sep 3, 2006

A1 refers to B1 which contains a number or #VALUE!

What could I use in A1 to return a number and if B1 = #VALUE!, a zero.

View 9 Replies View Related

Changing A Cell Reference

Jul 14, 2009

I have three sheets - Actual( which has actual figures from Jan - Dec), Budget ( figures from Jan - Dec) and a Summary sheet ( which is just the summary for the current month, say July and compares the budget VS Actual). Every month i need to change the summary sheet to the next months set of figures, say Augusts etc etc.

Currently i've been using find and replace, and changed the formula to the correct months column - but its a rather large spreadsheet( its a management accounts pack and has many many more sheets that link up - like lasts years comparison etc etc)
Is there any easy way of linking ( without VBA) or anything complex, so that each month i can change my data easily?

View 3 Replies View Related

VBA To Insert Formula And Then Fill Down Number Of Rows In Reference Column

Apr 8, 2014

I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:

Sheets("Sold Articles Database").Select
Range("U3").Formula = " =VLOOKUP(LEFT(K3,2),'Input Variables'!$A$48:$B$52,2,FALSE)"
Range("V3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$C$10000,3,FALSE)"
Range("W3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$D$10000,4,FALSE)"

[Code] ........

View 3 Replies View Related

Changing Linked Cell Row Reference?

Nov 14, 2009

I have a master summary sheet that contains all the required information for 21 other worksheets. Each row contains the information for a single sheet. I have linked all the relative cells to the first sheet but would like create a formula that changes the row reference in each of the links rather than the tedious task of updating each individual link in every sheet.

I have tried using the indirect function but constantly get errors returned, the basic formula i have been using is: =INDIRECT("Summary"!C&,X1)

'C' is the column in the summary sheet that the information should be taken and 'X1' is the cell in the worksheet that will define what row the data should be taken e.g in one sheet the reference for that specific cell will be C5 and the next C6 and so on.

View 4 Replies View Related

MAX Date - Cell Reference Not Changing For Each Row

Aug 10, 2013

I'm using the following formula which is entered by using VBA on the worksheet change event but the cell reference 'A2' is not changing for each row, i.e. A3, A4 etc.....

=MAX(IF( 'Device Use - 4 month Period'!$A$2:$A$20000=A2, 'Device Use - 4 month Period'!$C$2:$C$20000))

View 4 Replies View Related

Change One Value Of A Cell Without Changing Others That Reference It

Apr 7, 2007

I am creating a schedule to use for our helpdesk - it has pre-defined tasks that need to be assigned each day - easy enough to accomplish with a formula. And I've done this by using this similar formula on each line of the tasks (This first task is based on a manual entery into one field (B19))
=IF(AND(B6="Liz"),"Paul",IF(AND(B6="Paul"),"Mark",IF(AND(B6="Mark"),"Diane",IF(AND(B6="Diane"),"Jeff",IF(AND(B6="Jeff"),"Dan R",IF(AND(B6="Dan R"),"Chris",IF(AND(B6="Chris"),"Liz","")))))))

So, B4 is one task (phones) and the formula above (B6) is from another task (tickets) and then a third task (admin) looks at the value of B5 and uses the same formula above, etc.

However, I want to be able to change the value of a single cell, based on the (manual entry) value of another (out of task) series of cells (i.e. Sick (B35) & vacation (B36)) - and I don't want any of the other task cells(in the C cell series of 'tasks'), that are basically linked together by the value of the other cell, to change. Thus, in turn, would mean that the same person would now be in two different cells, and thus is creating a circular reference.

Let me spell this out a bit clearer.
The first manual field is B19 - If I enter 'Chris' into this field, then field C5 matches it. Then field B6 uses the formula above to figure it's value; as does B7 thru B12.
Now, If I put another manual entry into 'Sick' (B35), then I want whichever task that person is assigned to (for this example, we'll say I entered 'Jeff" into Sick, and Jeff happens to be on 'tickets' that day) to change to whoever is in cell B12 - but I don't want any of the other cells to change - but, since they are all dependant on each other, this doesn't seem like a possibility.

I've tried creating a count of names in the Sick or Vacation fields and then added this to the formula
=IF(AND(C5="Liz", B38=0),"Paul",IF(AND(C5="Paul",B41=0),"Mark",IF(AND(C5="Mark",B40=0),"Diane",IF(AND(C5="Diane",B40=0),"Jeff",IF(AND(C5="Jeff",B43=0),"Dan R",IF(AND(C5="Dan R",B37=0),"Chris",IF(AND(C5="Chris",B45=0),"Liz",B12)))))))

B38 being Pauls count; B41 being Marks, etc - so as long as their count equals zero, they are 'eligible' to fill that slot.
B12 is the last person on the schedule.

View 8 Replies View Related

Inserting Row Without Changing Reference Cell On Another Sheet

Jul 3, 2014

I am trying to create a formula that references information on two different sheets, but i want it to show me the values that are newly created when a new row is inserted. The two current sheets are Master and Data.

I have a formula on Master that reads ='Data'!$K$3 to reference the value on Sheet data in cell K3. When a new row is inserted showing me the current readings, I want the Master sheet to show me the NEW value in cell K3 on the data sheet. The problem is, the insert of a row moves the formula on the Master sheet to now be ='Data'!$K$4. How can i STOP the insertion of a row from changing the formula so i continue to see the value i need?

View 3 Replies View Related

Changing Cell Reference In A Formula When Inserting A Row

Jul 10, 2009

I have a function Sum('1st Qtr:4th Qty'!AW1) in row 1 and dragged down 129 rows so the last reads Sum('1st Qtr:4th Qty'!AW129). If I insert a row the cell reference does not increment automatically below the insert location. How do I get it to do so. The insertion occurs when I run a macro.

View 3 Replies View Related

Repeat Formula 5 Times Before Changing Cell Reference?

Feb 27, 2014

how I can have a formula repeat down a column five times before it changes to another formula? For example. Say on tab 1 I have a list of products. On tab two I have five codes that repeat down the page over and over again. On tab two next to the repeating codes I need to repeat product one 5 times and then skip to product 2 on the 6th row and repeat five times and then skip to product 3 on the 11th row and repeat 5 times and so on?

View 10 Replies View Related

Copy Formulas In Cells Without Changing Reference Cell?

Jul 7, 2014

I'm preparing an accounting model for my office use. How to solve the copying of formula to all other cells.

The detail is here:

Sheet 1:
1) I've entered a number 1000 in L2

Sheet 2:
1) I've entered a values in columns D,E,F,G
2) Calculations :
at G2 : the formula is =D2*Sheet1!L2

The problem is when I Copy the formula in G2 through G3, G4, G5........... it changes to =D3*Sheet1!L3, =D4*ValidData!L4, =D5*ValidData!L5 and so on... but it should be =D3*Sheet1!L2, =D4*ValidData!L2, =D5*ValidData!L2, so that the L2 value shall be constant for calculations in all cells.

View 2 Replies View Related

Excel 2010 :: Cell Insert In One Column To Insert In Other Columns?

Mar 23, 2012

I have a master data sheet with four columns, A, B, C and D

Column A has the primary data and B,C,D has dependent data values;

So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;

View 2 Replies View Related

Insert Cell Reference In Linking Cells?

Oct 16, 2013

I want to link cells B1 thru B16 to Cells F1 thru F16 receptively.

However, at times I need B1 thru B16 to show other columns 1 thru 16.

I would like to type in the column designation in A1, ie. F,G,H,I.....and have the values of those columns shown in B1 thru B16.

View 2 Replies View Related

Insert Values Based On Other Cell Value Reference

Mar 21, 2014

The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.

Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’

Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’

Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’

View 9 Replies View Related

Insert Rows Automatically From Value Specified In Reference Cell

Sep 8, 2009

Can anyone help? I need to achieve the following involving the insertion of rows from a specified value reference within the worksheet to which the rows are being added.

The original data would look like:


The number of rows to be inserted under each entry is listed in column "C" (which is a count of the separate entries in column "B".

The output data following the application of the "solution" would need to look as follows:


Where the specified number of rows have been inserted underneath the original entry and the separate values in column "B" have been listed in consequtive cells, relating to their original entry in column "D".

Can anyone suggest code to achieve this outcome?

View 9 Replies View Related

Insert Cell Reference Into Macro For Data Filter

Nov 19, 2013

I want to insert a cell reference into the code below to replace the hard coded "100500" so the value in cell A1 replaces the criteria below. Is this possible ?

ActiveSheet.Range("$A$3:$B$17").AutoFilter Field:=2, Criteria1:="100500"
Range(Selection, Selection.End(xlDown)).Select

View 2 Replies View Related

Multiply Range Of Cells By Cell Reference Without Changing Hard Coded Values?

Aug 20, 2014

I am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.

View 5 Replies View Related

Macro To Insert Data In Cell In Workbook1 For Reference In Workbook2?

Apr 4, 2014

I need to open a customer-submitted workbook which has a list of items in column B, marked by an 'X' in column A on selected rows. Each customer's list is tailored to them with, say, 25 rows each.

I have a master list with identical formatting but which has the data from ALL lists combined.

I want to insert an 'X' in column A of my master workbook for any matching items for which there is an 'X' in column A of the customer's workbook.

I could do this with a formula in column A of my master list, but each customer's workbook has a unique filename and I don't know how to make the formula use a changing lookup array, so I figured this is something that I have to accomplish with VBA.

View 2 Replies View Related

How To Stop Random Numbers Changing

Mar 1, 2012

I generate a range of random numbers in column C. Then I perform another task and then copy the randoms numbers in the range c4:c(some row) into another sheet.

But the two columns are not the same? I want to copy the same random numbers into the other sheet.

'Generate random numbers
Lastrow1 = Range("c65536").End(xlUp).Row
ActiveCell.FormulaR1C1 = "=RAND()"
Range("c5:c" & Lastrow1).FormulaR1C1 = "=RAND()"

'Finding a specific row number and copying random numbers

[Code] ........

View 5 Replies View Related

Stop #REF! Occurring When Changing Spreadsheet

Jun 3, 2008

Is it possible to stop the #REF! error appearing in my formulae when I make a change to my spreadsheet? I have some complicated spreadsheets that take a lot of re-building whenever I need to make changes because of the #REF! error. I tried turning off automatic calculation and that seems to work, until you calculate, then the errors appear. I find myself copying formulae into a text file and then re-pasting back into the spreadsheet after making the changes - there's gotta be a better way! (Using Excel 2003).

View 9 Replies View Related

Stop User Changing Rows

Jan 19, 2007

Is there a general way to disable a user from changing row size or adding new rows.

View 7 Replies View Related

If Cell Is Blank, Stop Going Down The Column

Mar 27, 2007

the following macro works great if there is data below cell O4. but is there isn't any (e.g., at the beginning), then the cursor runs down to 65,536. ?if no data exists, how can i stop at cell O5...

'Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(1, 0).Activate

View 2 Replies View Related

Stop Macro From Changing Range In Formula

Jul 6, 2006

If I have a formula that uses cells that are currently blank - and then run a macro that puts data into those cells - the original formula cell ranges change - even if I put $ within the ranges.


Cell A1 has formula = sumproduct(e1:e10>10)
Currently cells E1:E10 are empty

Run the macro

E1:E10 now has data in.

But formula in cell A1 now reads something like =sumproduct(E13:E23>10) or could read =sumproduct(Ref#>10)

View 9 Replies View Related

Changing Calendar Start And Stop Dates

Jun 20, 2007

The company I work for does not use the usual calendar dates and uses a modified calendar. As an example, the month of January is Dec 31 thru Jan 27, February is Jan 28 thru Feb 24 and so on. I need to group data using a pivot table and summarize data by month, but as I just described above, calendar months will not work. Is there a way to modify what Excel sees as monthly dates?

View 9 Replies View Related

Stop European Date Format Changing

Jan 21, 2008

I have a userform which is used to update a spreadsheet.

There are 7 textboxes which add dates to the spreadsheet. These dates are then used in formulas.

Every time a record is saved the date format changes.

Ths means that if the date is entered dd/mm/yyyy it writes mm/dd/yyyy and this is fine. When this record is next retrieved it will show mm/dd/yyyy and any new date will be dd/mm/yyyy.

Wen saved to the spreadsheet both formats change meaning the dates in the spreadsheet appear in diffrent formats messing up all formulas.

How can I stop the date format changing each time the record is saved?

View 3 Replies View Related

Stop Excel From Changing Numbers To Dates In CSV File?

Jun 6, 2005

When opening a .CSV created from a third party software, one of the columns is converted to date format. The numbers are imported in #/# format. and Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be that big of an issue if I could simply set it back. Clearing format or changing to text converts it to a 5 digit number.

The only way I have found to get it to work properly thus far is to open a blank sheet and import data and setting the column to text prior to import.

I would like to find a way of opening the CSV file without all the extra steps. Is there a setting that can be turned off?

View 3 Replies View Related

How Do You Stop Changing The Date Format During Find And Replace?

Aug 26, 2009

When I remove "Release date:" from "Release date:24 December 2008," excel will automatically change the text to "24-Dec-08." Yes, I did try to format the column to "text" before using find and replace.

This wouldn't be a problem, except that half the dates are in Dutch, so only half the dates are changed, which means that I can't make all date formats in a column uniform.

View 2 Replies View Related

Stop Users Changing Computers Time/Date

Aug 31, 2006

I am creating time sheet application , obviously i need to store the time when the user logs & logs out .. The issue is, the user can "Cheat" by changing the system time ...

Any alternatives?? Is it possible to store the time from a particular server etc or some other source.

View 9 Replies View Related

Reference To Cell Column Letter By Reference To Another Cell Value

Apr 25, 2014

Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.

View 9 Replies View Related

Macro To Go Through One Column Until It Hits Blank Cell And Stop?

Mar 25, 2014

I need a macro to go through one column until it hits a blank cell and stop... the criterea of the loop:

if cell = "Multi" then **Move to X tab** else **move to X2 tab**

View 1 Replies View Related

Excel 2007 :: Stop Links Changing When Use SaveAs To Create Backup?

Sep 25, 2013

I have two spreadsheets that are linked in Excel 2007. They both hold very different information about the same projects, so each row shares some information. However, they are both very large and I do not want to combine them in case the whole spreadsheet becomes unusable. I'm not allowed to use a database

When I save Spreadsheet 1 in a different location, so that I have a backup of the precious data, the link in Spreadsheet 2 changes to show that new location. I want the link to stay at the old location.

Is there a way to stop the link changing when I use SaveAs, so that when I open Spreadsheet 2 it refers to the original location of Spreadsheet 1?

View 2 Replies View Related

Stop Inserting Entire Row, Only Insert Range

Mar 24, 2009

Right now, the code below is inserting an entire row when it pastes the values in I only want the values to be inserted in Range (A:AJ).

View 5 Replies View Related