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


ADVERTISEMENT

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

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

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

Code:
=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 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 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 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

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

Sheets("ESF").Select

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

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

Copying A Formula Without Changing A Part Of The Reference

Aug 4, 2009

I have this If statement on one of the cells

=IF(A7=AQ6,AQ32,IF(A7=AR6,AQ32,IF(A7=AS6,AQ32,IF(A7=AT6,AQ30,IF(A7=AU6,AQ29,IF(A7=AV6,AQ33,IF(A7=AW6 ,AQ33,IF(A7=AX6,AQ31,""))))))))

If I want to change only A7 to A8, A9, A10 without changing the other parts of the formula, how do I do that. I need to copy this formula in about 300 rows.

View 3 Replies View Related

Adjust Formula To Allow For Changing Sheet Reference.

Nov 22, 2009

I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating.

=IFERROR(INDIRECT("'"&COLUMNS($A$1:A$1)&"'!P"&ROWS($A$1:$A8)),"")

I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect?

=IFERROR(INDEX('1'!$A$8:$AT$115,MATCH($A3,'1'!$A$8:$A$115,0),MATCH("PAT",'1'!$A$7:$AT$7,0)),"")

The sheet reference will change from '1' through '90'.

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

A
B
1
Martha

[Code]....

View 5 Replies View Related

Changing Indirect Reference When Dragging Formula

Dec 9, 2012

I have been using the following formula to do conditional summing over multiple worksheets:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$AB$2:$AB$13&"'!b3:b39"),$B16,INDIRECT("'"&$AB$2:$AB$13&"'!c3:c39")))

The formula works fine, but when I drag it to the next column c3:c39 stays the same. How do I get that reference to change when I drag the formula over multiple columns.

View 1 Replies View Related

Relative Reference Formula Not Changing Until Re-Entered

Dec 28, 2006

When I copy the formula to another cell, It doesn't show the result. But when I double click on it, it works. For example

there r values in A1 as "john" & A2 as "Jack". I write formula =A1 in cell B1. It shows "John". Now I copy this formula to B2, it shows "john" instead of "Jack" . When I double click on B2 & enter, it shows "Jack".

View 2 Replies View Related

Copy Formula From 1 Workbook To Another With Reference Changing

Apr 17, 2008

I want to copy a formula from Workbook A to Workbook B and have the formula configured with Workbook B's spreadsheet cells, not Workbook A's.

View 2 Replies View Related

Function/Formula To Reference Changing Rows

Aug 13, 2008

I have a table of consolidated data that consists of the date at which certain payments occur and their amounts that other sheets need to reference to perform calculations. I am currently using the formula below to put the data under the correct date column. =INDEX(ImportedData!$B$10:$DB$10,MATCH(B3,ImportedData!$B$4:$DB$4,FALSE)). The problem that I have is that the INDEX range, currently B10:DB10, will need to change depending on the number of files that are consolidated ie with more files the row will increase.

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

Dynamic SumIF Formulas That Reference Changing Codes

Dec 1, 2007

I have a list of codes from which I'm trying to extract all unique entries.

For example, col A has 001, 001, 002, 003, 003, 003, 004, ...

I'm trying to create a report template with formulas that reference col A and return a single unique entry for each code so that the new column has only 001, 002, 003, 004.

Essentially, the codes reference customers. A customer can have multiple purchases -- but I'm trying to create a report in which I can utilize SumIf formulas to Sum the purchases for each customer and represent those purchases on a single line per customer code.

The catch is that the purchases change monthly (i.e., next month the purchases may be 001, 002, 002, 002, 004) so my report template needs to have the flexibility to adapt without me rewriting the SumIf formulas and return SumIf results for only customers 001, 002, 004. Whereas in the prior month, the report returned SumIf results for customers 001, 002, 003, 004.

View 9 Replies View Related

Identify Circular Reference (Changing Ranges, Vlookup(), If() And Sum())

May 28, 2007

Changing Ranges, Vlookup(), If() And Sum()

This is the link to the thread where ByTheCringe2 showed me the power of SUMPRODUCT and sorted my problem out. I don't think you'll need it, but I'd rather reference it before Roy reprimands me! lol

I am building on the same topic, though.

Hi All!

I have an investment where the monthly interest is added back to the capital. The next month earns interest on the higher figure and this higher interest is again added onto the capital and the story starts all over at the next month. OK, that's easy to do (Again, I think!).

All works fine UNTIL the end of the Tax year is reached and Tax on the interest earned is payable from the investment as the capital carried forward is reduced by the Tax payable. In the attached file I show how I calculate the interest earned for each following Tax year (which always ends at month 2) using SUMPRODUCT. Sumproduct is used because I never know whether the initial investment is made in month 5, 11, or any other month and by using certain controls (thanks ByTheCringe2!) I can simply total the interest earned in each tax year - the months between month 3 (start) and 2 (end).

On a seperate sheet I calculate the Income Tax payable as follows:

Firstly, I calculate the standard Tax payable on the investor's income from other sources. I then add the taxable portion of the interest to the standard income from other sources and calculate the tax payable on that combined figure. I then deduct the two figures from each other and get the increase in Tax payable because of the interest earned.

This "increase in Tax payable"-figure is then returned to the spreadsheet on which the interest is added onto the capital. See Sheet2! cells O9:O15. The Tax payable for each Tax year is placed next to taxable values I calculate using SUMPRODUCT (again!) in column N9:N15.

VLOOKUP is then used to transfer this Tax payable figure into the calculations (Column K9:K68) as and when required (only when tax is payable) - and should be deducted from the value in (Same row) Column H and that answer should be transferred to next row Column E (Balance at beginning of month) -

BUT it causes CIRCULAR REFERENCE faults and I am absolutely frustrated at not being able to figure out how to sort it out!

Sheet2, Column E9:E69 (See Sheet2!E19 for Comment explanation) in the attached workbook.

View 9 Replies View Related

Change Table Array Reference Without Changing Formula In Every Worksheet?

Jun 25, 2014

i want to change the table_array reference without changing formula in every worksheet. I tried using a new worksheet and naming it the same as what is referenced in vlookup table_array but it messed up all the data.

I have to use a different worksheet every month so need a way to change reference OR how to change array data without messing up the worksheets with the vlookup

View 9 Replies View Related







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