+ Reply to Thread
Results 1 to 8 of 8

Using indirect to pull cell reference in other worksheets in ranges

  1. #1
    Registered User
    Join Date
    12-18-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003, 2007
    Posts
    75

    Using indirect to pull cell reference in other worksheets in ranges

    Not sure what I am missing

    countif(Sheet1!$B$9:INDIRECT("'"&"Sheet 2"&"'!$F$5"), "Yes") returns #Ref

    Much appreciated for the help

  2. #2
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├┤ Pew Pew
    Posts
    441

    Re: Using indirect to pull cell reference in other worksheets in ranges

    countif(indirect("'Sheet1'!B9:" & 'Sheet 2'!$F$5), "Yes")

    Maybe is what you were looking for? Is there a cell reference like C12 or something in Sheet 2:F5?
    Despite the high cost of living, it remains very popular.

    Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!

  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: Using indirect to pull cell reference in other worksheets in ranges

    what reference are you trying to create ie what would it look like without the indirect
    where is the info you are going to reference in the indirect?
    "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
    Registered User
    Join Date
    12-18-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003, 2007
    Posts
    75

    Re: Using indirect to pull cell reference in other worksheets in ranges

    ah crap forgive me.
    a long day looking at this spreadsheet has numbed me from common sense...

    It should be this
    countif(Sheet1!$B$9:INDIRECT("'"&"Sheet 1"&"'!$F$5"), "Yes")

    The range should be on the same sheet.

    where F5 in sheet 1 refers to the cell reference, based on a bunch of dependent variables.

  5. #5
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├┤ Pew Pew
    Posts
    441

    Re: Using indirect to pull cell reference in other worksheets in ranges

    I think you want what I posted then, try this

    =countif(indirect("Sheet1!B9:" & 'Sheet 1'!$F$5), "Yes")

    Although, do you really have 2 tabs called Sheet1/Sheet 1?

  6. #6
    Registered User
    Join Date
    12-18-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003, 2007
    Posts
    75

    Re: Using indirect to pull cell reference in other worksheets in ranges

    So say if F5 in sheet 1 returns B15,
    it should be generate countif(sheet1!b9:b15)

  7. #7
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├┤ Pew Pew
    Posts
    441

    Re: Using indirect to pull cell reference in other worksheets in ranges

    Pretty much. Putting it around just a basic cell reference is unnecessary. You want it to wrap around the first reference since part of it is text and part of it is a cell reference. Then it should return the correct range of values.

  8. #8
    Registered User
    Join Date
    12-18-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003, 2007
    Posts
    75

    Re: Using indirect to pull cell reference in other worksheets in ranges

    ah i see.

    Many thanks!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] indirect cell reference using copies of worksheets in same workboo
    By Gord Dibben in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 04:05 AM
  2. [SOLVED] indirect cell reference using copies of worksheets in same workboo
    By JT Spitz in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

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