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

[HansV]

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>

[T-Man]

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

[T-Man]

Thx, that worked great.

[T-Man]

Now I

[sdckapr]

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

[T-Man]

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

[HansV]

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?)

[SammyB]

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

[SammyB]

You are too fast, Hans! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

[HansV]

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

[T-Man]

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

[sdckapr]

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