Dynamic Index formula using cell references instead of cell numbers.

[Tyron]

Hey guys,

Got a real brain teaser for the hardcore excel users.

Recently I have been using an INDIRECT function:

INDIRECT("'"&("WO"&$E$1)&"'!C8")

As you can see I have already started to make my worksheet functions more dynamic by using cell references. In the above example rather than hardcode the worksheet name I have used a string "WO" which means weekly order followed by the "&" symbol to concatenate with the a cell reference meaning the value that is located in the cell "E1". Which in this case happens to be a 1 for the moment. Now for my dilema.

I am trying to get the last part of this to be a cell reference as well. I no longer wish to hardcode the !C8, but instead make this a cell reference as well. In this case the value of "A8"

Someone else suggested that I might instead use an INDEX function instead, but I have yet to come up with the correct way to concatenate it all together. Here is what I have so far:

INDEX(("'"&("WO"&$E$1)&"'")&!C7:C107"&,(A8))

Unfortunately the above index function does not work.

all help is appreciated.

Ty

 

[steve the fish]

The first formula will return the value of cell C8 in the worksheet 'WO1' if cell E1 contains a 1.

 

[Andrew Poulsom]

Maybe try:

=INDEX(INDIRECT("'"&("WO"&$E$1)&"'!C7:C107"),A8)

 

[Tyron]

Hey steve the fish,

That is correct. Now I want to indicate to excel that I will be giving it a value in place of C8 in a different cell.

later

Ty

 

[Tyron]

Hey Andrew Poulsom,

We have a winner! lol. I am still testing on some cells, but it appears that it is working. Funny that while I was thinking some more about it I came up with:

INDEX(("'"&("WO"&$E$1)&"'")&"!"&("C7:C107"),(A8),0)

Unfortunately that caused the worksheet reference to come out as a string. lol.

I was contemplating trying the INDEX function with the INDIRECT function, but wasn't sure as Microsoft does not have any kind of reference that says what functions work within or without other functions. Would you happen to have such a resource that you could point me to? Or is it mainly trial and error?

Thanks again. This question has been solved.

later

Ty

 

[Andrew Poulsom]

You can nest any function within another function provided that it returns what the outside function's relevant argument expects. That's the only rule really.

 

[Tyron]

Hey Andrew Poulsom,

Thanks. I hadn't realized that the formulas were that workable. I guess I need to book up on my functions now.

later

Ty