Relative worksheet reference in 3-D formulas?

[Hash]

Gang -

I have a workbook with a summary worksheet at the first tab, and then a
new worksheet inserted every quarter as the second tab. Earlier quarters
are pushed to the right on the tabs.

Is there a way to reference data on the second tab no matter what that
second worksheet is titled? It changes every quarter. Right now I do a
global replace manually via menu.


Thanks in advance.

....best, Hash

 

[Ken Wright]

No but there are other ways to handle these kinds of issues. Give us some
more detail of your layout. At the moment I am picturing some kind of
summary sheet at the start, that I am unsure of as to whether it references
just your second tab, or all the tabs, or whether it is a mixture.

 

[Rob]

Hash,

If the following was the formula in say cell A1 of Sheet1 (your summary),
then inserting a new sheet between Sheet2 and Sheet3 would automatically be
included in the summary sheet (Sheet1).

=SUM(Sheet2:Sheet3!A1)

Regards, Rob

 

[KL]

Hash,

Following up on Rob's suggestion, you may want to review Tom Ogilvy's posts in the following thread: http://tinyurl.com/699a7

Regards,
KL

 

[Hash]

Hi Ken -

In rereading my posting, I'm not sure if its a relative or absolute
reference I want any more, but it is definitely always the second tab ;-)

The summary sheet at the first tab references only into the second tab,
and no further.

....best, Hash

 

[Hash]

Rob -

The problem is that there is no "between" involved. It's always the
second tab, and every quarter it has a new name. To invent some syntax,
I'd want something like

A1: = sheet[+1]!A1

from RC notation.

....best, Hash

 

[Ken Wright]

But if there's nothing that says it absolutely has to be the second sheet
then my advice was going to be exactly the same as Rob's. I'd name the
second sheet 'start' and the third 'finish' or maybe just a and b, and then
use as Rob suggested

=SUM(start:finish!A1)
or
=SUM(a:b!A1) instead of references to a sheet2 or whatever the name would be

Then just drag your new sheet each time between a and b and drag the old one
out to the right of sheet b. Sheet1 will then only pick up data from any
sheet between a and b. There will be no data on sheets a or b so all you
will get is the data from your new sheet.

Failing that take a look at the INDIRECT function and then in a single cell
on your summary sheet put the name of the tab you are referring to. Use
that cell in your formulas and then just change the sheet name in that one
cell to refer to the one you want, eg if the name of your second sheet was
in cell C1 on your summary sheet then you could use

=INDIRECT(C1&"!A1")

on your summary sheet to refer to cell A1 on whatever sheet has it's name in
cell C1

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Rob -

The problem is that there is no "between" involved. It's always the
second tab, and every quarter it has a new name. To invent some syntax,
I'd want something like

A1: = sheet[+1]!A1

from RC notation.

...best, Hash

Hash,

If the following was the formula in say cell A1 of Sheet1 (your summary),
then inserting a new sheet between Sheet2 and Sheet3 would automatically be
included in the summary sheet (Sheet1).

=SUM(Sheet2:Sheet3!A1)

Regards, Rob

 

[Hash]

Ken -

Thanks -

I'll consider Indirect. It's volitile, and I won't like it, but it may
be the best way to go.

Thanks again.
....best, Hash

But if there's nothing that says it absolutely has to be the second sheet
then my advice was going to be exactly the same as Rob's. I'd name the
second sheet 'start' and the third 'finish' or maybe just a and b, and then
use as Rob suggested

=SUM(start:finish!A1)
or
=SUM(a:b!A1) instead of references to a sheet2 or whatever the name would be

Then just drag your new sheet each time between a and b and drag the old one
out to the right of sheet b. Sheet1 will then only pick up data from any
sheet between a and b. There will be no data on sheets a or b so all you
will get is the data from your new sheet.

Failing that take a look at the INDIRECT function and then in a single cell
on your summary sheet put the name of the tab you are referring to. Use
that cell in your formulas and then just change the sheet name in that one
cell to refer to the one you want, eg if the name of your second sheet was
in cell C1 on your summary sheet then you could use

=INDIRECT(C1&"!A1")

on your summary sheet to refer to cell A1 on whatever sheet has it's name in
cell C1

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Rob -

The problem is that there is no "between" involved. It's always the
second tab, and every quarter it has a new name. To invent some syntax,
I'd want something like

A1: = sheet[+1]!A1

from RC notation.

...best, Hash

Hash,

If the following was the formula in say cell A1 of Sheet1 (your summary),
then inserting a new sheet between Sheet2 and Sheet3 would automatically be
included in the summary sheet (Sheet1).

=SUM(Sheet2:Sheet3!A1)

Regards, Rob

Gang -

I have a workbook with a summary worksheet at the first tab, and then a
new worksheet inserted every quarter as the second tab. Earlier quarters
are pushed to the right on the tabs.

Is there a way to reference data on the second tab no matter what that
second worksheet is titled? It changes every quarter. Right now I do a
global replace manually via menu.


Thanks in advance.

...best, Hash

 

[Hash]

Hi Ken -

Well, I futzed with it all day. Couldn't use the bracketing worksheets
because some of the data is textual, and I couldn't find one of the
limited 3-D functions that handled that. I didn't use a straigtforward
indirect method because I didn't want to populate more cells in the
summary sheet, which is saved off as text and imported to a Lotus Notes
database.

So I tried J-Walk's SHEETOFFSET function (See his Excel 2003) and a
vague memory of how I would do this years ago in XLM. That's what I
settled on, after a lot of digging, since it seemed a bit faster.
That's important--I can hit the head while this thing calculates and
still come back to the hour-glass. ;-) The summary sheet is ~5400 rows
by 14 columns. The idea remains to automate this as much as possible.

I defined a name as SHEETARRAY = GET.WORKBOOK(1)

GET.WORK(1) returns a array of fullpaths to each worksheet in order.

and a second name of COPYDATA as

=INDIRECT("'"&MID(INDEX(SHEETARRAY,2),FIND("]",INDEX(SHEETARRAY,2))+1,128
)&"'!"&ADDRESS(ROW(),COLUMN()))

Works like a champ. I was pleasantly surprised INDEX() worked with the
array like that. I put =COPYDATA in wherever needed.

Thanks.
....best, Hash

 

[Hash]

KL -

I will. Thanks for pointing it out. I have a solution (see other post)
but I'd love a faster answer.

....best, Hash

 

[Ken Wright]

Glad you got sorted, though I'm not surprised it takes a while to calc.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Hi Ken -

Well, I futzed with it all day. Couldn't use the bracketing worksheets
because some of the data is textual, and I couldn't find one of the
limited 3-D functions that handled that. I didn't use a straigtforward
indirect method because I didn't want to populate more cells in the
summary sheet, which is saved off as text and imported to a Lotus Notes
database.

So I tried J-Walk's SHEETOFFSET function (See his Excel 2003) and a
vague memory of how I would do this years ago in XLM. That's what I
settled on, after a lot of digging, since it seemed a bit faster.
That's important--I can hit the head while this thing calculates and
still come back to the hour-glass. ;-) The summary sheet is ~5400 rows
by 14 columns. The idea remains to automate this as much as possible.

I defined a name as SHEETARRAY = GET.WORKBOOK(1)

GET.WORK(1) returns a array of fullpaths to each worksheet in order.

and a second name of COPYDATA as

=INDIRECT("'"&MID(INDEX(SHEETARRAY,2),FIND("]",INDEX(SHEETARRAY,2))+1,128
)&"'!"&ADDRESS(ROW(),COLUMN()))

Works like a champ. I was pleasantly surprised INDEX() worked with the
array like that. I put =COPYDATA in wherever needed.

Thanks.
...best, Hash

But if there's nothing that says it absolutely has to be the second sheet
then my advice was going to be exactly the same as Rob's. I'd name the
second sheet 'start' and the third 'finish' or maybe just a and b, and then
use as Rob suggested

=SUM(start:finish!A1)
or
=SUM(a:b!A1) instead of references to a sheet2 or whatever the name would be

Then just drag your new sheet each time between a and b and drag the old one
out to the right of sheet b. Sheet1 will then only pick up data from any
sheet between a and b. There will be no data on sheets a or b so all you
will get is the data from your new sheet.

Failing that take a look at the INDIRECT function and then in a single cell
on your summary sheet put the name of the tab you are referring to. Use
that cell in your formulas and then just change the sheet name in that one
cell to refer to the one you want, eg if the name of your second sheet was
in cell C1 on your summary sheet then you could use

=INDIRECT(C1&"!A1")

on your summary sheet to refer to cell A1 on whatever sheet has it's name in
cell C1

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------

--

It's easier to beg forgiveness than ask permission

--------------------------------------------------------------------------

--

 

[Harlan Grove]

(e-mail address removed) wrote...
....

I defined a name as SHEETARRAY = GET.WORKBOOK(1)

GET.WORK(1) returns a array of fullpaths to each worksheet in order.

and a second name of COPYDATA as

=INDIRECT("'"&MID(INDEX(SHEETARRAY,2),FIND("]",INDEX(SHEETARRAY,2))+1,128)
&"'!"&ADDRESS(ROW(),COLUMN()))

Works like a champ. I was pleasantly surprised INDEX() worked with the
array like that. I put =COPYDATA in wherever needed.

Be VERY CAREFUL using this. If you're running XL97 or XL2K, copying
ranges on one worksheet containing references to such names and pasting
into other worksheets will CRASH Excel, so full data loss since last
save.

Also, INDIRECT(x&ADDRESS(ROW(),COLUMN())) is wasteful. Try
INDIRECT(x&"RC",0). Anything you can do with INDIRECT(..ADDRESS()) can
be done without the ADDRESS call using R1C1-style addressing.

Finally, there's no need to remove the workbook pathname portion of the
entries in SHEETARRAY. Excel ignores them if they point into the same
workbook. So you could just use
=INDIRECT("'"&INDEX(SHEETARRAY,2)&"'!RC",0)

 

[Hash]

Harlan -

Thank you. I always pay attention to your replies.

3 paragraphs, and I learned 3 things. I haven't used R1C1 since
Multiplan and Excel 1.5--didn't know at all that Indirect took a second
argument.

Six called function reduced to two, and the end-result now fits in the
define box. Definite case of less is more.

Thanks again.

....best, Hash

(e-mail address removed) wrote...
...

I defined a name as SHEETARRAY = GET.WORKBOOK(1)

GET.WORK(1) returns a array of fullpaths to each worksheet in order.

and a second name of COPYDATA as

=INDIRECT("'"&MID(INDEX(SHEETARRAY,2),FIND("]",INDEX(SHEETARRAY,2))+1,128)
&"'!"&ADDRESS(ROW(),COLUMN()))

Works like a champ. I was pleasantly surprised INDEX() worked with the
array like that. I put =COPYDATA in wherever needed.

Be VERY CAREFUL using this. If you're running XL97 or XL2K, copying
ranges on one worksheet containing references to such names and pasting
into other worksheets will CRASH Excel, so full data loss since last
save.

Also, INDIRECT(x&ADDRESS(ROW(),COLUMN())) is wasteful. Try
INDIRECT(x&"RC",0). Anything you can do with INDIRECT(..ADDRESS()) can
be done without the ADDRESS call using R1C1-style addressing.

Finally, there's no need to remove the workbook pathname portion of the
entries in SHEETARRAY. Excel ignores them if they point into the same
workbook. So you could just use
=INDIRECT("'"&INDEX(SHEETARRAY,2)&"'!RC",0)

 

[Hash]

Harlan -

Actually 8 functions into 2. Thanks again.

....best, Hash

(e-mail address removed) wrote...
...

I defined a name as SHEETARRAY = GET.WORKBOOK(1)

GET.WORK(1) returns a array of fullpaths to each worksheet in order.

and a second name of COPYDATA as

=INDIRECT("'"&MID(INDEX(SHEETARRAY,2),FIND("]",INDEX(SHEETARRAY,2))+1,128)
&"'!"&ADDRESS(ROW(),COLUMN()))

Works like a champ. I was pleasantly surprised INDEX() worked with the
array like that. I put =COPYDATA in wherever needed.

Be VERY CAREFUL using this. If you're running XL97 or XL2K, copying
ranges on one worksheet containing references to such names and pasting
into other worksheets will CRASH Excel, so full data loss since last
save.

Also, INDIRECT(x&ADDRESS(ROW(),COLUMN())) is wasteful. Try
INDIRECT(x&"RC",0). Anything you can do with INDIRECT(..ADDRESS()) can
be done without the ADDRESS call using R1C1-style addressing.

Finally, there's no need to remove the workbook pathname portion of the
entries in SHEETARRAY. Excel ignores them if they point into the same
workbook. So you could just use
=INDIRECT("'"&INDEX(SHEETARRAY,2)&"'!RC",0)