Copy Down An INDIRECT Formula
Sep 18, 2007When I try to copy this formula down, it stays the same and won't reference the cells.
=INDIRECT("PO_Order_Form!B5")
ADVERTISEMENT
Excel 2010 :: Copy And Paste INDIRECT Formula?
Aug 2, 2013I'm working in Excel 2010 on a sheet that will be compiling data from a different worksheet based on the current month. I'm using the formula:
=IF(INDIRECT($K$7&"!C45")>"",INDIRECT($K$7&"!C45"),"")
to complete the field using the other sheets value if it's not empty, where $K$7 references a worksheet name based on the previous month.
It's working just fine, but I want to copy and paste it into other cells having the referenced cell, C45, change as I move it around the sheet (if I copy and paste it 3 cells to the right it would become =IF(INDIRECT($K$7&"!C48")>"",INDIRECT($K$7&"!C48"),"").
I haven't been able to find a way to make the C45 cell reference dynamic while copying and pasting. It would save me a ton of time if I could.
Develop An Indirect Indirect Validation Drop Down List
Aug 18, 2009I am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.
In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.
Copying Down An Indirect Formula
Aug 20, 2009How can I copy down an indirect formula? When I copy it the lookup reference doesn't change. My formula is: =IF(INDIRECT("Q1")="",INDIRECT("R1"),INDIRECT("Q1"))
but when I copy down the cell reference stays the same (I need to keep the indirect formula because I'm adding columns in column Q but it needs to reference column Q even when columns are added). From reading through some other posts I believe I need to add a ROW() or COLUMN() formula in there somewhere.
SUMPRODUCT With Indirect Formula
Jul 15, 2013I am trying to sum the same data across several tabs but I am using information in the column and row headers to find it. For some reason this formula wont work and I can't figure out what I'm doing wrong.
=SUMPRODUCT(N(INDIRECT(""&Sheets&"!"&ADDRESS(MATCH($A$12,$A:$A,0),MATCH($G$3,3:3,0)),FALSE)))
I'm getting #REF! and when I step through it, it fails when it reaches the Indirect statement.
Array Formula And Indirect
Aug 27, 2007getting an array formula to work with my indirects....
View 9 Replies View Related#VALUE Error In Indirect Formula
Sep 24, 2008rate2008
rate2007
...
And I want to show this data in a table, where I have the years 2008, 2007, ... in cells A1:A10 and the formulas =INDIRECT("rate"&A1), =INDIRECT("rate"&A2), ... in cells B1:B10.
For some reason, I am getting nothing but #VALUE! errors in my indirect formulas. In fact, even if I take out the indirect and just have ="rate"&A1, ="rate"&A2, etc., I still get the errors. It seems like the problem is with the & operator. This only seems to be a problem in this certain workbook; I am able to get the desired results if I open a new workbook.
#REF! And #VALUE! Error With INDIRECT Formula
Jul 25, 2006when using the following forula as below; =INDIRECT(INDEX($B$37:$B$62,$B$3)&"!"&ADDRESS(ROW(D6),COLUMN(D6))). centain cells come up with #REF! and or #VALUE!
View 6 Replies View RelatedAdd Indirect Formula Via Macro
Oct 26, 2006I need to create a formula for a series of ranges that have a variable sheet name (which is located on sheet Backend!E15) and when it creates the formula will reference the exact same cell on the variable sheet.
this is what i have so far...
Option Explicit
Sub formulaset()
Dim Cell As Range
Dim target As String
For Each Cell In Range("b4:al132")
Application. ScreenUpdating = False
target = Cell.Address
Cell.FormulaR1C1 = "=INDIRECT(CONCATENATE(BackEnd!E15,""!"",target))"
Application.ScreenUpdating = True
Next Cell
End Sub
but this is the answer I am getting in the first cell of the range...
=INDIRECT(CONCATENATE(BackEnd! 'E15',"!",target))
as you can see I am having trouble getting the target address to lock in. To make things worse, its needs to be in " " so the concatenate creates the corect address link.
Alternate Formula For Sum Indirect
Feb 14, 2007I have tried to apply '= SUM(INDIRECT("A2:A10"))' formula to do the SUM at cell A11. But, if I add two more Rows, then my formula moves down to cell A13 but numbers in Cell A11 and A12 does not get added to the total. How can I avoid that? I have reserached this site extensively and could not find an archived solution.
View 6 Replies View RelatedFormula To Increment Indirect References
Mar 5, 2009I have a sheet which needs to look up one reference and then fill a table with the rest of them.
EG:
Cell A1 contains '0091 911'!$E$2 (cell E2 contains value 100)
Cell A2 contains =indirect(A1) and displays value 100
I need a formula which will auto fill the remaining cells in the table.
eg:
Cell A3 fills to contain '0091 911'!$E$3 (row +1)
Cell B2 fills to contain '0091 911'!$F$2 (column +1)
so it needs to fill the Indirect reference and not =indirect(A1),=indirect(A2)....
'INDIRECT' Formula Across Excel Versions
May 22, 2009I have made a file that works perfectly in excel 2007, but when I send it to a client it doesn't work as they have 2003.
View 12 Replies View RelatedAdapting Offset Indirect Formula?
Nov 7, 2012Although I have seen lots of formulae to total a dynamic column, I found a brilliant formula on the Internet, as follows
HTML Code:
=SUM(OFFSET(INDIRECT("R1C",),,,ROW() -1))
The problem is I cannot work out how to adapt it to total across rows (which will expand with new columns).
SUMPRODUCT INDIRECT & SUMIF Formula
Jul 12, 2006I have a set of data as attached where the productivity of each staff is recorded on a daily basis.However, the cell in which their name is located on each tab of the day of the month might not be the same. I am thankful that the forum actually provided me with the formula located in cell c15.
1)However, I would appreciate if someone could translate what the formula means as I am not so sure what the formula represents.
2)The reason is I would like to calculate how many training days (TR), Medical Leave(ML) and Emergency Leave(EL) for the month of January for each staff.I tried modifying the formula but it didnt work.
3) Is it possible to use a vlookup function for this solution?
Indirect Function In Picture Formula
Mar 31, 2007I'm in the middle of making a yahtzee game in excel. Does anyone know if it is possible to use the indirect function in the formula of a picture. I have embedded 6 bitmap images of dice on sheet 2 (worksheet name: "Dice Images"). I took a picture of one of those dice and pasted that picture on sheet1. The formula in the picture is: ='Dice Images'!$A$1
This means that it is refering to cell A1 of the "Dice Images" worksheet of which contains the pasted bitmap of the dice image. I was wondering if I could use the INDIRECT function instead of the formula above but the following returns an error:
=INDIRET("'Dice Images'!$A$2",true). It returns the following error: "The text you entered is not a valid reference or defined name". It doesn't seem to like the INDIRECT function for some reason, unless I am doing something wrong.
Indirect Formula For Sheet Name & Range
Jan 10, 2008I have a formula: ='Jan 7-11'!D10. I want to replace the tab name 'Jan 7-11' to change based on the content of F6 (which is a data validation for all tabs in my workbook).
View 3 Replies View RelatedRelative Indirect Formula To SUM Range
Feb 13, 2008I am using VB in Excel to write a formula for summing a column of numbers that will vary in length. I have been using the Indirect formula and have the start and end cells of the range identified in another location. My problem is, the macro in vb is sending the Indirect formula to the cells containing the start and end points by using relative functions ie. go across 30 columns and up 10 rows to get start cell. I wanted it to just save the cell location of the start and end points.
EG.
VB formula is: "= SUM(INDIRECT(R[-23]C[27]&R3C29):INDIRECT(R[-23]C[27]&R3C30))"
whereas I just want it to read "=Sum(Indirect(C4&D5):INDIRECT(C4&D6))"
where cell C4 gives the column letter and D5 the row number of the start of the range and C4 and D6 give the column letter and row number of the end of the range.
Non Volatile Replacement For INDIRECT In SUMIFS Formula
Dec 4, 2013Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.
My original formula was:
=SUMIFS(INDIRECT("'"&A2&"'!K4:K500"),INDIRECT("'"&$A$2&"'!B4:B500"),"=LEFT(AA1,2)",LEFT(INDIRECT("'"&$A$2&"'!C4:C500"),3),"=MID(AA1,7,3)")
However I kept getting an error and excel said that INDIRECT("'"&A2&"'!K4:K500") was volatile, so I tried to replace it with INDEX and my formula was:
=SUMIFS(INDEX(A2:A2,1)&"'!K4:K500",INDEX(A2:A2,1)&"'!B4:B500",LEFT(AA1,2),LEFT(INDEX(A2:A2,1)&"'!C5:C500",3),MID(AA1,7,3))
However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.
Sheet Cell Reference And INDIRECT Formula
Jun 9, 2009I have this formula in excel 2007:
=COUNTIFS(Blad1!W:W;"1";Blad1!X:X;"H")+COUNTIFS(Blad1!W:W;"6";Blad1!X:X;"H").
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.
Dynamic Cell Reference In Indirect Formula
Oct 11, 2011I need making a dynamic cell reference in an indirect formula, which looks at a different workbook.
The other workbook is called: ESF.xlsx
The sheet name in the other workbook is defined in Cell B2
The first cell i need to pull across is B115.
In cell B8, This is what i have so far:
=INDIRECT("[ESF.xlsx]"&B2&"!"&"B115")
How do i make it so that i can copy this formula quickly, but so only the row and column reference change?
Like in C8 i would want it to look at cell C115, In C9, id want it to look at C116 etc.
Changing Indirect Reference When Dragging Formula
Dec 9, 2012I 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.
Indirect Function Placement In Array Formula
Feb 27, 2014The following formula is all on one line. I want to do indirect for file name and tab in all instances it shows up. (I want to have the filename in one cell and sheet/tab name in another cell to reference the indirect to.)
How would I use indirect in the formula to reference the cells?
=INDEX('[Feb 2014.xlsm]TRADEDB'!$A$75:$Q$131,
SMALL(IF(('[Feb 2014.xlsm]TRADEDB'!$A$75:$A$131=$H$8)*('[Feb 2014.xlsm]TRADEDB'!$G$75:$G$131=$I$8),
ROW('[Feb 2014.xlsm]TRADEDB'!$A$75:$Q$131)),ROW('[Feb 2014.xlsm]TRADEDB'!2:2))-74,1)
Using Indirect Formula To Pull In Data From Different Worksheet
Jul 7, 2014I am trying to use the indirect formula to pull in data from a cell in a different worksheet using the tab name as my reference. For example:
I have my tab name in cell A2, then I am using this formula to pull the information from cell g29 on the tab listed in A2, but its not working:
=INDIRECT(A2&”!”&”g29″)
Also, my tab name in cell a2 is a date, does that make any difference? I've tried a couple variations on the indirect formula and have gone from the #REF! error to the #NAME? error.
Variable Cell Reference In INDIRECT Formula
Sep 28, 2006I want to create a formula of the form: =INDIRECT(ADDRESS(4,2,2,TRUE,($A9) and insert this in a cell BUT the $A9 reference needs to reference the row of the cell where the formula is being inserted which will vary. ie. A30, A31, etc. How do I do this?
View 2 Replies View RelatedIncrement Indirect Formula Column Reference
Dec 3, 2007COPYING indirect formula. When I copy, the lookup reference does not change. My formula is =INDIRECT("'"&$A247&"'!j106"). When I copy horizontally across cells, I want J106 to increase, ie j107, j108 etc. At the moment it remains at J106 and i have to manually increase the numbers by 1.
How do I change my formula so that the numbers increase automatically?
How To Update Cell Reference When Dragging - INDIRECT Formula
Jul 10, 2014 I am using the formula below to sum across several worksheets, and the formula I am using works fine, but when I copy the formula across to other cells, I can't figure out how to have it update my cell range:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!G:G")))
The range A:A is ok to be static, but I need the G:G to be dynamic so when I drag it to the right it will change from G:G to H:H. I:I, etc.
Getting Indirect Formula To Update Cells References When Copied?
Mar 18, 2014As you would normally use indirect formulas so the cell references don't change. Which that is what I want in the end, but I need to copy them to an indefinite number of cells first and would like to not do it by hand. I have found some solutions to similar questions/problems but cannot figure out how to make them work for me. So, what I am looking to do is this... (I have also attached the spreadsheet for reference)
I have gotten the information in columns A through F on the first sheet to update as rows are added, moved, deleted on the second sheet using Indirect range. Also, I could do this for Column I (Copmleted Proj. Avg. Terminations) but I would have to do it manually (as I began doing in I3, I4 & I5) but that would be time consuming. So I am hoping there is a way I can copy the formula down the cells are updated for the initial copy but then don't update if the referenced cells are moved or deleted.
Indirect Formula Syntax For Referring To A Range On Two Worksheets
Jul 5, 2012B1=Name of WorkSheet
B2=Name of 2nd Worksheet
I am trying to write a sum formula using Indirect so that the end-user can enter the names of the sheets in B1 & B2 (the values are always in cell F5 on each sheet). I thought this would work but it is throwing a REF# error.
=SUM(INDIRECT("'"&B1&":"&B2&"'!F5"))
with some added spaces for clarity:
=SUM(INDIRECT(" ' " & B1 & " : " & B2 & " ' ! F5"))
Excel 2007 :: VLookup Formula With Indirect Function
Mar 19, 2013Code:
=D5-VLOOKUP($C5,$C$45:$F$80,2,0)
I have formulas such as above in my Worksheet. So the above is in Cell H5
For the Range $C$45:$F$80, I am inserting rows (So moving down the data) and copying data from $C$5:$F$40 as values into the new space in $C$45:$F$80
I am doing the above using a macro, but when I run it I want this
Code:
=D5-VLOOKUP($C5,$C$45:$F$80,2,0)
to stay static, but instead I end up with
[CODE=D5-VLOOKUP($C5,$C$85:$F$120,2,0)[/CODE]
So my Table Array $C$45:$F$80 changes to $C$85:$F$120
How can I keep it as $C$45:$F$80
The other references in the formula seem to stay as I want them.
COUNTIFS And INDIRECT Function - Formula Not Working And Keep Getting Error Message?
Apr 25, 2014Because countif cannot be used across multiple tabs, I'm using the following workaround where "MySheets" is the range of tabs and E8 is the cell I want "counted".
=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented"))
This works perfectly, except that I really need COUNTIFS... I want Excel to count either "Fully implemented" or "Partially implemented" when in E8. I cannot get the formula to work and keep getting an error message.
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented",(INDIRECT("'"&MySheets&"'!E8"),"Partially implemented")))