1. ## Relative Sheet References?

Is there a way to reference a sheet name by a variable? Consider what I'm trying to do:

1) In this workbook, I have one sheet per employee, using the name format "lastname, firstname" for the name of each tab.

2) I want to grab certain information off of each person's sheet and "collect" it in a summary sheet.

3) In the summary sheet, I have a column listing everybody's name in the "lastname, firstname" format. Let's say these names are in column "A".

Let's say in A1 I have "Adams, Ansel" and in A2 I have "Bunny, Bugs". Right now if I want to read something off of Adams' sheet, my formula in B1 like this:

Then I have manually recreate this for B2:

IF('Bunny, Bugs'!O28=0,"Do this.","Do that.")

What I'm looking for is something that would do the equivalent of the following (but Excel 97 won't let me get away with this):

IF(A1!O28=0,"Do this.", "Do that.")

This way the relative reference to "A1" would change when I copied and pasted the formula down column B. And then each formula in B would read the data off the correct individual's sheet. I've experimented with such functions as T(), ADDRESS(), CELL(), to name a few, but I haven't figured out a solution. Any suggestions would be greatly appreciated!

2. ## Re: Relative Sheet References?

The INDIRECT function does what you need:

=INDIRECT(A1 & "!O28") is a reference to cell O28 on the sheet named in cell A1

Your formula would look something like

IF(INDIRECT(A1 & "!O28")=0, "Do this.","Do that.")

Ian.

3. ## Re: Relative Sheet References?

Ian,

I can make Indirect() work for certain cells within the same sheet, but I can't seem to make it work for cells in another sheet. What am I missing?

4. ## Re: Relative Sheet References?

You are not missing anything really, it's just that INDIRECT() does not like spaces or punctuation in a reference. It should work with say Adams_Ansel but not Adams, Ansel or Adams Ansel. However it can be coaxed into accepting those names by the use of the following adaption to the formula :

<big>=INDIRECT(CHAR(39)&A1&CHAR(39)&"!O28")</big>

Hope that fixes it for you

Andrew C

5. ## Re: Relative Sheet References?

Thanks, Andrew - I didn't try with spaces in the name. Is CHAR(39) the same as a single quote (') ?

Using a ' seems to work too:
=INDIRECT("'" & a1 & "'!O28")

Ian.

6. ## Re: Relative Sheet References?

Yep char(39) is the same as a single quote, and using "'" is easier. I just got the habit of using Char(39) for a single quotes as Excel treats a single quote as a special case in some cases (to treat any following entry as text, e.g '123 = 123 as text).

Thanks,

Andrew

7. ## Re: Relative Sheet References?

Ian & Andrew,

Many thanks to both of you. With the addition of the CHAR(39) trick to the original idea, the formulae work well and my summary sheet works as anticipated. About the only glitch was with the name "O'Neil" because of the apostrophe; thus Ms. O'Neil has been changed (at least on her tab's name) to "ONeil". That's a small sacrifice that I hope she won't mind!

Regards,

Jim

8. ## Re: Relative Sheet References?

Jim,

This demonstrates why it's a good practice to make sheet names without spaces or some special characters. Although it's not as readable, your references will be easier to understand (none of this "" ' "" " type of stuff). Instead of spaces, you can use a _ character.

If you use a VBA macro to perform similar actions on each sheet, this becomes easier, as you can use a For Each...Next loop to cycle through each sheet without having to refer to them by name. If you have some experience with VBA, it would be easy to automate a summary report in this way, and you wouldn't have to maintain all kinds of linked formulas as employees are added and deleted. Assign this to a button, and you'll have your summary report with one click!

9. ## Re: Relative Sheet References?

Steve,

After all the hassle with this project, it will be easy to keep your advice in mind! As for VBA -- I'm just starting to learn it in Excel 97, using the MS book on the subject. If you know of anything that would help me along in this regard, any titles you find/found helpful, I'd be happy to hear about them.

Regards,

Jim

10. ## Re: Relative Sheet References?

Walkenbach's Excel 97 VBA for Dummies(?) is helping me as a beginner.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•