Reference Sheet Names With Indirect Function To Make Formula?

May 21, 2014

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


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.

View 6 Replies View Related

INDIRECT FUNCTION - Make Data Stay Permenantly

Dec 9, 2009

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.

View 5 Replies View Related

Obtaining List Of Worksheet Names For INDIRECT Function

Jun 11, 2008

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.

View 9 Replies View Related

Worksheet Reference In Indirect Function

Feb 22, 2010

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



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?

View 9 Replies View Related

Indirect Function To Reference A Named Range

Apr 30, 2009

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:


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.

View 9 Replies View Related

Dynamic Cell Reference In Indirect Formula

Oct 11, 2011

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:


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.

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


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

Variable Cell Reference In INDIRECT Formula

Sep 28, 2006

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 Related

Increment Indirect Formula Column Reference

Dec 3, 2007

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

View 6 Replies View Related

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:


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.

View 6 Replies View Related

Indirect Function: Use Cell For Sheet Name

Dec 7, 2006

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

View 9 Replies View Related

Indirect Function In Picture Formula

Mar 31, 2007

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.

View 3 Replies View Related

Indirect Function Placement In Array Formula

Feb 27, 2014

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)

View 5 Replies View Related

Excel 2007 :: VLookup Formula With Indirect Function

Mar 19, 2013


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

to stay static, but instead I end up with


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.

View 2 Replies View Related

COUNTIFS And INDIRECT Function - Formula Not Working And Keep Getting Error Message?

Apr 25, 2014

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

View 1 Replies View Related

Hyperlink Won't Keep Reference When Sheet Names Change?

Apr 30, 2014

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

I change my tab names with the above but than my hyperlinks breaks.

View 4 Replies View Related

Reference Sheets With Variable Sheet Names

Dec 4, 2007

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

View 6 Replies View Related

How To Reference Other Sheet Names In Workbook But Keep Cell References The Same

Oct 7, 2013

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.

View 3 Replies View Related

Indirect Formula For Sheet Name & Range

Jan 10, 2008

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 Related

Count Used Cells Formula. Make Reference Static

Nov 5, 2006

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

View 9 Replies View Related

Reference Different Concatenated Range Names In A LOOKUP Formula

Apr 2, 2009

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:


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?

View 2 Replies View Related

INDIRECT : How Do I Make It Work

Jul 2, 2008

The following formula gives me the error message #REF!


The problem I believe is in the INDIRECT("R7") as the following formula works


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.

View 9 Replies View Related

Reference A Function From A Different Sheet

Nov 6, 2008

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

View 4 Replies View Related

Sheet Reference Function

Jul 28, 2009

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

View 2 Replies View Related

Use Sheet Reference In Average Function?

Nov 27, 2013

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 Related

Make SUMIF Formula Calculate With SUBTOTAL Function

Feb 21, 2014

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


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?

View 5 Replies View Related

Use A Formula As A Cell Reference In A Function

Aug 3, 2005

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

View 14 Replies View Related

Using A Formula As The Reference In An Offset Function

Sep 2, 2008

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:
I want to do this:

Effectively replacing the starting cell of the offset function with a dynamic formula that returns the maximum value of an array.

View 9 Replies View Related

Indirect Reference To Defined Name

Feb 17, 2012

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:


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.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved