Attached I have a document where I am wanting all of the individual sheet names on the Total page. Rather than having to change each formula to match the sheet name I believe there is a way to reference the sheet name column (AO) in the formula so you can drag it down to fill in the columns. I am basic with Excel and do not know how to add this indirect function into my current formulas.
View 5 Replies
I 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.
I have approx 500 workbooks which contain custoemr details and I need to extract certain info (postcode, name, contact, phone number etc) and place it all on 1 spreadsheet. I was originally going to source data from unopen workbooks, but have realized I only need to do this task once, and I can maintain it from then on...
I have created a spreadsheet (attached) which uses INDIRECT formulas to retrieve the data. When I type in the file name in column A, Columns B to K fill themselves in. What I want to know is:
Once I open say, 10 Workbooks, TYPE the name in manually and all the other cells have filled themselves, I want to somehow KEEP the data there, rather than it return a #REF error when I close the workbooks to open the next batch.
Also, if you look at my workbook, to the far right is a range of data which I use in my INDIRECT formulas. Once I have filled all the cells with the relevant data, I want to be able to Filter my spreadsheet by Date Ascending. BUT I cant seem to do it because it includes my INDIRECT range in the filter and mess's up the formulas then I have tried Locking & protecting workbook, with Autofilter & select cells etc TICKED for users to use, but it still tells me to unlock workbook when I try to filter.
I am familiar with the use of the INDIRECT function to retrieve data from different sheets in a workbook. However, is there a simple way of obtaining a list of all sheet names in a workbook (I have about 50) rather than typing them into the sheet individually?
I can see them in the workbook properties but can't copy and paste them.
I have a worksheet with a name that inckudes a hyphen, e.g. 1234-5678.
And I have the worksheet name in a cell, say, I!.
Using
=INDIRECT(I$1&"!YADDA")
I get a REF error because of the inclusion of the hyphen in the worksheet name.
How can I fix this without eliminating the hyphen?
How would I used the indirect function to reference a named range in the workbook in formula creation.
For instance, if in cell A1 I have entered the text "Sales" which is also a named range in the workbook. Then in cell A2 I tried entering the following formula to sum based on the entry in cell A1 using the indirect funtion. For instance:
=sum(indirect(A1))
In this instance I was hoping this formula would then sum the amounts in the "Sales" named range. And, if I changed the text in cell A1 to "Cost" for instance (another named range in the workbook), it would sum the amounts in the "Cost" named range. Allowing for a dynamic formula based on the entry in cell A1.
I'm using Excel 2007.
I 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.
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.
I 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 RelatedCOPYING 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?
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.
Cell F4 contains a worksheet name which was “calculated” by an IF function (IF something, then ‘T2’, otherwise ‘T3’).
Cell F5 must then look at either sheet T2 or sheet T3 (depending on what it is told to do by reference to cell F4 just above) and report what it finds in cell A1 there.
Clearly I must use the INDIRECT function. But I have tried every imaginable combination of single and double quotation marks to produce the equivalent of
=T2!A1
all to no avail.
Is it something to do with letter and number combinations?
Alternatively, how else should I achieve what I want?
PS F4 could have been “calculated” by any other means, e.g. =MONTH(TODAY()).
I'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.
The 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)
Code:
=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.
Because 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")))
VB:
Sub RenameTabs()
For i = 1 To Sheets.Count
If Worksheets(i).Range("C1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("C1").Value
End If
Next
End Sub
I change my tab names with the above but than my hyperlinks breaks.
I have a workbook with multiple sheets. There is an overview sheet and then 14 sheets allowing for 14 days worth of schedule information. There are then 3 sheets following the 14 days to total some information. The workbook users frequently change the sheet names of the 14 sheets to reflect days of the week.
I'm creating a copy for distribution that takes the active workbook and copies the values to a new workbook. There are columns that I would like to delete from the 14 day sheets and that would be easy enough if the sheet names were never changed.
The code I'm using currently follows (my thanks to Turtle 44 for helping on that section)
Sub Copy_Visible_Sheets()
Dim arr() As String
Dim i As Integer
Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Application. ScreenUpdating = False
'Make sure template is saved as .xls
If Not ThisWorkbook.Saved Then
MsgBox "Please save this workbook before generating a Client Copy."
Else
Within one workbook I have 15 sheets, 13 are for separate divisions within the company, 2 are used to present sums across the 13 sheets. All 13 sheets have identical columns and rows, with unique numbers in each cell. Right now I am manually selecting the cells to reference in my master sheet, =sheet name!cell+sheetname!cell+sheetname!cell - so and and so on. How can I reference the sheet name once, and then all of the cells to SUM? =sheetname!(c4+c14+c24+c34) etc?
This would let me be able to copy the formula from cell to cell, and only have to change the sheet name each time.
I 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 RelatedI have a problem using indirect in a counta function. Basically, I want to count the number of used cells in a range. However, there would be insertions to the rows (using the insert row) on top of the range.
my function is now =counta(indirect(" events database"!"A"&M22&":A"&M500)
However, this formula returns an error. What have I typed wrongly?
I want to create a series of VLOOKUP commands with different lookup tables whose names are concatenated from two different pieces. Here's what I'm trying to do:
VLOOKUP(x,LUTWK01,y,false)
VLOOKUP(x,LUTWK02,y,false)
VLOOKUP(x,LUTWK03,y,false)
LUTWK01, etc., are named ranges. I have the text "WK01", "WK02", "WK03" stored in other places, and would like to be able to concatenate "LUT" and the particular week (e.g., "WK01"). The formula that I've created, which isn't working is:
VLOOKUP(x,CONCATENATE("LUT",A1),y, false) where cell A1=WK01.
When I pull out just the CONCATENATE portion, it resolves to LUTWK01, but apparently this is not recognized as a range name. Any idea as to how I make the VLOOKUP formula recognize a concatenated range name? Or is there a better way to do this?
The following formula gives me the error message #REF!
=INDEX(INDIRECT("R7"),MATCH(V6,D8:D11,1),11)
The problem I believe is in the INDIRECT("R7") as the following formula works
=INDEX(Well_AA_09,MATCH(V6,D8:D11),1),11)
The content of cell R7 is the text Well_AA_09 which is the name of a dynamic range I have created and pasted from within VBA into cell R7.
I have a the following function: =INDIRECT(ADDRESS(SUMPRODUCT((F35:G45=K40)*ROW(F35:G45))-21,SUMPRODUCT((F35:G45=K40)*COLUMN(F35:G45))))
I'm trying to put the get the value of it from a different Sheet.
say this function is in Sheet1 and I wish to place the equation in Sheet3 - what should I change? I tried INDIRECT(ADDRESS(SUMPRODUCT((Sheet1!F35:G45=K40)...
nothing works.
what the function does? there are 2 tables. table 1 has values and table 2 has values as well. K40 is the user input --it reflects the number in table2 and the return value is the 'reference' in table1. for example, if table2 in F41 has the number 54 and the reference for it in table1 is $344, when the user types 54 in K40 the returned result in the function will be $344
I need a function where the sheet-reference can be linked to a cell.
By other words can the content of cell A1 be sheet1, sheet2, sheet3 etc. The cell in B2 shall always point to cell b3 in the choosen sheet, but the sheetreference is dependent on the sheet refererred to in A1
Each sheet contains the test data for an individual class and I am trying to create a "Totals" sheet where I can show the average that each class had on a particular test and the letter grade breakdown. I am trying to create one 'average' formula that I may then copy across that will change the sheet reference automatically without me having to manually re-create the formula every time I add a new class sheet. I tried using the INDIRECT function but could not get it to work. Keep getting #Ref error.
View 3 Replies View RelatedI'm trying to exclude cells that are filtered in a separate sheet.
The sheet I'm working with gathers information from this separate sheet using the following SUMIF formula:
=SUMIFS(Claims!$H:$H,Claims!$G:$G,$A9,Claims!$B:$B,C$6,Claims!$E:$E,$B9)
This formula extrapolates information perfectly for me if I do not filter columns in the separate sheet with all of the information.
I know that SUBTOTAL functions can be used in a way to exclude hidden cells. Can I combine the SUBTOTAL function into my SUMIF function above to produce results that exclude filtered cells?
I have a workbook with a Summary sheet and then a series of sheets for each month/year. Onthe summary sheet I use column A for the month/year as a date formatted "mmm yy" ie the same as the sheet names.
in columns B,C,D I want formulae that sums or averages a fixed range on each sheet but I would like the formula to take the sheet name from Columa A rather than hard coding it.
So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the "Dec 04" from the text in cell A3
Is there any way to use a formula rather than a direct cell reference in the Reference argument of an Offset function?
For example, rather than this:
=OFFSET(H3,0,-7,1,1)
I want to do this:
=OFFSET(max(H:H),0,-7,1,1)
Effectively replacing the starting cell of the offset function with a dynamic formula that returns the maximum value of an array.
Reduced to it's simplest form I have a formula
=1*(FY2012_TY), where FY2012_TY
is a Defined Name which refers to a column on another tab in the workbook, and is defined as:
=OFFSET(Table!$AA$3,0,0,COUNT(Table!$AA:$AA),1)
What I would like to do is to come up with an approach to this formula that would allow me to drag it across columns and have the year automatically increment (e.g., FY2013_TY, FY2014_TY, all of which are similarly defined Defined Names referring to adjacent columns). I have tried various approaches using INDIRECT along with CONCATENATE, but when it comes down to evaluating the formula, I consistently get a #REF! error.
