Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    Chesapeake, Virginia, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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:

    IF('Adams, Ansel'!O28=0,"Do this.","Do that.")

    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. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Star Lounger
    Join Date
    May 2001
    Location
    Chesapeake, Virginia, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #7
    Star Lounger
    Join Date
    May 2001
    Location
    Chesapeake, Virginia, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Lounger
    Join Date
    Apr 2001
    Location
    St. Louis, Missouri, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    Star Lounger
    Join Date
    May 2001
    Location
    Chesapeake, Virginia, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Relative Sheet References?

    Walkenbach's Excel 97 VBA for Dummies(?) is helping me as a beginner.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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