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


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?

View 2 Replies View Related

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

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

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

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

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

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

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

Changing Values In Column Based On Value In One Cell

Jan 12, 2009

Based on the attached (much simplified) spreadsheet, I want the values in the green cells on the "Month" page (B6:B10) to vary depending on the value in the yellow cell (B4), and for these cells to to look up the relevant column in the "Full Year" page - i.e. if B4 (on "Month") = Jan, look up C6:C10 in "Full Year"; if B4 = Feb, look up D6:D10, and so on. I know I can nest IF functions in order to achieve this, but could be a bit messy. I'm sure there must be a simpler formula within Excel ... just don't know what it's called!

View 3 Replies View Related

Reference Worksheet With Changing Name

Aug 23, 2007

I want to select a specific worksheet by it's VB name. In other words, if the sheet comes up as: Sheet3 (ESF) in the VB Editor, I would like to choose it using the Sheet3 designation. I can do it using the (ESF) designation using:


But I prefer to use Sheet3 so when the users change the name, the macro still works.

View 6 Replies View Related

Reference Changing Files In VBA

Dec 14, 2007

I have recorded a basic macro that sources data from a number of workbooks.

If I change the filename in the workbook with the source data or move this file to a different folder path the macro does not run without errors.

Does anyone know what vba code I can use to address this problem?

View 6 Replies View Related

Reference Every Nth Cell In A Column

Aug 26, 2009

I have a data entry spreadsheet that contains 120 data entry panels each of 10 rows. Each panel is separated by 3 rows (1 of which is hidden). When entering data to the panel the user is required to enter a date in column F of the data entry panel. The number of used rows in each data entry panel can vary from 1 to 10.

The entry of a date in the first Column F cell in each entry panel stamps today's date in Col F in the first row below each data panel (the hidden row) and that date is then linked to a separate summary sheet to show the date of data entry. The code I'm using for the date stamping routine is below, and although it works it is painfully slow and I'm sure very inefficient. I've had to break the code into the three sections as shown below because there seems to be a limit to the number of individual cells I can reference in each of the range statements. (Is there a limit or is the length of the range statement causing some other problem?)

View 4 Replies View Related

Pivot Table With Changing Reference.

Jun 23, 2009

I'm trying to take data from multiple worksheets and copy them into a summary sheet in the same workbook, and then make a pivot table from the summary sheet.

I got the first part done, but I can't figure out how to make the pivot table to incorporate new data in the summary sheet as new worksheets/data is added.

View 2 Replies View Related

Change Formula Without Changing The Reference

Dec 5, 2006

I have a set of formula in column C to H. I have set a formula as sheet1+sheet2+ like this for about 24 sheets with different reference of cells. I want to change in the entire range of cells C to H as =sum(sheet1:sheet24!b04). The reference what I have mentioned changes in different cells.

View 9 Replies View Related

Formula To Reference Changing Name Of Table

Feb 2, 2010

I am trying to run a quick format on a table that includes adding a Vlookup formula. The formula references a table that comes out of a pivot table, so it has a different name every time. If I look up the new generated table name, and change it below it works. Is there a way to capture the table name and insert it into the formula? Or possibly rename the table to the same thing everytime (which I think is going to cause some conflict).


View 9 Replies View Related

Changing Text In One Cell Creates Formula Change In Column Below?

Apr 1, 2014

I have a list of pricing and I want to be able to change the text of cell, say "D1", which says "Standard" to say "Premium" and all the formulas would change in accordance. The "Standard" pricing would be, Starting D2, "=C2*.3" and I want them all to go to "Premium" pricing which would be formula "=C2*.35".

What would be the best method of creating this function or formula?

View 3 Replies View Related

Keep Reference To A Cell When Column Is Deleted

Nov 28, 2007

I have a spreadsheet in excel with scheduling information, each group of data (job) is made up over 2 columns and 6 rows and is in a block (this is so it is easy to view and manouvre)

I have a link in another worksheet that extracts information from the cell if a criteria in the group is met!

At the end of each day I need to delete the columns (B and C, which make up that day) in the schedule.

When I do this all the references in the other worksheet are messed up, even though the next day is moved to columns B and C.

I would like the link to the other worksheet to automaticaly recognise the new values in column B and C as the reference.

View 9 Replies View Related

Layout Cell Reference Column

Jul 8, 2009

I am not sure how this is possible without doing copy pate transpose or individually refereing each cell to source. I would like the heading to be presented as in sheet1 which is sourced from Source sheet but if anychanges are made to source it updates the Sheet1. I have attached an example to illustrate. (Headers are not fixed number)

View 3 Replies View Related

Column Reference To Cell Transfer

Jan 18, 2010

I am trying to get information from 1 sheet to another. What i need to do is take the information from sheet 2 column H2:H26 and put it in sheet 1 cell I4. i already have the vlookup information in the other cells that i need but for what i need this for is different than a vlookup. basically i want to be able to type in a name (from sheet 2) in cell I4 (sheet1) and all the information that i need auto populates for me like i already have. I was able to do this with a drop down menu but that wont work as the information will change weekly,

View 11 Replies View Related

Function For Reference Of Cell Row/column

Mar 5, 2009

I have a column of numbers, on which i would like to perform subtraction, in a way that the cell in the (i)th row will be subtracted from the one in the (i+x)th row, while x is a parameter that the user can change as he wishes.

My problem is how to refer to the cell address and still use this conditional reference...
If anyone has an idea how to realize this calculation (which should be peanuts in Matlab, but apparently more problematic in Excel),

View 11 Replies View Related

Change Column And Cell Reference Name?

Apr 25, 2014

I have a massive excel sheet with 300,000 rows and 100+ columns. When reading back through my formulas, it can get very confusing. Example: "=(AZ9*5 + 1)+BH9/2 +(AP9*0.75)" I then have to figure out what each column letter is representing. Instead of this, could I rename a column so I can reference the variable name? Example:

Instead of =CONCATENATE(A4, " ", B4)


=CONCATENATE(FirstName4, " ", LastName4)

View 3 Replies View Related

Reference Column To A Single Cell

Jan 11, 2007

I'd like to reference Column A to a single cell in a different sheet.


Returns the value of A2 in the sheet called StoresServiced to another sheet.

What I want to do is, the values from A2 to A102 to show in one cell, separated by commas. So if theres value in A2 (8009) and A3 (8010), I want the active cell (ex. M43) to look like this -> 8009,8010 -- so on and so fort

View 9 Replies View Related

Copyrights 2005-15, All rights reserved