Indirect

[MDaly]

Okay,
I’m not saying this very clearly. Let me try again. In a nutshell I’m trying to make a simple 3d formula use a variable as a sheet name in a formula. Here is an example of what I’m trying to do.

Sheet1 cell a1 = 1
Sheet2 cell a1 = 1

In sheet3 using the address and indirect function I want to do a 3d sum “=sum(Sheet1:Sheet2!A!)” but because in the live spread sheet, the sheets are actually date names and I’m gathering weekly and monthly totals I need to be able to change the 5 or 6 date ranges just once. What I done is set up a cell with the beginning sheet name and one with the ending sheet range and used the address function in cells c1 and c2. Then used the following formula

=sum(((indirect(c1):Indirect(c2))),a1)

The problem is this returns 0. So apparently I’m not asking the correct question. Any help would be appreciated


[ This Message was edited by: MDaly on 2002-03-12 06:07 ]

[Paul B]

I don’t understand what you are trying to do, are you summing across sheets? If so try =SUM(Sheet1:Sheet3!A1)

[MDaly]

Basically I'm trying to use the indirect function within the sum function to reference the various sheet names

On 2002-03-07 08:02, MDaly wrote:
Basically I'm trying to use the indirect function within the sum function to reference the various sheet names

Why do you need to use Indirect ?

[MDaly]

The spreadsheet uses 3d references in many many cells and refers to different groupings of names. I want to be able to make the change in one place and have the cells reference back to the one change

[headtoadie]

In case anyone is still interested in this, here is a way to use variable sheet names in formulas. It uses the Indirect spreadsheet function.

It took a while to get this to work because I kept trying to make the sheet name the variable, but to get it to work I had to make the whole cell reference the variable.

So here is an example of what works (this example uses a SumIF formula):

=SUMIF(INDIRECT($B16),'Sheet1'!$A1,INDIRECT(C16))

where $B16 contains the concatenating formula =A16&"!$B$8:$B$82" with A16 being the cell that contains the variable sheet name. The formula for C16 looks similar.

This methodology can be used in any formula that reference a cell or range of cells on another worksheet.

HT

Note: if your variable sheet name has a blank space in it, the formula in $B16 needs to look like ="'"&A16&"'!$B$8:$B$82" - reason: the sheetname needs to be surrounded by single quotes.