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

1. 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. 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.
T-Man

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

Thx, that worked great.

Now I

5. 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")

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

Then in the Format Control - Control for the combobox enter
=InputRange for the Input Range and

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. 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. 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. 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

9. 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>

11. 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. 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
•