+ Reply to Thread
Results 1 to 7 of 7

Sheet cell reference and INDIRECT()

  1. #1
    Registered User
    Join Date
    06-09-2009
    Location
    Veldhoven, Netherlands
    MS-Off Ver
    2010 (Word, Excel, Powerpoint, Outlook)
    Posts
    36

    Sheet cell reference and INDIRECT()

    Hi all,

    this is the first time I'm here, and I'm sorry that I'm starting to ask for answers right away.

    I have this formula in excel 2007:

    =COUNTIFS(Blad1!W:W;"1";Blad1!X:X;"H")+COUNTIFS(Blad1!W:W;"6";Blad1!X:X;"H").

    Blad is Dutch for sheet, by the way.

    In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2.

    W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9.

    I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors. I tried everything I know, but I need the help of the experts.
    Last edited by NBVC; 06-09-2009 at 11:07 AM. Reason: Re-titled with original title to refer to original question only

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sheet cell reference and INDIRECT()

    What exactly is in Sheet2!B12 and Sheet2!B9?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-09-2009
    Location
    Veldhoven, Netherlands
    MS-Off Ver
    2010 (Word, Excel, Powerpoint, Outlook)
    Posts
    36

    Re: Sheet cell reference and INDIRECT()

    the value "W" (as text) and the value "X" (as text).

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sheet cell reference and INDIRECT()

    Try:

    Please Login or Register  to view this content.
    but not sure if the "1" & "6" should be in quotes (are they text too?)...

    ..if no:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-09-2009
    Location
    Veldhoven, Netherlands
    MS-Off Ver
    2010 (Word, Excel, Powerpoint, Outlook)
    Posts
    36

    Re: Sheet cell reference and INDIRECT()

    The quotes need to stay, as the "referencee" is text.

    ##EDIT: Anyway, it works! Question answered, problem solved, thanks!

  6. #6
    Registered User
    Join Date
    06-09-2009
    Location
    Veldhoven, Netherlands
    MS-Off Ver
    2010 (Word, Excel, Powerpoint, Outlook)
    Posts
    36

    Re: search for text, get column char [UNSOLVED]; INDIRECT[SOLVED]

    I have a second question.

    I'd like to search for text and get the column character where the cell with the found text.

    The combination the two 'demands'/formulae into one are giving me problems.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: search for text, get column char [UNSOLVED]; INDIRECT[SOLVED]

    The original question is Solved... please post the new question in a new thread..

    You can refer to this thread if desired.

    and also elaborate with an example or two.

+ 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