Results 1 to 12 of 12
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Cell References in INDEX Function (2000 sr-1)

    If the cell containing the name of the worksheet is A1, you can use:<pre>=INDEX(INDIRECT("'"&A1&"'!$C$2:$O$15"),IN DIRECT("'"&A1&"'!B2"),1)</pre>


  2. #2
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Cell References in INDEX Function (2000 sr-1)

    I am trying to use a cell reference value in an INDEX formula. The formula is

    =INDEX(INDEX(T10:T107,$U$6,1)!$C$2:$O$15,INDEX(T10 :T107,$U$6,1)!B2,1)

    The first INDEX function is actually the name of a hidden worksheet that it returns. I try typing in the above formula and I get the usual error message for syntax mistakes that Excel uses.
    Any help is appreciated.
    Thx in advance
    T-Man

  3. #3
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Cell References in INDEX Function (2000 sr-1)

    Thx, that worked great.

  4. #4
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Cell References in INDEX Function (2000 sr-1)

    Now I

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using Cell References in INDEX Function (2000 sr-1)

    Used named ranges: (insert name define:
    Names: InputRange
    Refers to: =INDIRECT("'"&$U$7&"'!C2:O15")

    Names: CellLink
    Refers to: =INDIRECT("'"&$U$7&"'!B2")

    Then in the Format Control - Control for the combobox enter
    =InputRange for the Input Range and
    =CellLink for the cell link.

    Note your combobox pulldown will ONLY pull the first column of your input range so you could just define:
    Names: InputRange
    Refers to: =INDIRECT("'"&"$U$7&"'!C2:C15")

    Unless you also want to use this name with an INDEX with all the other columns.

    Steve

  6. #6
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Cell References in INDEX Function (2000 sr-1)

    Thx Steve, but when I name the ranges and input that into the Format Control tab, I get the following error. What is wrong with my syntax?

    T-Man

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Cell References in INDEX Function (2000 sr-1)

    Just use the names of the ranges your defined, don't qualify them with additional addresses. So enter just InputRange into the Input Range box and CellLink into the Cell Link box.

    (You did define the names using Insert | Name | Define..., as suggested by Steve, didn't you?)

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Cell References in INDEX Function (2000 sr-1)

    Assuming that you have setup the named ranges as Steve said, then the Input range is just Inputrange and the Cell link is celllink. The "!c213" and "!b2" do not make sense to Excel or me. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Cell References in INDEX Function (2000 sr-1)

    You are too fast, Hans! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Cell References in INDEX Function (2000 sr-1)

    Please read Steve's <post#=270039>post 270039</post#> again, carefully.

  11. #11
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Cell References in INDEX Function (2000 sr-1)

    Thx guys.
    I got it now. But now I have another problem. I have about 50 worksheets where the name celllink and inputrange could be used. That

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using Cell References in INDEX Function (2000 sr-1)

    I don't understand the problem

    When the sheet name in cell U7 changes, the cell link and input ranges will change in the combo box to point to the range in that sheet.

    If U7 has "Sheet1" in it the input range will be pulled from sheet1!C2:015 and the result will be in sheet1!B2.
    If U7 has "Sheet2" in it the input range will be pulled from sheet2!C2:015 and the result will be in sheet2!B2.
    If U7 has "George" in it the input range will be pulled from George!C2:015 and the result will be in George!B2.

    What does it do that you don't like and what is NOT do that you want it to do?

    Steve

Posting Permissions

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