# Use INDIRECT To Refer Sheet Name

May 9, 2014

I have multiple sheets as Name1 ; Name2 ; Name3 and each sheet have a value on cell A1 for any number let say between 0 to 100. On another sheet call SheetName, I have A1=Name1 ; A2=Name2 ; A3=Name3.

I have formula as:

[Code] .....

The INDIRECT formula work to refer each cell A1 in each sheet, however if the sheet name include a space then it won't work anymore. How to make it work with the sheet name with space?

## Using INDIRECT For Column Refer Only

Jul 20, 2009

Let's say I have a formula in cell A1 that is =COLUMN(L5). So cell A1 returns the result 12 (for column L).

I now want to create an index formula in another cell:

=INDEX(C1:C12, ........), but I want the 12 in C12 to be picked up from the result of cell A1

So, I tried various things like =INDEX(C1:C&INDIRECT(A1)....etc but I can't work out the correct way of doing this.

## Refer To Previous Sheet

Nov 24, 2009

I've got a workbook that increments in sheets for each new week. I have a cell showing the current week number for that sheet ( lets say cell C1) and any formulas that need to reference data from the previous sheet do in 'indirect' function which basically looks at the current week number ( lets say week 20)in cell C1 and minus's 1 to navigate to the previous week (week 19).

I was wondering if there was a way to reference the previous sheet purely by the order they're in.

So say i have 5 sheets named "1,4,5,8,9" and these represent week numbers so the sheet named '8' in cell C1 would have '8' standanding for the week number but cells that wanted to reference the previous sheet couldn't do the 'indirect' C1-1 as there is no 'week 7'.

The weeks used could vary alot so a formula to reference the directly previous sheet is needed.

ok, thought best if i show you the actual formula

=IF(ISBLANK(C12),"",IF(ISERROR(VLOOKUP(C12,INDIRECT("'"&(\$AG\$2-1)&"'!\$C:\$AG"),28,FALSE)),G12,SUM(G12,VLOOKUP(C12,INDIRECT("'"&(\$AG\$2-1)&"'!\$C:\$AG"),28,FALSE))))

the '\$A\$G2-1' part is the bit that redirects to the previous sheet based on it's name, what i think i really need is to replace this part with a 'PrevSheet' function but i'm unsure how.

## Refer To Sheet By Secret Name

Jun 9, 2006

When I look in VBA under the Microsoft Excel Objects at the sheet names in my workbook I see two names for the same sheet like so:

sOrdersFB(Orders From Bob)

how to select using the first name sOrdersFB?

## Refer To A Sheet's Codename From Another Workbook?

Apr 17, 2009

I want to be able to safeguard against a user changing the name of a worksheet, so I use the sheet's codename in all my macros.

I have the codename of a sheet: shOptions

From a different workbook, "ActiveWorkbook.shOptions" doesn't work. How can I use "shOptions" in the other workbook.

## Subscript Out Of Range - Refer To Sheet?

Jun 8, 2012

Dim k as string
K = "sheet1"

Sheets(k).select

This is the code 'm using

## Table To Refer To Sheet Names

Nov 23, 2006

I have 50 sheets (1 sheet per staff member) that will be linked to a Master Sheet that compiles the data. The staff members are based in 5 teams of 10. The names of the sheets are based on the person's initials plus the words Progress Day,

All formulas in the master sheet are based around linking to 10 sheets so I can view the performance of each team. My problem is that the composition of each team changes quite often and so the links have to be manually updated for each team. Is there any way, for example, of compiling a table of team initials like the one below:

AT
EF
FY
DE
CB
RO
PR
TA
NE
SK
HS

that could easily be edited so that the formulae containing the sheet names relate to the table rather than static sheet names?

Eg., If AT left the above team and was replaced by PG then I would update the list above and the current formula

='C:Documents and SettingsAll UsersShared DesktopProgress Day Daisychain[AT Progress Day.xls]Sheet1'!D32+'[EF Progress Day.xls]Sheet1'!D32

would update to:

='C:Documents and SettingsAll UsersShared DesktopProgress Day Daisychain[PG Progress Day.xls]Sheet1'!D32+'[EF Progress Day.xls]Sheet1'!D32

## Sheet Name Stored In Cell - Refer To In Formula

May 31, 2006

Sheet names in Col B
B2 : Sheet1
B3 : Sheet2
Etc

I tried

=INDIRECT(B3,\$J\$58)

at C3 where \$J\$58 is the cell I would like to reference on 50 different sheets

Formula returns #REF!
Col C ref's \$J\$58
Col D ref's \$L\$58
Col E ref's \$N\$58
Col F ref's \$P\$58

So as I copy the 4 formulas down, the ref to the correct sheet should update but the cell ref's should remain

## Delete Range Names That Refer To Sheet

Dec 19, 2006

I am looking for a way to delete all the range names in a worksheet, not the whole workbook. I have found several procedures that will delete all the names in an activeworkbook. For some reason it takes too long to run. Does anyone know how to handle a single sheet in VBA?

## Sheet Indirection. Refer To Sheets With Changing Names

Feb 25, 2008

I've been referencing sheets in my code directly with things like

Sheet1. Range(NamedRange)

however in recreating a new workbook (to reduce bloat), the sheet-numbering has changed... to avoid having to mess around adjusting things again, I was wondering if it's possible to do some sort of indirection... something similar to

#define SHEET_SC_CONFIG Sheet1
#define SHEET_CUSTOMER Sheet2

'and then use these throughout the code....
SHEET_SC_CONFIG.Range(NamedRange)

(cannot reference by sheet name, as end user may change these)

## Indirect Addressing To A Sheet

Nov 16, 2008

I want to do is copy data to my working sheet (say sheet-1) from other worksheetx (say sheet-2, sheet-3). That's easy enough, but I want to be able to indirectly address "sheet-2" or "sheet-3" from a cell in sheet-1.

Look at the attachment. The data under cost A, cost B, cost C is from other sheets in the same workbook. I want to able to type in "sheet-2" in the first column and Excel to automatically copy over the data in columns 2,3,4.

I do not want a VBA solution. I know this can be done with built-in Excel functions because I did it before. Unfortunately, I lost that spreadsheet and I can't recall how it was done. I tried using Indirect function, but it returns a ref# error.

## Lookup Across A Sheet VBA Indirect?

Dec 12, 2011

I've tried to find the solution and the options I've tried I just can't get the code or syntax correct...

I have a workbook with 11 sheets in, 10 data sheets, and a summary

On sheet call "CB", I want to select any cell, then, via VBA, on sheet "Summary", in cells b4 to b14, where a4 to a14 have the sheet names, I want to reference the cell I selected, using the Indirect function....

something like

Code:

SelCell = Activecell.Value
Range("b4").Select
Activecell.FormulaR1C1 = "=INDIRECT(""'""&R[0]C[-1]&""'!SelCell"")"

I know it's incorrect.

## INDIRECT In SUMPRODUCT For Sheet Name

Dec 18, 2006

In book3.xls, worksheet 'statistics_by_class_day', how can I rewrite the formula in C7 such that the count is based on number chosen in A3 and C3?

=SUMPRODUCT((INDIRECT("'attendance"&\$A\$3&"'!C:C")=\$A\$7)*((INDIRECT("'attendance"&\$A\$3&"'!I:I")<>0)))

In the formula, I:I refer to Day 4 of worksheet attendance9 (ie Column I), can I use INDIRECT() by referring to C3?

## INDIRECT And Sheet Name Starting With C01V

Mar 2, 2009

I am using the INDIRECT function to get data from sheets with different names, with the format INDIRECT(SheetName&"!\$A\$1"), with SheetName defined as the contents of a particular cell which contains the name of the sheet. This works fine with the exception of sheets which have names starting with C01V. Is there something special about these names? If I change the 0 to O or the C to A, B or D, there is no problem. Sheet names C0, C01 also don't have any issues.

## Vlookup In A Closed Sheet, By Using Indirect

Jun 21, 2007

IF(ISNA(VLOOKUP(\$B\$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE));"0";VLOOKUP(\$B\$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE))

It is the part regarding til indirect that gives me problems

This form I can get it to work

IF(ISNA(VLOOKUP(\$B\$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE));"0";VLOOKUP(\$B\$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE))

But here I need the Omklist.xls to be open. And that I dont want.

The what i want is that the user can use the sheet with out oping the data sheet..

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

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

## Develop An Indirect Indirect Validation Drop Down List

Aug 18, 2009

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

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

## Indirect: Reflect The Data In The Active Sheet Or Give Me A #VALUE Error

Jan 16, 2009

I am running the formula =VALUE(RIGHT(CELL("filename"),15)) to read the tab name in a workbook with 90 sheets. When a new account is opened, the operator inserts a new sheet from a template (which contains the above formula), and labels the tab to the account number. The formula then 'reads' in the account number and performs look-ups based on it.

The problem is that the sheets are only working when the sheet is active. They all seem to reflect the data in the active sheet or give me a #VALUE error. Where am I going wrong? How do I fix the problem?

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

## VBA To Refer To Outlook

Jun 18, 2009

In Excel, I want VBA to refer to the SENT ITEMS folder in Outlook. I want to loop over all sent items, and if the email address satisfies a condition, then record that fact in Excel. I am good at VBA in Excel, and I have a passing knowledge of VBA in Outlook, but I don't know how to refer to Outlook within my Excel VBA.

## Refer To Combo Box In VBA

Sep 14, 2009

How do I refer to a combo box (form control) in a calculation. EG: how can i say if comboBox = Something then ....

## Refer To More Than One Workbook

Nov 9, 2009

I'm using this code to open another workbook:

Dim FileToOpen
FileToOpen = Application.GetOpenFilename _
FileFilter:="BlankSAMSRegister.xlsm (*.xlsm),")
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Error"
Exit Sub
End If
Once this second workbook is open, how do I refer to each one within the subsequent code?

I really need the parent workbook to be named as wb1 and the child to be names wb2 so I can flick between the two in the next macro... I can get the parent using thisworkbook.name, but how do I get the child?

## Refer To A Checkbox Value By Vba

May 10, 2007

I have a check box whithin a worksheet. This CheckBox doesn't have any linked cell, and doesn't have any macro asigned. know I would like to refer to the value of the checkbox by vba kind of: ?ActiveSheet.Shapes("CheckBox1").value=true 'from the vba's inmediate window

## Ignore Day And Only Refer To The The Month?

Mar 9, 2009

The summary page summarises the source data into how much revenue each department has made over 12 months.

However the formula in Summary worksheet column B will only total up the amounts if the month in the Source worksheet column B is the 1st of the month. How do I change the formula in Summary worksheet column B to purely go by month and ignore the day?

## Formulas Refer To Other Tabs

Jul 17, 2009

The "summary" tab will be calculating 20 statistics for each of the 30 or more tabs. In an effort to automate this - can I replace writing "January!" with a cell reference? For example;
instead of writing = sum(january!a:a). Could I write = sum("A1"a:a), with the A1 Cell = january!. Obviously I tried this and it does not work but I am not sure if there are other options.

## How To Refer To Object Which Is In Another Subroutine

Apr 3, 2014

Is it possible to refer to an object which is in another subroutine?

Or should i have to give its definition in each sub i need it?

## Refer To The Last Row In A Selected Area Using VBA..?

Nov 3, 2008

I'm trying to do is add a signature box (for inventory-taking accountability) directly below the pasted area after I paste it. Now, of course, the copied and pasted area could have 5 rows or it could have 15, and when I copy it from the first sheet and paste it into the second it doesn't preserve the name of the range on the second page, so what I really need to do to add the signature box directly below the last row in the pasted area is specify the location in relation to the last row in the pasted selection, but I don't know how to do that. Using ActiveCell always refers to the top-left-most cell in the selected area. So...

If I have an area selected, how do I refer to the bottom-right-most cell in that selection (in the same way that ActiveCell refers to the top-left-most cell)? Or at least the bottom row? Or, even better, the row below the bottom row?

## New Lines To Refer To Different Sheets

Nov 20, 2009

=COUNTIF(A2&"_RES","Yes")

A2 refers to a linked cell but I have tried without the link there still no joy. Together they would generate for example "DCA_RES" which would refer to a column range in a different sheet on the same workbook.

I want this to work so that I can implement more easily new lines to refer to different sheets. (Drag to copy) instead of having to copy and edit a formula 100 times.