+ Reply to Thread
Results 1 to 10 of 10

use a cell/variable as a sheet reference?

  1. #1
    Registered User
    Join Date
    01-10-2010
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Question use a cell/variable as a sheet reference?

    I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells.

    For example... instead of LEMONS!a1 and LIMES!B2
    I would like to use REF1!a1 and REF2!B2
    where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes'

    How can I do this?

    thanks
    Last edited by 222fbj; 01-11-2010 at 06:08 PM.

  2. #2
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: use a cell/variable as a sheet reference?

    hi,

    Welcome to the Forum

    Read up on the "Indirect function" in the Excel Help files & let us know if you need help making it work.

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: use a cell/variable as a sheet reference?

    =indirect(z1&"!a1")
    if sheet name has spaces say "lemon tree"
    you need the extra '
    ='lemon tree'!A1
    then yo have to use them in the indirect
    =INDIRECT("'"&Z1&"'!a1") that's =INDIRECT("'"&Z1&"'!a1")
    Last edited by martindwilson; 01-10-2010 at 05:57 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: use a cell/variable as a sheet reference?

    Hi Shytott,

    Also, have a look at the ADDRESS function.

    =INDIRECT(ADDRESS(,,,,))

    Cheers,

  5. #5
    Registered User
    Join Date
    01-10-2010
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: use a cell/variable as a sheet reference?

    Thanks for the reply... I should have mentioned that I tried indirect - but no luck, or maybe I am using it incorrectly?
    Here is the actual formula:
    =COUNTIFS( =CHM!I3:I500,"=Chatham",CHM!M3:M500,"=M")

    This refers to a sheet 'CHM' - I want to replace 'CHM' with text in another cell. I could not get Indirect to work.

  6. #6
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: use a cell/variable as a sheet reference?

    hi,

    Are you working in Excel 2003 (listed in your profile)?
    Or in Excel 2007 which is when the "Countifs" function became available?

    Can you please upload an example file?

    Rob

  7. #7
    Registered User
    Join Date
    01-10-2010
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: use a cell/variable as a sheet reference?

    I am using 2007 (will update my profile). A file - Sample1.xlsx is attached. The formula I am working on is in sheet 'Summary' - cell K15 (yellow). That formula has a reference to 'CHM!' in it. I would like to pull 'CHM' (or whatever) from the adjoining cell J15 which contains 'CHM'.
    Thanks
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: use a cell/variable as a sheet reference?

    hi,

    Try this out:
    Please Login or Register  to view this content.
    This is very similar to Martin's earlier example where he has shown what is needed for sheets with spaces in the name & he has omitted the optional "A1" argument which defaults to "true" whereas my example states this.

    Note, that Indirect is a volatile function & if used extensively it could slow your spreadsheet calculations down.
    There's currently a discussion going on amongst some of the whizzes on the Forum discussing alternative approaches (eg using Index) but I'm taking the quicker (lazy!) approach for the moment because it looks like you will only be using the formula sparingly for flexible reporting.


    hth
    Rob

  9. #9
    Registered User
    Join Date
    01-10-2010
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: use a cell/variable as a sheet reference?

    Thanks for the help! That worked. Another question about the original formula: =COUNTIFS(CHM!I3:I500,"=Bryan",CHM!M3:M500,"=M")

    Instead of including "=Bryan" I'd like to use a cell reference that held "Bryan" or whatever text I needed. Is the Indirect function the best way to use a reference (instead of text) inside a formula? This seems like a somewhat generic issue re excel formula creation.

    UPDATE: I got it: INDIRECT("='"&J8&"'") Guess I'll read up on Indirect. The punctuation gets a bit confusing.


    thanks again.
    Last edited by 222fbj; 01-11-2010 at 03:39 PM.

  10. #10
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: use a cell/variable as a sheet reference?

    Thanks for the feedback - I'm pleased we could help

    I suggest reading through the examples in the 2007 Help files for the "Countifs" function as well. If I understand your last post correctly - no, indirect is not needed eg:
    Please Login or Register  to view this content.
    (or if cell K7 is changed from "Male" to "M")
    Please Login or Register  to view this content.
    Personally, I recommend avoiding Indirect where-ever possible, but it is (to me) the easiest way of making formulae flexible to refer to different sheets. An alternative setup of using a single "database style" layout on a single sheet (with the sheet name/grouping being added as a separate column) would allow you to use a pivot table for summarising your data.
    (pivot tables can half multiple source ranges but are a bit more involved)

    Anyway...
    If you are happy with what you've got, can you please mark the post as solved?

    hth
    Rob
    Last edited by broro183; 01-11-2010 at 05:23 PM. Reason: grammatical correction

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1