Dynamic Index formula using cell references instead of cell numbers.

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
Hey guys,

Got a real brain teaser for the hardcore excel users.

Recently I have been using an INDIRECT function:
Code:
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:
Code:
INDEX(("'"&("WO"&$E$1)&"'")&!C7:C107"&,(A8))
Unfortunately the above index function does not work.

all help is appreciated.

Ty
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,053
Office Version
365
Platform
Windows
The first formula will return the value of cell C8 in the worksheet 'WO1' if cell E1 contains a 1.
 

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
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

Active Member
Joined
Dec 5, 2012
Messages
258
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:
Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Dec 5, 2012
Messages
258
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,172
Messages
5,442,823
Members
405,199
Latest member
mkarnout

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top