Indirectly Referencing a Cell on a Different Worksheet

@JN,
A slight change will solve your problem.

=IFERROR(INDIRECT(TEXT(A3,"MMDD")&LEFT(B2,1)&"!B6"),"")

*** Pay attention to the parenthesize location change ***
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

I have a file where I receive reconciliations from cashiers for total money they received by day by tender type. I keep them all together in a workbook by month and have a summary tab. I name each tab MMDDx where x is the first initial of the cashier. For example, Ann's March 1st receipt tab is labeled 0301A. I setup this formula on my summary tab to pull the numbers from certain cells in each tab:

=IFERROR(INDIRECT(TEXT(A3,"MMDD"&LEFT(B2,1))&"!$b$6"),"")

B2 is the name of the cashier, A3 is the date. So this formula joins the date (in cell A3) and the first initial of the cashier (from cell B2) and returns the value of cell B6 on that tab. I have found that this only works with certain letters. If I have a cashier whose name starts with B, D, E, G, H, M, N, S, or Y the formula will not work. Any ideas why this wouldn't work on certain letters? Thanks!

I have a file where I receive reconciliations from cashiers for total money they received by day by tender type. I keep them all together in a workbook by month and have a summary tab. I name each tab MMDDx where x is the first initial of the cashier. For example, Ann's March 1st receipt tab is labeled 0301A. I setup this formula on my summary tab to pull the numbers from certain cells in each tab:

=IFERROR(INDIRECT(TEXT(A3,"MMDD"&LEFT(B2,1))&"!$b$6"),"")

B2 is the name of the cashier, A3 is the date. So this formula joins the date (in cell A3) and the first initial of the cashier (from cell B2) and returns the value of cell B6 on that tab. I have found that this only works with certain letters. If I have a cashier whose name starts with B, D, E, G, H, M, N, S, or Y the formula will not work. Any ideas why this wouldn't work on certain letters? Thanks!

One more add on INDIRECT...

It becomes even more powerful when combined with the ADDRESS() function, such as:

= INDIRECT( "'[WorkbookName.xlsx]" & SheetName & "'!" & ADDRESS( CellWithRowNumber, AnotherCellWithColumnNumber ))

= INDIRECT( "'[WorkbookName.xlsx]SheetName'!" & ADDRESS( CellWithRowNumber, AnotherCellWithColumnNumber ))

= INDIRECT( "'[WorkbookName.xlsx]SheetName'!B" & CellWithColumnNumber )

Getting the workbook name and sheet name is always kind of a pain, but there's a beautiful shortcut. Start your formula with:

= INDIRECT(

...and then click any cell of the sheet you want to reference. Let's say it happens to be B4 for this example. This is what you'll have:

= INDIRECT( '[WorkbookName.xlsx]SheetName'!B4" )

From here it's an easy end by enclosing the workbook and sheet name in double quotes and stripping off the cell address:

= INDIRECT( "'[WorkbookName.xlsx]SheetName'!" )

Then finish it off by adding an appropriate cell reference:

= INDIRECT( "'[WorkbookName.xlsx]SheetName'!" & ADDRESS( CellWithRowNumber, AnotherCellWithColumnNumber ))

While this looks complicated, take a deep breath and break it down into a few, easy steps. There's really not that much there there.

The hardest part is coming up with the values for the

...ADDRESS( CellWithRowNumber, AnotherCellWithColumnNumber )) portion.

I often add values in helper cells and then have ADDRESS() reference those cells. Many times these helper values contain a series of consecutive numbers, like 4, 5, 6, 7... or sequential numbers with a break, say, every three cells, like 4, 5, 6, 8, 9, 10, 12...

Or I'll have arguments like:

ADDRESS( ROW() + 2, COLUMN() + 3 )

I use this when the pattern of the cells I'm copying matches the pattern of cells I'm pasting to, it's just shifted over a few rows and columns.

So, why don't I just do a copy and paste?

1) Because I sometimes am copying from multiple sheets to multiple sheets.

2) Because I'm often working with non-contiguous cells -- different blocks or every other row.

3) Because I can select a dozen sheets and write the same formula on each simultaneously.

The INDIRECT() function is extremely powerful when pulling data from multiple data sources.

For example, I have a monthly report workbook with about a dozen sheets corresponding to
different plants and divisions. It's data comes from over a dozen different files, by different
providers, each pulling their data from different sources, and provided in different formats,
some of which are ad-hoc or which evolve each month. (I work for a very large division of a
very large company.)

Mastering the INDIRECT() function allowed me to reduce my data transfer time by up to 90%
or more and greatly reduce errors. With practice, one can quickly and easily write functions
to transfer a hundred cells from a dozen sheets of one workbook to a dozen sheets of another
workbook all at once even if their arrangements are different.

I really can't say enough good things about INDIRECT(), particularly when full automation is not
a good option.

It can be said based on my experience of using the INDIRECT function that if you have your data in a horizontal matrix (i.e in various columns under their headers), but need to extract them in a vertical matrix (i.e headings placed in rows one below the other) then INDIRECT is an excellent functionality provided by Excel. Please mail me on shreepadg@gmail.com, if anyone wishes to see a demo workbook/tutorial.

This is a good introduction on one way to use the INDIRECT function. However, it should be mentioned that most (if not all) uses of INDIRECT can and should be avoided. There are a few reasons for this.

(1) INDIRECT is a volatile function. This means that Excel will recalculate any cell with an INDIRECT every time ANY cell on the sheet is updated. Extensive use of INDIRECTs can slow down your calculation time dramatically.

(2) Much of the time if you need to use an INDIRECT, you have poorly structured data. The imaginary workbook used in this tip is a prime example: you should never separate monthly data by worksheet. What happens at the end of the year? How do you do an aggregate across months? How do you compare one month to the next? What if you figure out you had been entering data into the wrong month? If you use a structured data format with one table containing a "date" column, you will have no problems manipulating the data down the line. Once your data is structured, you will almost always eliminate the need to use an INDIRECT formula, and you can use something like INDEX/MATCH , VLOOKUP or SUMPRODUCT – all of which are non-volatile functions.

This will work, but only if the workbook is open.

Will this work referencing another workbook?