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

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

Apr 4, 2008

I'm using a template that has formulas using a \$ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:

becomes

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

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

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

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

....

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

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

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

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

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

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

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

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

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

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

## Changing Formula To Reference Spreadsheet From External Excel Document

Nov 22, 2011

how to change the formula below which is referencing an internal worksheet, to a forumula that will reference an external worksheet that is located in an excel document on the G: Drive.

So what it's doing is totaling the counts in the 1210laradj worksheet that match columns HMO-ALT SELECT as well as the numbers 3,5,9 etc.

Well now these worksheets will be on a seperate excel spreadsheet located on a network drive, so I am trying to figure out how I can fix this.

I tried adding it in like so, but it fails.

## Using A Value To Adjust A Cell Reference

Oct 18, 2008

I have a spreadsheet set up for my son to roll up D&D characters. There's a combo box to select Race and another for Class of character. What I now need to do is automatically insert the correct set of racial and class bonuses into the relevant squares.

The Combo box points to a list of Races on Sheet 0, in cells A3 to A12. The cell reference for this returns a value between 1 and 10 depending on race selected, i.e. 'Dragonborn' yields a 1, and 'Tiefling' yields a 10. Cells H3 to H12 contain the Strength modifier for the different races.

When I select for example a Dragonborn (1) in the combo box, I want the Strength Modifier cell to pick up the value from cell H3, and if i change the selection to Tiefling (10) I want the strength modifier cell to pick up from (obviously) cell H12

How do I use a variable in the cell reference to adjust the cell data is retrieved from?

## Sheet Reference In Formula?

Jul 30, 2014

I have this formula (which will soon be much more complex...):

=COUNTIF('dt-a'!C2:C194,'what they want for each teacher'!D1)

I would like to be able to replace the sheet names with a reference to a cell which contains the sheet names.

This is so that a user can enter the sheet name in a cell without having to modify the formulae, break the formulae, and then come running to me ranting about things not working and how computers never work properly.

## Reference A Sheet In A Formula By Number, Not By Name

Dec 13, 2008

I have the following formula: =IF(ISBLANK('WP'!D4), "", 'WP'!D4). where WP is the name of the sheet. I need to reference the sheet not by name, but by it's number (for example WP could be my second sheet, but tomorrow the name of my second sheet could change to WT).

## Format Formula Reference To Different Sheet

Jan 15, 2014

I have a workbook with several sheets, this question focuses on two sheets. 'Coded Prehospital Data' and 'Missing Pre Hospital Data', for brevity here i will refer to them as coded and missing respectively.

In coded the cells are populated using an index/match fx to another sheet and if that returns an error the fx looks to the same cell in missing for a value

Here is an example of the formula in coded cell C2:

=IF(OR(ISERROR(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE))),
ISBLANK(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))),VLOOKUP(
'Missing Pre Hospital Data'!D2,AGEUNITSCODE,2,FALSE),INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))

Without having to constantly switch between sheets I would like to set up a conditional format that fills a cell in missing yellow when that cell is an error in coded.

This is what I have done to achieve this with no success:

1) Select cell C2 in missing and add rule based on formula
2) Enter =OR(ISNA('CODED PREHOSPITAL DATA'!\$C2),ISBLANK('CODED PREHOSPITAL DATA'!\$C2))
3) Enter the custom formatting I decided on
4) In the "Applies To" box I have done two things: 1) drag the cursor from c2 to an28 which auto fills the applied to dialogue box with 'Missing Prehospital Data'!\$C\$2:\$AN\$28 and 2) Free type in the dialogue box 'Missing Prehospital Data'!\$C2:\$AN28

My problem is that I need the formatting for each cell in missing to refer to its sister cell in coded but it continues to refer only to coded c2

## Highlight Formula Reference On Another Sheet

Aug 14, 2008

Normally when you double-click on a formula you see its component cells highlighted.
But when I do this with a formula that uses cells from different worksheets NO cells are highlighted.
I wonder if anyone knows how to get the highlighting working for such formulas. Or is there an add-on which enables this?

## Formula Using Sheet Reference In Cell

Jan 21, 2008

After having searched for a while I can't seem to find any formula that will look at a cell for reference as to which sheet to go to and return a certain cell's contents.

For example,
in column A, I have the text "Sheet1" and in column B, I need the formula to return the contents of cell B15 from Sheet1, based upon column A.

## Formula Reference To Cell On Another Sheet

Mar 6, 2008

I have Excel 2007, here's what i want to do. I've got a workbook created with several worksheets in it. I want to input the name of a project in say, cell A1 on sheet1. Then on sheet2 cell C4, I want the text I input into the afore mentioned cell to show. I know how do do this with numbers (using the autosum button), but I can't figure out how to do it with text, though it's probably very simple & I'll be embarassed when I get an answer. Following is EXACTLY what I'm trying to do if the above example is confusing.

I have a Project Cost sheet and on it I input (text) a description of a building to be constructed. The cells next to it will be the associated costs of that particular building. There will be several buildings listed on the sheet. On a Sales Figures Sheet in the same workbook, I want to list those same buildings and then their corresponding sales prices. I want these descriptions to be automatically pulled from the costs sheet, so that I only have to input the sales figures.

## VBA To Add Sheet Reference Formula To Cell

Aug 13, 2008

Im trying to copy a formula to a cell via VBA.

Range("A3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!A3"

Ive tried all sorts of ', ", &, combinations, I cannot find it.

## Sheet Cell Reference And INDIRECT Formula

Jun 9, 2009

I have this formula in excel 2007:

Blad is Dutch for sheet, by the way.

In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2.

W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9.

I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors.

## Code To Add Sheet Formula Reference To Column

Aug 22, 2008

I need to copy a range of values from sheet 3 to sheet1. When I recorded macro, I got the below code. But, instead of RC in the 4th line <ActiveCell.FormulaR1C1 = "=Sheet2!RC"> I need to pass values like A1,A20 etc.

Since the values range & column to be copied would be varying dynamically, (say for first iteration it would be A1: A20 & for second iteration, I need to copy C1: C20.) how to pass these variables to the macro and use it instead of the static "RC". Whatever column I specify, it should copy from that <column>StartRow to that <column> EndRow.

Sub Updtval(StartRow As Integer, EndRow As Integer, f As Integer)
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault
End Sub

## Copy Formula To Different Worksheet & Reference That Sheet

Oct 22, 2007

Currently I have a worksheet which is organized to have two pages on it. I will try to explain, i.e. imagine one page of the worksheet in the hypothetical range of A1:D26, then another page in the range E27:K50. So it is staggered on the same sheet. Is there an easy way to copy the range of E27:K50 onto another worksheet.

Aside from the regular copy and paste, I am having problems because there are formulas in the 2nd range (E27:K50) which loose their references once copied.
i.e the formula SUM(A1:A10) I would like to be SUM('Master List'!A1:A10) on the new sheet. Is there an easier way then going through all the formulas and adding the master list part.

## Macro - Paste Formula Into Another Worksheet Using Active Sheet Name As Reference

Jan 9, 2014

I'm using a macro to paste a formulae into a destination cell on another worksheet.

The problem is that I need the Macro to put the name of the active sheet into the formulae

VB:
Sheets("Front").Select
Range("E1").Select
Selection.End(xlDown).Select
Range("E3").Select
{"ft_lang_label":"__label__en","ft_lang_prob":0.87417763,"math_prob":0.6313274,"size":16821,"snap":"2019-26-2019-30","text_gpt3_token_len":4612,"char_repetition_ratio":0.15757865,"word_repetition_ratio":0.02190039,"special_character_ratio":0.273111,"punctuation_ratio":0.12964989,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9802593,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-07-15T18:50:24Z\",\"WARC-Record-ID\":\"<urn:uuid:f172a1df-042c-4249-91e0-66c60938b858>\",\"Content-Length\":\"49221\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:bc92bdf8-0fac-47e6-bfee-388fb1d51d2b>\",\"WARC-Concurrent-To\":\"<urn:uuid:09a9e971-02e3-45c2-ad3f-1b3de9cbbd6a>\",\"WARC-IP-Address\":\"130.211.100.39\",\"WARC-Target-URI\":\"http://excel.bigresource.com/Adjust-formula-to-allow-for-changing-sheet-reference--i2nIV5eq.html\",\"WARC-Payload-Digest\":\"sha1:5EUCRZU3HLB7NW4HQXB7HNLE5ZSE4MDL\",\"WARC-Block-Digest\":\"sha1:6QGQHU5E3IGVXFVQHNBHIWPZFSVSTLG6\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-30/CC-MAIN-2019-30_segments_1563195523840.34_warc_CC-MAIN-20190715175205-20190715201205-00498.warc.gz\"}"}